Delphi Slowdown on importing multiple CSV files to a dataset

Question

I am using Delphi 7, Windows 7 and Absolute Database.

Quick Background. I work for a charity shop that relies on items being donated to us. To reclaim 'Gift Aid' from our HMRC we have to submit details of all sales together with the name and address of each donator of that sale. We help people with Special Needs so accurate data input is important.

Up to now to check Post Code verification was easy (based on our local area) basically the format of AA00_0AA or AA0_0AA. As our name has become better known not all Post Codes follow these rules.

I have access to UK's Royal Mail database for addresses in the UK si I thought to actually compare the inputted Post Code with a real Post Code. The RM csv file is huge so I use GSplit3 to break it down into more manageable files. This leaves me with 492 csv files each consisting of about 62000 lines. Note that I am only interested in the Post Codes so there is massive duplication.

To load these files into a dataset (without duplication) I first loaded the file names into a listbox and ran a loop to iterate through all the files to copy to the dataser.To avoid duplication I tried putting an unique index on the field but even running outside of Delphi I still got an error message about duplication. I then tried capturing the text of the last record to be appended and then compare it with the next record

   procedure TForm1.importClick(Sender: TObject);
    var
     i,y:Integer;
     lstfile:string;
    begin
      for i:= 0 to ListBox1.Items.Count-1 do
        begin
          lstfile:='';
          cd.Active:=False;//cd is a csv dataset loaded with csv file
          cd.FileName:='C:\paf 112018\CSV PAF\'+ListBox1.Items[i];
          cd.Active:=True;
          while not cd.Eof do
            begin
              if (lstfile:=cd.Fields[0].AsString=cd.Fields[0].AsString) then cd.Next 
            else
              table1.append;
               table1.fields[0].asstring:=cd.Fields[0].AsString;
               lstfile:=cd.Fields[0].AsString;
               cd.Next;
            end;
         end;
   table1.Edit;
   table1.Post;
   end;

This seemed to work OK although the total number of records in the dataset seemed low. I checked with my own Post Code and it wasn't there although another post Code was located. So obviously records had been skipped. I then tried loading the CSV file into a string list with dupignore then copying the stringlist to the dataset.

        unit Unit1;

        interface

        uses
          Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
          Dialogs, Grids, DBGrids, SMDBGrid, StdCtrls, DB, ABSMain, SdfData;

        type
          TForm1 = class(TForm)
            cd: TSdfDataSet;
            dscd: TDataSource;
            dst: TDataSource;
            ABSDatabase1: TABSDatabase;
            table1: TABSTable;
            table1PostCode: TStringField;
            Label2: TLabel;
            ListBox1: TListBox;
            getfiles: TButton;
            import: TButton;
            procedure getfilesClick(Sender: TObject);
            procedure importClick(Sender: TObject);

          private
            { Private declarations }
          public
            { Public declarations }
          end;

        var
          Form1: TForm1;
          num:Integer;
        implementation

        {$R *.dfm}
        procedure ListFileDir(Path: string; FileList: TStrings);
        var
          SR: TSearchRec;
        begin
          if FindFirst(Path + '*.csv', faAnyFile, SR) = 0 then
             begin
              repeat
              if (SR.Attr <> faDirectory) then
                begin
                  FileList.Add(SR.Name);
                end;
              until FindNext(SR) <> 0;
              FindClose(SR);
             end;
        end;

        procedure TForm1.getfilesClick(Sender: TObject);
        begin//Fill listbox with csv files
         ListFileDir('C:\paf 112018\CSV PAF\', ListBox1.Items);
        end;

          //start to iterate through files to appane to dataset
        procedure TForm1.importClick(Sender: TObject);
        var
          i,y:Integer;
          myl:TStringList;
        begin

         for i:= 0 to ListBox1.Items.Count-1 do
          begin
            myl:=TStringList.Create;
            myl.Sorted:=True;
            myl.Duplicates:=dupIgnore;
            cd.Active:=False;
            cd.FileName:='C:\paf 112018\CSV PAF\'+ListBox1.Items[i];
            cd.Active:=True;
            while not cd.Eof do
              begin
               if (cd.Fields[0].AsString='')then cd.Next 
              else
                myl.Add(cd.Fields[0].AsString);
                cd.Next;
              end;
            for y:= 0 to myl.Count-1 do
              begin
               table1.Append;
               table1.Fields[0].AsString:=myl.Strings[y];
              end;
          myl.Destroy;
          end;
          t.Edit;
          t.Post;
        end;


        procedure TForm1.Button1Click(Sender: TObject);
        begin
        t.Locate('Post Code',edit1.text,[]);
        end;

        procedure TForm1.Button2Click(Sender: TObject);
        var
          sel:string;
        begin
        q.Close;
        q.SQL.Clear;
        q.SQL.Add('Select * from postc where [Post Code] like :sel');
        q.ParamByName('sel').AsString:=Edit1.Text;
        q.Active:=True;
        end;

        end.

This starts well but soon starts to slow I presume because of memory leaks, I have tried myl.free(), freeandnil(myl) and finally destroy but they all slow down quickly. I am not an expert but do enjoy using Delphi and normally manage to solve problems through your pages or googling but this time I am stumped. Can anyone suggest a better method please

Answer 1

The following shows how to add postcodes from a file containing a list of them to a query-type DataSet such as TAdoQuery or your q dataset (your q doesn't say what type it is, as far as I can see).

It also seems from what you say that although you treat your postcode file as a CVS file, you shouldn't actually need to: if the records contain only one field, there should be no need for commas, because there is nothing to separate, the file should contain simply one postcode per line. Consequently, there doesn't seem to be any need to incur the overhead of loading it as a CSV file, so you should be able simply to load it into a TStringList and add the postcodes from there.

I'm not going to attempt to correct your code, just show a very simple example of how I think it should be done. So the following code opens a postcode list file, which is assumed to contain one postcode per line, checks whether each entry in it already exists in your table of postcodes and adds it if not.

  procedure TForm1.AddPostCodes(const PostCodeFileName: String);
  //  The following shows how to add postcodes to a table of existing ones
  //  from a file named PostCodeFileName which should include an explicit path
  var
    PostCodeList : TStringList;
    PostCode : String;
    i : Integer;
  begin
    PostCodeList := TStringList.Create;
    try
      PostCodeList.LoadFromFile(PostCodeFileName);
      if qPostCodes.Active then
        qPostCodes.Close;
      qPostCodes.Sql.Text := 'select * from postcodes order by postcode';

      qPostCodes.Open;
      try
        qPostCodes.DisableControls;  //  Always call DisableControls + EnableControls
        //  when iterating a dataset which has db-aware controls connected to it
        for i := 0 to PostCodeList.Count - 1 do begin
          PostCode := PostCodeList[i]; //  use of PostCode local variable is to assist debuggging
          if not qPostCodes.Locate('PostCode', PostCode, [loCaseInsensitive]) then
            qPostCodes.InsertRecord([PostCode]);  //  InsertRecord does not need to be foollowed by a call to Post.
        end;
      finally
        qPostCodes.EnableControls;
        qPostCodes.Close;
      end;
    finally
      PostCodeList.Free;  //  Don't use .Destroy!
    end;
  end;

Btw, regarding the comment about bracketing an iteration of a dataset by calls to DisableControls and EnableControls, the usual reason for doing this is to avoid the overheaad of updating the gui's display of any db-aware controls connected to the dataset. However, one of the reasons I'm not willing to speculate what is causing your slowdown is that TAdoQuery, which is one of the standard dataset types that codes with Delphi benefits massively from DisableControls/EnableControls even when there are NO db-aware controls connected to it. This is because of a quirk in the coding of TAdoQuery. Whatever dataset you are using may have a similar quirk.