TJvCsvDataSet and filter

user_1608044 0
Viewed 378

I have a problem when it comes to filter a .csv file uploaded in TJvCsvDataSet. The following is a MCVE to explain

program Project3;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  JvCsvData,
  data.DB;

var
  FJvCsvDataSet: TJvCsvDataSet;
I:Integer;
begin
  ;
  FJvCsvDataSet := TJvCsvDataSet.Create(nil);
  FJvCsvDataSet.CsvFieldDef :=
    'VTHB:%,VTHS:%,VTHBBP:&,VTBBSP:&,VTHBLP:&,VTHBLS:&,VTHTS:@';
  FJvCsvDataSet.FieldDefs.Add('VTHB', ftInteger, 0, False);
  FJvCsvDataSet.FieldDefs.Add('VTHS', ftInteger, 0, False);
  FJvCsvDataSet.FieldDefs.Add('VTHBBP', ftFloat, 0, False);
  FJvCsvDataSet.FieldDefs.Add('VTBBSP', ftFloat, 0, False);
  FJvCsvDataSet.FieldDefs.Add('VTHBLP', ftFloat, 0, False);
  FJvCsvDataSet.FieldDefs.Add('VTHBLS', ftFloat, 0, False);
  FJvCsvDataSet.FieldDefs.Add('VTHTS', ftDateTime, 0, False);

    FJvCsvDataSet.FileName :=
    'E:\...\abc.csv';
  FJvCsvDataSet.Open;
  FJvCsvDataSet.First;
//  FJvCsvDataSet.Sort('VTHB,VTHBBP', True);
  FJvCsvDataSet.Filtered := False;
  FJvCsvDataSet.Filter := '';
  for I := 1 to 1093 do
  begin
    FJvCsvDataSet.Filter:='VTHB=' + IntToStr(I);
    FJvCsvDataSet.Filtered := True;
  end;
  FJvCsvDataSet.Free;
end.

and the following is a sample of abc.csv uploaded by FJvCsvDataSet

VTHB,VTHS,VTHBBP,VTBBSP,VTHBLP,VTHBLS,VTHTS
1,1,3.05,279.86,3.1,115.98,2019-08-10 14:28:47.505
1,2,3.9,259.65,3.95,237.73,2019-08-10 14:28:47.52
1,3,3.9,136.48,3.95,31.97,2019-08-10 14:28:47.52
1,5,10.5,68.83,11,52.03,2019-08-10 14:28:47.52
1,4,12.5,41.4,13,47.75,2019-08-10 14:28:47.52
2,1,3.05,279.86,3.1,115.98,2019-08-10 14:28:47.863
2,2,3.9,259.65,3.95,237.73,2019-08-10 14:28:47.863
2,3,3.9,136.48,3.95,31.97,2019-08-10 14:28:47.863
2,5,10.5,68.83,11,52.03,2019-08-10 14:28:47.863
2,4,12.5,41.4,13,47.75,2019-08-10 14:28:47.863

When the statement FJvCsvDataSet.Filter:='VTHB=' + IntToStr(I); is executed FJvCsvDataSet.RecordCount does not change therefore the filter is not performed

What am I doing wrong ?

jedi delphi
1 Answers

I don't think you are doing anything "wrong", it just seems to be that TJvCsvDataSet implements filtering in rather a strange way.

Using your code to set up and open the dataset, the following code works fine and produces the expected records (observed in a DBGrid in a VCL version of your code):

FJvCsvDataSet.SetFilterNum('VTHB', jfIntEqual, 1);
FJvCsvDataSet.Filtered := True;

This results in a FJvCsvDataSet.RecordCount of 5, as it should. This way of filtering uses the following enumeration as the second argument of SetFilterNum:

TJvCsvFilterNumCompare = (jfIntEqual, jfIntNotEqual, jfLessThan, jfGreaterThan);

However, doing this instead

FJvCsvDataSet.Filter:= 'VTHB=1';

produces incorrect results, namely no records are filtered out and a RecordCount of 10. Obviously something is going wrong, and I'll be back later to say if I've found out what it is.

Update I've looked into it a bit further and there really is something rather strange going on.

Just doing

FJvCsvDataSet.Filter:= 'VTHB = 1';
FJvCsvDataSet.Filtered := True;

has no effect whatever, the dataset doesn't even scan the records to see if they match the filter. OTOH doing this

FJvCsvDataSet.SetFilter('VTHB', '1');
FJvCsvDataSet.Filtered := True;

does result in the rows begin scanned, by the SetFilter method

// string Filtering: Make Rows Visible Only if they match filterString
procedure TJvCustomCsvDataSet.SetFilter(const FieldName: string; Pattern: string);

and the rows are correct marked Filtered or not depending on whether they match the filter. However, at least two strange things:

  • All the records, matching or not, are still displayed by the grid. I think this means the problem lies somewhere else, possibly with the InternalSkipFiltered method, but then how come doing FJvCsvDataSet.SetFilterNum('VTHB', jfIntEqual, 1) does work? Needs further exploration.

  • Calling Close and Open on the dataset after applying the filter doesn't result in the rows being rescanned, as it logically should, but then we don't call SetFilter a second time, so this is presumably WAD. But it's a stange design, because SeFilter shouldn't be publically accessible in the first place, it should be called by a setter for the Filter property, imo.

Update #2 Well, we can't say we weren't warned

https://wiki.delphi-jedi.org/wiki/JVCL_Help:TJvCustomCsvDataSet.SetFilter

says

Note that this feature is NOT API-compatible with or similar to the filtering feature that is normally found in VCL TTable component.

and the preceding description is couched in terms of filtering fields as character data, so I'm wondering whether it was ever intended to handle ftInteger fields ....

user_26638630
Related