Puzzled with reading a CSV file into a ClientDataSet

WEB TMS WEB Core
1

Reading a CSV file was enabled a while ago in Web Core, but I can't get it to work. I played all sorts of keyword bingo and the best I could achieve is this:

Var
 Con : TWebClientConnection;
 CDS : TWebClientDataSet;
Begin
 Con := TWebClientConnection.Create(Self);
 CDS := TWebClientDataSet.Create(Self);
 Try
  Con.AutoOpenDataSet  := True;
  CDS.Connection := Con;

  Con.URI := 'myfile.csv';
  Con.SkipFirstCSVLine := True;
  Con.Delimiter := ';';

  Await(Con.Open);

  Console.log(CDS.RecordCount); // prints 5, as expected

  CDS.First;
  While not CDS.Eof do
   Begin
    Console.log(CDS.Fields[0].Asstring);
    CDS.Next;
   End;

This successfully loads the CSV file and CDS.RecordCount returns the number of rows in the CSV. All well up to this point!

Unfortunately, the first access to the fields as in

Console.log(CDS.Fields[0].Asstring);

throws exception "Cannot read properties of undefined (reading 'column0')"

What I tried:

  • Con.AutoOpenDataSet := False;
    Await(Con.Open);
    Await(CDS.OpenAsync);
    This hangs, OpenAsync does not resolve
  • Create the fields first
    CDS.FieldDefs.Add('column0',ftString,255);
    CDS.FieldDefs.Add('column1',ftString,255);
    CDS.FieldDefs.Add('column2',ftString,255);
    But this returns CDS.RecordCount = 0
  • And many other combinations

What is the correct way to handle this such that CDS.Fields are assigned?

2

You should in this case not create the fields at application level.
To begin with, is CDS.Fields.Count returning the expected nr. of columns?
Is the delimiter effectively set correct?

3

Yes, everything runs as expected up until the fields access. But I think I could trace down the problem.

This code fails at the last line, as reported before:

  Con.URI := './TestDropdownData.csv';
  Con.SkipFirstCSVLine := True;
  Con.Delimiter := ';';

  Await(Con.Open);

  Console.log(CDS.RecordCount);   // prints 12, as expected
  Console.log(CDS.FieldCount);    // prints  3, as expected
  Console.log(CDS.Fields.Count);  // prints  3, as expected

  CDS.First;
  While not CDS.Eof do
   Begin
    Console.log(CDS.FieldByName('column0').Asstring);
    // Or alternatively
    // Console.log(CDS.Fields[0].Asstring);

Tracing into FieldByName, shows this in the debugger:

grafik
grafik1095×806 50 KB

After CDS.First, TDataSet.ActiveBuffer tries to read the first record, for which FActiveRecord is 0. BUT, FBuffers[0] holds no data!

Now, at this point, SkipFirstCSVLine := True seems suspicious. Setting

SkipFirstCSVLine := False

in fact fixed the problem, as well as, when leaving SkipFirstCSVLine := True, doing a Next after the initial First, like so:

  Con.SkipFirstCSVLine := True;
  ...
  Await(Con.Open);
  ...
  CDS.First;
  CDS.Next;
  While not CDS.Eof do
   Begin
    Console.log(CDS.FieldByName('column0').Asstring);

So it turns out that improper handling of property SkipFirstCSVLine is the root cause of the problem. With SkipFirstCSVLine := True, FBuffers still holds a buffer at index 0, but this buffer is empty (which somehow may make sense, as it should intentionally be skipped).

You may use the attached CSV (renamed to .txt)
TestDropdownData.csv.txt (229 Bytes)
for testing.

Edit:

May I suggest that, with SkipFirstCSVLine := True, the auto-created CDS fields are named after the column headers in the first CSV row instead of "column0", "column1" etc.?

Edit 2:
And with SkipFirstCSVLine := True, CDS.RecordCount should read "one less", in my case 11 rather than 12? Shouldn't it?

4

We traced & solved the issue. The next update will address this.

1 Like