how to create Delphi 4 structure to map column names in XLS to column names in SQL

Question

I have an EXCEL sheet.it has various form-fields that are named as

 “smrBgm133GallonsGross/ smrBgm167GallonsGross “

and

 “smrEgm133GallonsGross/ smrEgm167GallonsGross “

I added to the XCEL sheets 2 new form fields named
smrBgm167GrosGalnsDA/smrEgm167GrosGalnsDA

The above additons I made in EXCEL should ACTUALLy be named as

`smrBgm229GallonsGross/smrEgm229GallonsGross` because. This is a  MUST  for the Delphi application to function properly.

This Delphi-4 application extracts , and vewrifys the form DATA in tandem with the DB.

My Delphi-4 application works (checks/inserts/retrieves) so that current months beginning gallon of milk “bgm229” is equal to last months ending gallon of milk “egm229” , and then throw an exception if they are different.

Excel sheets:- Bgm167GrosGalnsDA/ Egm160GrosGalnsDA Delphi Code (DB- input/ DB- output/validation):- bgm229/ egm229 SQL 2005 DB:- bgm167DA/ egm167DA
Actually the columns I ADDED should have been named asa "smrEgm133GallonsGross/ smrEgm167GallonsGross "...I messed up in naming them and they are on the production now....

In the Delphi procedure,for the beginning inventory, the code it is

  ExtractFormBgmInfo(smrMilkAvMilk,  'smrBgm133');
  ExtractFormBgmInfo(smrMilkDe,           'smrBgm167');

For ending inventory the code it is

  ExtractFormEgmInfo(smrMilkAvMilk,  'smrEgm133');
  ExtractFormEgmInfo(smrMilkDe,           'smrEgm167');

I am adding “smrBgm229GrosGalns/smrEgm229GrosGalns” to the list But the issue is that they are named erroneously as “smrBgm167GrosGalnsDA/ smrEgm167GrosGalnsDA” IN THE EXCEL sheets, while they are to be named as 'smrBgm229/'smrEgm229''(as is the case in the Delphi code). Hence. I added ...to the above

  ExtractFormBgmInfo(smrMilkAvMilk,    'smrBgm133');
  ExtractFormBgmInfo(smrMilkDe,           'smrBgm167');
  ExtractFormBgmInfo(smrMilkDyedDe,       'smrBgm229'); 

  ExtractFormEgmInfo(smrMilkAvMilk,      'smrEgm133');
  ExtractFormEgmInfo(smrMilkDe,           'smrEgm167');
  ExtractFormEgmInfo(smrMilkDyedDe,       'smrEgm229');

This throws an error , as smrBgm229GallonsGross /smrEgm229GallonsGross are not defined in the EXCEL sheets .So the issue is how do I convert “smrBgm167GrosGalnsDA” from Excel sheets into “smrBgm229GallonsGross” and then make my “ExtractForm” statement correct?

Please help there is an release scheduled today and got to discuss this with my superirors

Answer 1

What you want to do is map one string to another. You can use a simple string list for that.

// Declare the variable somewhere, such as at unit scope or as a field
// of your form class
var
  ColumnNameMap: TStrings;

---

// Somewhere else, such as unit initialization or a class constructor,
// initialize the data structure with the column-name mappings.    
ColumnNameMap := TStringList.Create;
ColumnNameMap.Values['Bgm167 GrosGalns DA'] := 'bgm229/ egm229';

---

// In yet a third place in your code, use something like this to map
// the column name in your input to the real column name in your output.
i := ColumnNameMap.IndexOfName(ColumnName);
if i >= 0 then
  RealColumnName := ColumnNameMap.Values[ColumnName]
else
  RealColumnName := ColumnName;

Later versions of Delphi have the generic TDictionary class. Use TDictionary<string, string>. The TStrings solution I outlined above will have problems if any of the column names can have equals signs in them, but you can mitigate that by changing the NameValueSeparator property.

var
  ColumnNameMap: TDictionary<string, string>;

---

ColumnNameMap := TDictionary<string, string>.Create;
ColumnNameMap.Add('Bgm167 GrosGalns DA', 'bgm229/ egm229');

---

if not ColumnNameMap.TryGetValue(ColumnName, RealColumnName) then
  RealColumnName := ColumnName;