Importing a CSV file using a FDBatchMove into a FDTableTask

Question

I am starting my first app that needs an embedded database, ability to import a CSV into a table and display the results into a Grid. I used the Live Binding Wizard to link my grid to a BindSourceDB. Here are the basic components I am using:

BindingsList1: TBindingsList;
LinkFillControlToField1: TLinkFillControlToField;
BindSourceDB1: TBindSourceDB;
FireTaskList: TFDConnection;
FDTableTask: TFDTable;
FDQuery: TFDQuery;
FDGUIxWaitCursor1: TFDGUIxWaitCursor;
FDPhysIBDriverLink1: TFDPhysIBDriverLink;
FDTableTaskDATE: TDateField;
FDTableTaskDESCRIPTION: TStringField;
FDTableTaskORIGDESC: TStringField;
FDTableTaskAMOUNT: TIntegerField;
FDTableTaskTYPE: TStringField;
FDTableTaskCATEGORY: TStringField;
FDTableTaskACCTNAME: TStringField;
FDTableTaskLABELS: TStringField;
FDTableTaskNOTES: TMemoField;
FDBatchMove1: TFDBatchMove;
FDBatchMoveTextReader1: TFDBatchMoveTextReader;
FDBatchMoveDataSetWriter1: TFDBatchMoveDataSetWriter;
OpenDialog1: TOpenDialog;
Grid1: TGrid;
LinkGridToDataSourceBindSourceDB1: TLinkGridToDataSource;

1st problem, it is a real slow process importing a file with 7 total columns and total of 5500 lines or records in the file. I setup my FDBatchMoveTextReader for CSV file adding the fields of the file. I Setup my FDBatchMoveDataSetWriter to write to the Dataset FDTableTask and assigned my table's fields to it.

Here is my basic code:

if OpenDialog1.Execute then
begin
  ShowMessage('Start read'); // more then 5 secods before this displays (sometimes)
  FDBatchMoveTextReader1.FileName := OpenDialog1.FileName;
  ShowMessage('Start Move'); // Dispalys instantly
  FDBatchMove1.Execute;
  Showmessage('done'); //About 25 seconds before this displays
end;

It works but takes very long. Doing this in Delphi 5 and using DBISAM and a CSV Import component, the whole process is like 5 seconds. I have just the default settings on the FDBatchMove component. Not only does it take long, but I am replacing the data in the FDTableTask table by setting the FDBatchMove1.Options to [poClearDest,poIdentityInsert].

The process is slow and the grid never gets repopulated with the new file data until I close and reopen the app. How can I make this process faster and display the new data in the grid when the process complete?

Answer 1

Update In view of your comments, I've updated this a third time to show an example which is as close as I'm prepared to get to your code and which does not seem to suffer the problems you say you are having. I can only suggest that you try it yourself and then try and pin down why your own project does not behave in the same way. I am not going to spend any more time on this.

Using Live Bindings is way slower than using traditional db-aware components.

That said, I'm afraid I cannot reproduce your problems. I set up a multi-device FMX project as shown in the code and DFM extract below. To make it as self-contained as possible, I've put all the components and code in a single form unit and the code generates the CSV file to import.

As you'll see if you compile and run the project, the app starts with the StringGrid containing 3 rows, and clicking the ImportCSV button generates and imports 9997 extra rows. This takes no more than a couple of seconds on my laptop.

Note that I don't see any noticeable change in the speed of the ImportCSV procedure, if I comment out the calls to DisableControls and EnableControls. This surprised me slightly, but perhaps TFDBatchMove does this or similar internally.

This sample app shows a quirk of LiveBindings (in Seattle at least). Without the calls to FDMemTable1.First in FormCreate and ImportCSV, the StringGrid only shows row 3 after FormCreate and rows 1, 2 and 10000 after ImportCSV.

Code:

type
  TForm1 = class(TForm)
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
    StringGrid1: TStringGrid;
    BindSourceDB1: TBindSourceDB;
    BindingsList1: TBindingsList;
    FDMemTable1: TFDMemTable;
    FDMemTable1ID: TIntegerField;
    FDMemTable1Name: TStringField;
    LinkGridToDataSource1: TLinkGridToDataSource;
    FDBatchMove1: TFDBatchMove;
    FDBatchMoveDataSetWriter1: TFDBatchMoveDataSetWriter;
    Button1: TButton;
    BindNavigator1: TBindNavigator;
    FDBatchMoveTextReader1: TFDBatchMoveTextReader;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    procedure ImportCSV;
  end;

var
  Form1: TForm1;

implementation

{$R *.fmx}

procedure TForm1.Button1Click(Sender: TObject);
begin
  ImportCSV;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  FDMemTable1.IndexFieldNames := 'ID';
  FDMemTable1.CreateDataSet;
  FDMemTable1.InsertRecord([1, 'One']);
  FDMemTable1.InsertRecord([2, 'Two']);
  FDMemTable1.InsertRecord([3, 'Three']);
  FDMemTable1.First;
end;

procedure TForm1.ImportCSV;
var
  AFileName : String;
  TL : TStringList;
  i : Integer;
begin
  AFileName := 'c:\temp\book1.csv';

  try
    TL := TStringList.Create;
    for i := 4 to 10000 do
      TL.Add(IntToStr(i) + ',' + 'Row ' + IntToStr(i));

    TL.SaveToFile(AFileName);

    FDMemTable1.DisableControls;
    FDBatchMoveTextReader1.FileName := AFileName;
    FDBatchMove1.Execute;
    FDMemTable1.First;
  finally
    FDMemTable1.EnableControls;
    TL.Free;
  end;
end;

DFM

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 480
  ClientWidth = 429
  FormFactor.Width = 320
  FormFactor.Height = 480
  FormFactor.Devices = [Desktop]
  OnCreate = FormCreate
  DesignerMasterStyle = 0
  object StringGrid1: TStringGrid
    Position.X = 8.000000000000000000
    Position.Y = 8.000000000000000000
    Size.Width = 409.000000000000000000
    Size.Height = 201.000000000000000000
    Size.PlatformDefault = False
    TabOrder = 3
    RowCount = 100
    RowHeight = 21.000000000000000000
    Viewport.Width = 389.000000000000000000
    Viewport.Height = 176.000000000000000000
  end
  object Button1: TButton
    Position.X = 160.000000000000000000
    Position.Y = 288.000000000000000000
    TabOrder = 10
    Text = 'Button1'
    OnClick = Button1Click
  end
  object BindNavigator1: TBindNavigator
    Position.X = 8.000000000000000000
    Position.Y = 216.000000000000000000
    Size.Width = 240.000000000000000000
    Size.Height = 25.000000000000000000
    Size.PlatformDefault = False
    TabOrder = 19
    DataSource = BindSourceDB1
    xRadius = 4.000000000000000000
    yRadius = 4.000000000000000000
  end
  object FDGUIxWaitCursor1: TFDGUIxWaitCursor
    Provider = 'FMX'
    Left = 352
    Top = 48
  end
  object BindSourceDB1: TBindSourceDB
    DataSet = FDMemTable1
    ScopeMappings = <>
    Left = 160
    Top = 48
  end
  object BindingsList1: TBindingsList
    Methods = <>
    OutputConverters = <>
    Left = 272
    Top = 48
    object LinkGridToDataSource1: TLinkGridToDataSource
      Category = 'Quick Bindings'
      DataSource = BindSourceDB1
      GridControl = StringGrid1
      Columns = <>
    end
  end
  object FDMemTable1: TFDMemTable
    FetchOptions.AssignedValues = [evMode]
    FetchOptions.Mode = fmAll
    ResourceOptions.AssignedValues = [rvSilentMode]
    ResourceOptions.SilentMode = True
    UpdateOptions.AssignedValues = [uvCheckRequired, uvAutoCommitUpdates]
    UpdateOptions.CheckRequired = False
    UpdateOptions.AutoCommitUpdates = True
    Left = 72
    Top = 48
    object FDMemTable1ID: TIntegerField
      FieldName = 'ID'
    end
    object FDMemTable1Name: TStringField
      FieldName = 'Name'
    end
  end
  object FDBatchMove1: TFDBatchMove
    Reader = FDBatchMoveTextReader1
    Writer = FDBatchMoveDataSetWriter1
    Mappings = <>
    LogFileName = 'c:\temp\Data.log'
    Left = 72
    Top = 136
  end
  object FDBatchMoveDataSetWriter1: TFDBatchMoveDataSetWriter
    DataSet = FDMemTable1
    Left = 352
    Top = 136
  end
  object FDBatchMoveTextReader1: TFDBatchMoveTextReader
    DataDef.Fields = <>
    Left = 192
    Top = 136
  end
end