BatchMove using FireDAC: missing fieldnames and tablename (solved)

[JohnLM 10]

win7, d11.2, vcl, firedac, sqlite with dbgrid

 

I initially tried with the batchmove sample but that was too confusing and complicated. I thought it would be a simple and quick method to learn, but I was wrong. Then, I searched a few ideas from google and am now trying a variation of one in the example described below. 

 

I am trying to learn how to read from text files using FireDAC, into a DBGrid.  Once imported, I'd like to be able to view the results first, then process it if necessary into an SQLite db.  Later, I will add file-drop capability so that i can quickly drag files into the app. 

 

The first problem is, I will usually not know the fieldnames at first, or there won't be any.  So I'm not sure how to handle that.

 

The second problem is missing a tablename to give the dataset (if using a query).

 

So, if I run the app at first, without knowing the fieldnames, I get an error about missing fieldnames. However, if I manually add them in using the following steps below..

1. select the [TextRead1]
2. double-click the ">DataDef" property
3. and double-click the Fields "..." and manually add the fields

..then, when I run the app again, I will get the error: "SQl table not defined" 

 

 

 

Code for the [ImportText] button. 

 

procedure TForm1.btnImportTextClick(Sender: TObject);
begin
  if od1.Execute then if od1.filename <> null then begin
    TextReader1.FileName := od1.FileName;
    try
      ImportFromTextBatch.Execute;
      qry.Close;
      qry.Open;
    except
       on e: exception do showmessage(E.Message);
    end;
  end;
end;

 

So, I'm close, but not there just yet. 

What am I missing to get the fieldnames automatically and a tablename set up for a DBGrid and SQLite database?

Edited December 12, 2022 by JohnLM

[programmerdelphi2k 235]

with FireDAC framework it's easy:

 

implementation

{$R *.dfm}
{
  1 FDGUIxWaitCursor
  1 FDBatchMove
  1 FDBatchMoveTextReader
  1 FDBatchMoveDataSetWriter
  1 FDMemTable
  1 DataSource
  1 DBGrid
  //
  1 FDBatchMove (if dont using same than above)
  1 FDBatchMoveDataSetReader
  1 FDBatchMoveTextWriter
}

procedure TForm1.FormCreate(Sender: TObject);
var
  MyField: TFDTextField;
begin
  MyField           := FDBatchMoveTextReader1.DataDef.Fields.Add;
  MyField.FieldName := 'ID';
  MyField.DataType  := TFDTextDataType.atLongInt;
  //
  MyField           := FDBatchMoveTextReader1.DataDef.Fields.Add;
  MyField.FieldName := 'First_Name';
  MyField.DataType  := TFDTextDataType.atString;
  //
  MyField           := FDBatchMoveTextReader1.DataDef.Fields.Add;
  MyField.FieldName := 'Birthday';
  MyField.DataType  := TFDTextDataType.atDate;
  //
  FDBatchMoveTextReader1.DataDef.Delimiter      := '"'; //
  FDBatchMoveTextReader1.DataDef.EndOfLine      := TFDTextEndOfLine.elDefault;
  FDBatchMoveTextReader1.DataDef.Separator      := ',';
  FDBatchMoveTextReader1.DataDef.WithFieldNames := false; // if your MyData.txt does not have "field names" on first line!!!
  //
  FDBatchMoveTextReader1.FileName := '..\..\MyData.txt';
end;

procedure TForm1.Button1Click(Sender: TObject); // reading from  MyFile.txt
begin
  FDMemTable1.Close;
  //
  FDBatchMove1.Reader := FDBatchMoveTextReader1;
  FDBatchMove1.Writer := FDBatchMoveDataSetWriter1;
  //
  FDBatchMoveDataSetWriter1.DataSet := FDMemTable1;
  //
  DataSource1.DataSet := FDMemTable1;
  DBGrid1.DataSource  := DataSource1;
  //
  FDBatchMove1.Execute;
end;

procedure TForm1.Button2Click(Sender: TObject); // write on MyFile.txt
begin
  if FDMemTable1.Active and (FDMemTable1.RecordCount > 0) then
    begin
      FDBatchMoveDataSetReader1.DataSet := FDMemTable1;
      //
      FDBatchMoveTextWriter1.DataDef  := FDBatchMoveTextReader1.DataDef;
      FDBatchMoveTextWriter1.FileName := FDBatchMoveTextReader1.FileName;
      //
      FDBatchMove2.Reader := FDBatchMoveDataSetReader1;
      FDBatchMove2.Writer := FDBatchMoveTextWriter1;
      //
      FDBatchMove2.Options := FDBatchMove2.Options + [poClearDest];
      FDBatchMove2.Execute;
    end;
end;

 1,"john",12/12/2022
2,"peter III",13/12/2022
3,"paul IV",14/12/2022
4,"mary",15/12/2022

now, study about "FDBatchMoveSQLReader / FDBatchMoveSQLWriter" components to SQL usage!!!

Edited December 12, 2022 by programmerdelphi2k

[JohnLM 10]

Update.. solved!

 

Alright, I finally got it. Thanks to your suggestion, and reading some of your code (the snippet below showed me the answer), I was able to work out the solution at this junction. 

 

Part of the issue/problem was with how I was linking the datasource and memtable, and I had to remove some components and edit out the code for the query, and change some properties/values.  But regarding the datasource and memtable, after a few hours, I finally realized that I had them reversed if that makes any sense. You must choose the proper path for these two components' datasource linking or else you may get a "circular reference" error sometimes. 

 

I will keep your code for future reference. 

 

Thank you for your help!

 

  DataSource1.DataSet := FDMemTable1;
  DBGrid1.DataSource  := DataSource1;