Link to home
Create AccountLog in
Avatar of TG-Steve
TG-Steve

asked on

Create mapped identifier during external data query

Hi Guys

This question is in direct relation to
https://www.experts-exchange.com/questions/26668220/Loading-a-CSV-Into-DBGrid-and-exporting-it-as-a-new-csv-with-extra-feilds.html

i am querying a csv file via microsoft text odbc driver to pull out certain information which is then being loaded into a dataset and then exported into a prepared csv file with nessacery fields in place for our currier software to automatically convert the data into automatic label print offs.

within the import file is a country field i.e (spain, united kingdom, france etc) and although i use that data in my export. i must also include a country identifier i.e (ES, GB, FR) in the output file fields. I have a list of the correct identifiers to use but i dont know how to carry out the procedure as part of the routine. although i am coming to terms with working with inbound and outbound data, my knowledge is limited and therefore if examples could be given that would be greatly appreciated.

Within procedure button2. i have not edited the fields in this example to match the wordings i use but it does give a direct example to match the data i need but have pasted the original snippet given to give the idea of what i am using.

the mappings i have are in the form of the below and i would prefer to hard code these within the application rather than looking at another external datasource.
 
MAPPINGS EXAMPLE
//C_Name	C_Code
//ZAIRE	CD
//ANDORRA	AD
//UNITED ARAB EMIRATES	AE
//AFGHANISTAN	AF
//ANTIGUA AND BARBUDA	AG
//ANGUILLA	AI
//ALBANIA	AL
//ARMENIA	AM
//NETHERLANDS ANTILLES	AN

####THE CODE USED AT THE MOMENT#####
function UnicodeToAnsiString(const AValue: WideString; ACodePage: Word): AnsiString;
var
  Len: Integer;
begin
  if AValue <> '' then
  begin
    Len := WideCharToMultiByte(ACodePage,
      WC_COMPOSITECHECK or WC_DISCARDNS or WC_SEPCHARS or WC_DEFAULTCHAR,
      @AValue[1], - 1, nil, 0, nil, nil);
    SetLength(Result, Len - 1);
    if Len > 1 then
      WideCharToMultiByte(ACodePage,
        WC_COMPOSITECHECK or WC_DISCARDNS or WC_SEPCHARS or WC_DEFAULTCHAR,
        @AValue[1], - 1, @Result[1], Len - 1, nil, nil);
  end
  else
    Result := '';
end;

procedure TForm3.Button4Click(Sender: TObject);
begin
  InitConnection;
  ADOQuery1.SQL.Clear;
  ADOQuery1.Close;
  ADOQuery1.SQL.Text := 'select [Buyer Full name],[Payment Method],[Buyer Email],[Buyer Address 1],[Buyer Address 2],[Buyer Town/City],[Buyer Postcode],[Buyer Country] from SalesHistory.csv where [Buyer Country] not like ''United Kingdom'' AND [Payment Method] = ''PayPal''';
  ADOQuery1.Open;
end;

procedure TForm1.Button2Click(Sender: TObject);
var
  NameField, Add1Field, Add2Field, TownField, ZipField, CountryField: TField;
  procedure InitDataset;
  begin
    if ClientDataset1.FieldCount = 0 then
    begin
      with ClientDataset1.FieldDefs do
      begin
        Add('Misc5', ftString, 128); 
        Add('Name', ftString, 128);
        Add('Address1', ftString, 128);
        Add('Address2', ftString, 128);
        Add('Town', ftString, 128);
        Add('Misc6', ftString, 128);
        Add('Postcode', ftString, 128);
        Add('Country', ftString, 128);
        Add('Misc1', ftString, 128);
        Add('Misc2', ftString, 128);
        Add('Misc3', ftString, 128);
        Add('Misc4', ftString, 128);
      end;
      ClientDataset1.CreateDataSet;
    end;
    ClientDataset1.EmptyDataSet;
  end;
begin
  NameField := ADOQuery1.FieldByName('Name');
  Add1Field := ADOQuery1.FieldByName('Address Line 1');
  Add2Field := ADOQuery1.FieldByName('Address Line 2/District/Neighbourhood');
  TownField := ADOQuery1.FieldByName('Town/City');
  ZipField := ADOQuery1.FieldByName('Postcode');
  CountryField := ADOQuery1.FieldByName('Country');

 InitDataset;

  ADOQuery1.DisableControls;
  ADOQuery1.First;
  while not ADOQuery1.Eof do
  begin
    ClientDataset1.Append;
    ClientDataset1.FieldByName('Misc5').Value := '0';
    ClientDataset1.FieldByName('Name').Value := NameField.Value;
    ClientDataset1.FieldByName('Address1').Value := Add1Field.Value;
    ClientDataset1.FieldByName('Address2').Value := Add2Field.Value;
    ClientDataset1.FieldByName('Town').Value := TownField.Value;
    ClientDataset1.FieldByName('Misc6').Value := '0';
    ClientDataset1.FieldByName('Postcode').Value := ZipField.Value;
    ClientDataset1.FieldByName('Country').Value := CountryField.Value;
    ClientDataset1.FieldByName('Misc1').Value := '0';
    ClientDataset1.FieldByName('Misc2').Value := '0';
    ClientDataset1.FieldByName('Misc3').Value := '0';
    ClientDataset1.FieldByName('Misc4').Value := '0';
    ClientDataset1.Post;
    ADOQuery1.Next;
  end;
  ADOQuery1.EnableControls;
  SaveDatasetToFile(ClientDataset1, 'your file name');
end;

procedure TForm1.SaveDatasetToFile(ADataset: TDataset; const AFileName: string);
var
  OutputStr: string;
  I: Integer;
  WriteStr: AnsiString;
  Stream: TFileStream;
  function AddToOutput(const ABaseString, AAdded, ADelimiter: string): string;
  begin
    if ABaseString = '' then
      Result := AAdded
    else
      Result := ABaseString + ADelimiter + AAdded;
  end;
begin
  Stream := TFileStream.Create(AFileName, fmCreate);
  try
    //output column headings/field names
    for I := 0 to ADataset.FieldCount - 1 do
      OutputStr := AddToOutput(OutputStr, ADataset.Fields[I].FieldName, '|');
    WriteStr := UnicodeToAnsiString(OutputStr + #13#10, CP_THREAD_ACP);
    Stream.Write(Pointer(WriteStr)^, Length(WriteStr) * SizeOf(AnsiChar));

    //output data
    ADataset.DisableControls;
    ADataset.First;
    while not ADataset.Eof do
    begin
      OutputStr := '';
      for I := 0 to ADataset.FieldCount - 1 do
        OutputStr := AddToOutput(OutputStr, ADataset.Fields[I].AsString, '|');

      WriteStr := UnicodeToAnsiString(OutputStr + #13#10, CP_THREAD_ACP);
      Stream.Write(Pointer(WriteStr)^, Length(WriteStr) * SizeOf(AnsiChar));

      ADataset.Next;
    end;
    ADataset.EnableControls;
  finally
    FreeAndNil(Stream);
  end;
end;
 

procedure TForm3.CreateConnectionString;
begin
  FConnectionString :=
    Format('Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=%s;Extensions=asc,csv,tab,txt',
      [IncludeTrailingPathDelimiter(ExtractFileDir(ParamStr(0)))]);
end;

procedure TForm3.InitConnection;
begin
  if not ADOConnection1.Connected then
  begin
    CreateConnectionString;
    ADOConnection1.ConnectionString := FConnectionString;
    ADOConnection1.Connected := True;
  end;
end;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of TG-Steve
TG-Steve

ASKER

ewangoya:

this methods works perfect. before closing the question, is it possible to add an else clause in the new unit just incase one of the mappings is incorrect

example:= in the dhl mapping a country may be IRELAND, REPUBLIC OF. but in the paypal file it may just say IRELAND. so if there was a else clause to create an empty return field then the dhl software will just skip that one line in the output file. instead of the procedure just halting when it hits something that doesnt match.

kind regards
steve
Actually when it does not find a match, it returns an empty string


class function TCountryMapping.CodeOf(const ACountryName: string): string;
var
  I: Integer;
begin
  Result := ''; //if there is no match, result is an empty string
  for I := 0 to Length(CCountryInfo) - 1 do
    if UpperCase(ACountryName) = CCountryInfo[I].Country then
    begin
      Result := CCountryInfo[I].Code;
      Break;
    end;
end;

Open in new window

well done ewangoya have a great new year
Why don't you use a TStringList instead of an array?  By using the TStringList, you can have the data sorted AND you can reference teh 2 character code using the long country name.

I have created a basic Unit for you but you may have to add to the use line (I didn't have time to fully debug it . . . sorry ;-).
unit uCountryMappings;

interface

uses
  SysUtils, Classes, IniFiles;

type
  function GetCountryCode(const ACountryName: string): string; //this returns code given country name
  function GetCountryName(const ACode: string): string;     //this returns country name given the code

const
  CountryMappingINIFileName = 'CountryMappings.INI';

var
  CountryMapping      : TStringList
  CountryCodeMapping  : TStringList
  CountryMappingINI   : TIniFile;
  Ndx                 : Integer;


implementation
{ TContryMapping }

function GetCountryCode(const ACountryName: string): string;
//if this list is sorted, we can use Binary Search, but for a small list its not neccessary
begin
  Ndx     :=  CountryMapping.IndexOfName(ACountryName);
  if  (I < 0)
  then BEGIN
    GetCountryCode  :=  '  ';
  end
  else begin
    GetCountryCode  :=  CountryMapping[I];
  end; {if}
end;

function GetCountryName(const ACode: string): string;
begin
  Ndx     :=  CountryCodeMapping.IndexOfName(ACode);
  if  (I < 0)
  then BEGIN
    GetCountryName  :=  '  ';
  end
  else begin
    GetCountryName  :=  CountryCodeMapping[I];
  end; {if}
end;

initialization
  CountryMapping.Create();
  CountryMappingINI :=  TIniFile.Create(CountryMappingINIFileName);
  CountryMappingINI.ReadSectionValues('Countries', CountryMapping);
  for Ndx := 0 to (CountryMapping.Count - 1) do
  begin
    CountryCodeMapping.Add(ReverseString(CountryMapping[Ndx]));
  end; {for}
  CountryMappingINI.Free;

end.

Open in new window

Oh, yeah, my unit returns two (2) spaces when it doesn't get a hit (going either direction).  That can easily be changed to some other default value by modifying the if statements in the functions.

Also, my unit expects there to be an INI file with a section Countries and, within that section a set of countryname=countrycode value pairs.