how to map names in Delphi4

Question

I have an Delphi 4 application, that extracts data from XLS 2003 sheets (filled Forms ) and inserts into SQL2005 DB .

i have a group of fields in XSL and SQL2005 called.In the Delphi code it is correspondingly called 133, 167 etc.The words around "smrBgm133GallonsGross, .." i.e "smrBgm" and "GrossGallons" are concatinated accordingly in the Delphi files.

   SQL/XLS                           Delphi 
smrBgm133GallonsGross...                133
smrBgm167GallonsGross ...               167

For the above I added a new field called in XSL/SQL called smrBgm167GallonsGrossDA

But the PROBLEM is in the Delphi it should be NAMED AS 229, NOT as 'smrBgm167GallonsGrossDA' (as per some biz rules;coz the Delphi appl, processes both EDI/XLS and EDI accepts 229)Hence getting an error while inserting and updating data via the EXCEL sheets ."saying 229 not found in DB". (Excel sheets it is named as 'smrBgm167GallonsGrossDA' where as in Delphi it is named as '229').

How to tell the Delphi application.... "if it is " smrBgm167GallonsGrossDA" then consider it as "229"?????????????

Answer 1

if copy(fieldname, Length(fieldname) - 2, 2) = 'DA' then begin delphiField = 229 end

???

Answer 2

Not entirely sure what you need, I can't make head nor tail from what you specificly are asking but perhaps this gets you on the right path.

function ExtractNumber(const Value: string): Integer;
begin
    if Value = 'smrBgm167GallonsGrossDA' then 
      Result := 229 
    else 
      Result := YourNormalFunctionToExtractTheNumber(Value);
end;

Answer 3

You can create a lookup table. Which can be used to lookup the name.

For example:

const
  cSize = 2;
  cNames : array[0..cSize-1] of string = (
    'Name1', 'Name2'
  );
  CNumbers : array[0..cSize-1] of Integer = (
    99, 123
  );

function Convert(const AName: string): Integer;
var
  i : Integer;
begin
  i := 0;
  while (i<cSize) do begin
    if cNames[i] = AName then begin
      Result := cNumbers[i];
      Exit;
    end;
    Inc(i);
  end;
  Result := NormalConvert(AName);
end;

Note you can also use one array of records:

type
  TLookupRec = record
    name   : string;
    number : Integer;
  end;
const
  cSize = 2;
  cLookup : array[0..cSize-1] of TLookupRec = (
    ( name : 'Name1'; number :  99; ),
    ( name : 'Name2'; number : 123; )
  );

function Convert(const AName: string): Integer;
var
  i : Integer;
begin
  i := 0;
  while (i<cSize) do begin
    if cLookUp[i].name = AName then begin
      Result := cLookUp[i].number;
      Exit;
    end;
    Inc(i);
  end;
  Result := NormalConvert(AName);
end;