FireDac TFDBatchMoveTextReader not bringing in the full field value

Question

I have a CSV file I would like to import with the FireDAC BatchMove family of components (TextReader and DataSetWriter).

Sample CSV Data

Vehicle,State,Toll distance (mi),Distance (mi),Time (hours)
Tr226,VA,0.0,8679.9,142.5
Tr114,VA,0.0,7227.2,151.5

Sample Code

  {FDBatchMove, FDBatchMoveTextReader, FDBatchMoveDataSetWriter, and FDMemTable are declared in the Private declarations of my form}
  FDBatchMove := TFDBatchMove.Create(nil);
  FDBatchMoveTextReader := TFDBatchMoveTextReader.Create(nil);
  FDBatchMoveDataSetWriter := TFDBatchMoveDataSetWriter.Create(nil);

  FDMemTable := TFDMemTable.Create(nil);

  FDBatchMoveTextReader.FileName := 'Y:\Shared\VehicleShort.csv';
  FDBatchMoveDataSetWriter.DataSet := FDMemTable;

  FDBatchMove.Reader := FDBatchMoveTextReader;
  FDBatchMove.Writer := FDBatchMoveDataSetWriter;

  FDBatchMove.Analyze := [taDelimSep, taHeader, taFields];

  FDBatchMove.AnalyzeSample := 10;

  FDBatchMove.Execute;

After executing the above block, I loop over the FDMemTable and display the contents of the first field:

  FDMemTable.First;
  while not FDMemTable.Eof do
  begin
    ShowMessage(FDMemTable.Fields[0].AsString);
    FDMemTable.Next;
  end;

And I get the following answers. Notice the last character is cut off:

Tr22
Tr11

If I modify the CSV data to include longer vehicle names like Truck226 and Truck114 I get the same problem with the last character cut off.

Truck22
Truck11

I'm a bit at a loss for what can be done here. I need to create the BatchMove components at runtime, and the demos that came with Delphi don't go into enough depth for me to solve the problem on my own.

Interesting. I decided to create a brand new txt file and copied the data from my SO post and it worked. So that tells me there's some fundamental difference between the files themselves. I tried changing the encoding, but that doesn't seem to work. If I just copy/paste the data (using notepad++) into a new file, it works. — FLDelphi, Oct 24 '19 at 17:35

score 2 · Answer 1 · answered Oct 24 '19 at 04:36

2

String fields in csv are not enclosed with delimiter (double or single quote) so you must set FDBatchMoveTextReader.DataDef.Delimiter := #0;

answered Oct 24 '19 at 04:36

Branko

1,384
1
16
35

1

Try to change to [taDelimSep, taFormatSet, taHeader, taFields]. If this does not help, then what are your settings for date format, date separator, decimal separator ? And what is your Delphi version ? – da-soft Oct 24 '19 at 14:55
Neither of these ideas seemed to have done the trick. My Delphi version (as tagged) is Seattle. – FLDelphi Oct 24 '19 at 17:21
1

This helped me on an issue where I imported data from a text file and an unclosed double quote inside a field's content caused BatchMove to interpret the rest of the line as being part of the field, ignoring `DataDef.Separator`. It looks like `DataDef.Delimiter` is set to double quotes by default... – emno Jun 01 '22 at 08:19

score 0 · Accepted Answer · answered Oct 24 '19 at 17:42

After FredS commented I realized that it worked if I built the project from my SO question. That clued me in to the fact that my file must have a peculiarity. I realized that my file had only Line Feeds. I replaced all Line Feeds with Carriage Return \ Line Feeds and it worked.

After some research I can use FDBatchMoveTextReader.DataDef.EndOfLine to control this behavior.

FireDac TFDBatchMoveTextReader not bringing in the full field value

2 Answers2