How to load CSVDataset to DBGrid? CSV overwritten after first run.

[Vodnik]

Following SQLdb Tutorial, I successfully access DBs with SQL and show data in DBGrid.
But how the CSVDataset should be opened in DBGrid?

I have added TCSVDataset, TDataSource and TDBGrid to the form and set properties accordingly.
I can see the data at design time, but not at run time.
CSVDataset1.Open seems not enough.

Do we need SQLQuery and SQLTransaction in that case?

[mangakissa]

CSVDataset1.Open;

[Vodnik]

Yes, but this works for the first time only.
After exit from the application CSV file is modified to binary format:

Code: Pascal  [Select][+][-]

1. inBufDataSet    Column1   Column1я      Column2   Column2я   яяяяю     301   Resp. Buryatiyaю     302
2.    Buryatskij AOю     302   CHitinskaya obl.ю     341   Resp. Udmurtiyaю     342   Permskaya obl.ю     343   Sverdlovskaya obl.ю     345   Tyumenskaya obl.ю     346   Tyumenskaya obl.ю     347   Resp. Bashkortostanю     349   YAmalo-Neneckij Okrugю     351   CHelyabinskaya obl.ю     352   Kurganskaya obl.ю     353   Orenburgskaya obl.ю    

Setting CSVDataset1.Readonly=True doesn't help.

[Vodnik]

Dear gurus, please help to understand why CSV file is overwritten after application first run.
Project attached.

[ezlage]

If you have "FirstLineAsFieldNames=False" you probably need to create the FieldDefs manually. Maybe the absence of this information causes the file to be recreated.

[Vodnik]

With FirstLineAsFieldNames=True CSV-file is overwritten, too.
If FieldDefs are created manually, CSV-file is overwritten, too.
Why at all application should overwrite it?

[winni]

Hi!

Set the CSV to read only and then check on which line it crashes.

Winni

[ezlage]

I don't know what is happening - maybe a bug -, but I was able to open, write and save this way.

Before, I cleared the CSVDataset1.FileName property!

Code: Pascal  [Select][+][-]

1. var
2.   Form1: TForm1;
3.   CSVdata: TMemoryStream;
4.  
5. implementation
6.  
7. {$R *.lfm}
8.  
9. { TForm1 }
10.  
11. procedure TForm1.FormCreate(Sender: TObject);
12. begin
13.   CSVdata:=TMemoryStream.Create;
14.   CSVdata.LoadFromFile('citycodes.csv');
15.   CSVDataset1.LoadFromCSVStream(CSVdata);
16.   CSVDataset1.Open;
17. end;
18.  
19. procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
20. begin
21.   CSVdata.Clear;
22.   CSVDataset1.SaveToCSVStream(CSVdata);
23.   CSVdata.SaveToFile('citycodes.csv');
24.   CSVDataset1.Close;
25.   CSVdata.Free;
26. end;

[mangakissa]

It seems that TCSVdataset is doing strange things in the textfile.
You can use TBufdataset combined with this piece of code:

Code: Pascal  [Select][+][-]

1. type
2.  
3. { TMyCSV }
4.  
5.  TMyCSV = class
6.     fBufdataset : TBufdataset;
7.     fTextfile   : TStrings;
8.     fUseFirstRow: boolean;
9.  
10.     procedure CreateDataset;
11.     procedure fillDataset;
12.    public
13.       constructor create(aBufdataset : TBufdataset; aTextfile : string; const aUseFirstRow : boolean = true);
14.       destructor  destroy; override;
15.   end;
16.  
17. implementation
18.  
19. { TMyCSV }
20.  
21. constructor TMyCSV.create(aBufdataset: TBufdataset; aTextfile: string; const aUseFirstRow : boolean);
22. begin
23.   fBufdataset := aBufdataset;
24.   fTextfile   := TStringlist.create;
25.   fTextfile.LoadFromFile(aTextfile);
26.   fUseFirstRow:= aUseFirstRow;
27.   CreateDataset;
28.   fillDataset;
29. end;
30.  
31. destructor TMyCSV.destroy;
32. begin
33.   fTextfile.free;
34.   inherited destroy;
35. end;
36.  
37. procedure TMyCSV.CreateDataset;
38. var FirstTextline : string;
39.     slRegel       : TStrings;
40.     columnindex   : integer;
41. begin
42.   slRegel := TStringlist.create;
43.   try
44.     slRegel.Delimiter := ',';
45.     slRegel.StrictDelimiter := true;
46.     slRegel.CommaText := fTextfile[0];
47.     for columnindex := 0 to slRegel.count - 1 do
48.     begin
49.       if fUseFirstRow then
50.        fBufdataset.FieldDefs.Add(slRegel[columnindex],ftstring,30)
51.      else
52.        fBufdataset.FieldDefs.Add('line_' + inttostr(columnindex + 1),ftstring,30);
53.     end;
54.     fBufdataset.CreateDataset;
55.     fBufdataset.Active := true;
56.   finally
57.     slRegel.free;
58.   end;
59. end;
60.  
61. procedure TMyCSV.fillDataset;
62. var slRegel       : TStrings;
63.     columnindex   : integer;
64.     firstrowindex : integer;
65.     subindex      : integer;
66. begin
67.   slRegel := TStringlist.create;
68.   try
69.     slRegel.Delimiter := ',';
70.     slRegel.StrictDelimiter := true;
71.     if fUseFirstRow then
72.       firstrowindex := 1
73.     else
74.       firstrowindex := 0;
75.     for columnindex := firstrowindex to fTextfile.Count - 1 do
76.     begin
77.       slRegel.CommaText := fTextfile[columnindex];
78.       fBufdataset.Append;
79.       for subindex := 0 to slRegel.Count - 1 do
80.         fBufdataset.Fields[subindex].Value := slRegel[subindex];
81.       fBufdataset.Post;
82.     end;
83.   finally
84.     slRegel.free;
85.   end;
86. end;
87.  

Use can also look at JanSQL

[Vodnik]

Set the CSV to read only and then check on which line it crashes.

Destructor TLCLComponent.Destroy on calling inherited Destroy.

[wp]

Your description somehow reminds me of a bugreport of mine which was fixed recently in fpc trunk and 3.2-to-come: https://bugs.freepascal.org/view.php?id=34435.

[Vodnik]

Thanks, @wp, this is the same bug.
I have updated Lazarus trunk version (SVN 62624M), but issue still exists.

[wp]

No, the basic CSVDataset code belongs to FPC, not to Lazarus. Therefore, updating Lazarus is usless. Unfortunately, FPC 3.2 has not yet been released. Unless you want to build FPC from code you can download a precompiled snapshot from http://ftp.icm.edu.pl/packages/fpc/snapshot/.

[Vodnik]

@ezlage, I have tested your code, it works.
I'm trying to understand what is the keypoint.
Similar code that I expect to run, too:

Code: Pascal  [Select][+][-]

1. procedure TForm1.FormCreate(Sender: TObject);
2. begin
3.   CSVDataset1.LoadFromCSVFile(GetCurrentDir+'\citycodes.csv');
4.   CSVDataset1.Open;
5. end;
6.  
7. procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
8. begin
9.   CSVDataset1.SaveToCSVFile(GetCurrentDir+'\citycodes.csv');
10.   CSVDataset1.Close;
11. end;
12.  

causes "Stream write error" on exit.

[ezlage]

@ezlage, I have tested your code, it works.
I'm trying to understand what is the keypoint.
Similar code that I expect to run, too:

Code: Pascal  [Select][+][-]

1. procedure TForm1.FormCreate(Sender: TObject);
2. begin
3.   CSVDataset1.LoadFromCSVFile(GetCurrentDir+'\citycodes.csv');
4.   CSVDataset1.Open;
5. end;
6.  
7. procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
8. begin
9.   CSVDataset1.SaveToCSVFile(GetCurrentDir+'\citycodes.csv');
10.   CSVDataset1.Close;
11. end;
12.  

causes "Stream write error" on exit.

Yeah, I tried this way too and it failed the same way. Strange.