Opening second CSV File Fails

[Aruna]

I have three CSV datasets.

I used a CSVDataset, Datasource, and TDBGrid. I use a OpenDialog to load my CSV data file. Everything works as expected. Now, When I try to open a different CSV file Lazarus complains. I have attached a screenshot. I have tried to fix this but have been unsuccessful. I decided to ask on the forum thinking more experienced folk might see what I am doing wrong.

The first time everything works. The second time no such luck 

Seems every time I try to open a different CSV file Lazarus looks for the first loaded dataset field number 1. This is the behavior I am seeing.  I have attached screenshots of all three data sets.  I would really appreciate if anyone can point me in the right direction please. The code is below:

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

1. unit Unit1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, Forms, Controls, Graphics, Dialogs, DBGrids, StdCtrls,
9.   csvdataset, DB;
10.  
11. type
12.  
13.   { TForm1 }
14.  
15.   TForm1 = class(TForm)
16.     Button1: TButton;
17.     CSVDataset1: TCSVDataset;
18.     DataSource1: TDataSource;
19.     DBGrid1: TDBGrid;
20.     EditFilter: TEdit;
21.     Label1: TLabel;
22.     Label2: TLabel;
23.     OpenDialog1: TOpenDialog;
24.     procedure Button1Click(Sender: TObject);
25.     procedure Button2Click(Sender: TObject);
26.     procedure DBGrid1TitleClick(Column: TColumn);
27.     procedure EditFilterChange(Sender: TObject);
28.     procedure FormCreate(Sender: TObject);
29.   private
30.     procedure LoadCSVFile(const FileName: string);
31.   public
32.  
33.   end;
34.  
35. var
36.   Form1: TForm1;
37.   i:Integer;
38.  
39. implementation
40.  
41. {$R *.lfm}
42.  
43. { TForm1 }
44. procedure TForm1.EditFilterChange(Sender: TObject);
45. var
46.   country: string;
47. begin
48.  
49.   country := EditFilter.Text;
50.   country := country+'*';  //THE TRICK WAS TO ADD A WILDCARD "*" TO THE FILTER STRING AT THE END OF EACH KEYPRESS. THERE HAS TO BE A BETTER WAY?
51.   CSVDataSet1.Filtered := False; // Disable previous filters
52.   CSVDataSet1.Filter := Format('country = ''%s''', [country]);
53.   CSVDataSet1.Filtered := True;
54.   end;
55.  
56. procedure TForm1.FormCreate(Sender: TObject);
57. begin
58.  
59. end;
60.  
61.  
62. procedure TForm1.DBGrid1TitleClick(Column: TColumn);
63. begin
64.    //showmessage('sort clicked');
65.   CSVDataset1.IndexFieldNames := Uppercase(Column.Title.Caption);
66.  
67. end;
68.  
69. procedure TForm1.LoadCSVFile(const FileName: string);
70. begin
71.   CSVDataset1.Close; // Close any previously opened dataset
72.  
73.   // Set the new file name
74.   CSVDataset1.FileName := FileName;
75.   Label1.Caption:=Filename;
76.  
77.  // Optionally configure properties
78. //  CSVDataset1.FirstLineAsFieldNames := True;
79.  
80.   CSVDataset1.Open; // Open the new dataset
81.  
82.   DataSource1.DataSet := CSVDataset1; // Link the DataSource to the new dataset
83.   DBGrid1.DataSource := DataSource1; // Link the DBGrid to the DataSource
84.  
85.     for i:=0 to DBGrid1.Columns.Count-1 do
86.       DBGrid1.Columns[i].Width := 150;
87.  
88.     Label2.Caption:='Record Count: '+intToStr(CSVDataset1.RecordCount);
89. end;
90.  
91.  
92.  
93. procedure TForm1.Button1Click(Sender: TObject);
94.   begin
95.         OpenDialog1.Filter := 'CSV Files|*.csv';
96.   if OpenDialog1.Execute then
97.   begin
98.     LoadCSVFile(OpenDialog1.FileName);
99.  
100.   end;
101. end;
102.  
103.  
104.  
105.   {CSVDataset1.Close;
106.  
107.   OpenDialog1.Filter := 'CSV Files|*.csv';
108.  
109.   if OpenDialog1.Execute then
110.     CSVDataSet1.FileName := OpenDialog1.FileName;
111.  
112.     Label1.Caption:='CSV File Path: '+OpenDialog1.FileName;
113.  
114.     CSVDataSet1.Open;
115.  
116.     DataSource1.DataSet := CSVDataset1; // Link the DataSource to the new dataset
117.     DBGrid1.DataSource := DataSource1; // Link the DBGrid to the DataSource
118.  
119.     for i:=0 to DBGrid1.Columns.Count-1 do
120.       DBGrid1.Columns[i].Width := 150;
121.  
122.     Label2.Caption:='Record Count: '+intToStr(CSVDataset1.RecordCount);
123.  
124.  
125.     CSVDataset1.refresh;
126.     DBGrid1.refresh;
127.  
128.  
129.  
130. end;}
131.  
132.  
133.  
134. procedure TForm1.Button2Click(Sender: TObject);
135. begin
136.  
137. end;
138. end.
139.  

[jamie]

debug the resulting filename returned from the OpenDialog.

I noticed that you don't set the initial directory of the dialog box and on second turn, it may be set to something other than nil.

Just an idea to look at.

[Aruna]

debug the resulting filename returned from the OpenDialog.

I did put it in the Label1.Caption all the previous screenshots show the full path of the csv file opened.

I noticed that you don't set the initial directory of the dialog box and on second turn, it may be set to something other than nil.
Just an idea to look at.

I set the initial directory and tried. Same error. Please see the attached screenshot.

[jamie]

You need to test the file name returned after the first run and after the second, the initial path could be getting changed.

[Aruna]

You need to test the file name returned after the first run and after the second, the initial path could be changed.

The path remains intact I double-checked. The reason I used an Open Dialog is that the data file could be anywhere on a file system and I really do not wish to hardcode a file path. And the error I do not think has anything to do with the file path as it specifically says it is unable to find a field which is in the CSVDataset? Thank you for your suggestions though.

[jamie]

use the Debugger to step the code at the LoadFromFile, in other words, place the file name in a variable so you don't encapsulate the call to the Dialog to make this easier to step with the debugger.

  See what the real problem is. The source code for the DataSet should open so you can walk it.

[Aruna]

use the Debugger to step the code at the LoadFromFile, in other words, place the file name in a variable so you don't encapsulate the call to the Dialog to make this easier to step with the debugger.

  See what the real problem is. The source code for the DataSet should open so you can walk it.

Please see the atached screenshot. I tried.. sigh!

[paweld]

When opened, the dataset creates fields (if you don't define them beforehand). As you can see in your screenshots, each file has a different structure, and the first opening of CSVDataset1 creates the field structure from that file.
The solution is to clear the field definitions after closing the dataset.
Modify the LoadCSVFile procedure as follows:

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

1. procedure TForm1.LoadCSVFile(const FileName: string);
2. begin
3.   CSVDataset1.Close; // Close any previously opened dataset
4.    
5.   //clear fields definition
6.   CSVDataset1.Clear;
7.  
8.   // Set the new file name
9.   CSVDataset1.FileName := FileName;
10.   Label1.Caption:=Filename;
11.  
12.  // Optionally configure properties
13. //  CSVDataset1.FirstLineAsFieldNames := True;
14.  
15.   CSVDataset1.Open; // Open the new dataset
16.  
17.   DataSource1.DataSet := CSVDataset1; // Link the DataSource to the new dataset
18.   DBGrid1.DataSource := DataSource1; // Link the DBGrid to the DataSource
19.  
20.     for i:=0 to DBGrid1.Columns.Count-1 do
21.       DBGrid1.Columns[i].Width := 150;
22.  
23.     Label2.Caption:='Record Count: '+intToStr(CSVDataset1.RecordCount);
24. end;

[jcmontherock]

More simple:

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

1.   StringGrid1 := TStringGrid.Create(Self);
2.   //StringGrid1.Visible := False;
3.   StringGrid1.LoadFromCSVFile('customer.csv', ',');
4.  

[Aruna]

When opened, the dataset creates fields (if you don't define them beforehand). As you can see in your screenshots, each file has a different structure, and the first opening of CSVDataset1 creates the field structure from that file.

That makes a lot of sense, Thank you.

The solution is to clear the field definitions after closing the dataset.

I tried your fix. I am still getting the same error. I will try to see any other ways to clear,close and reset all the data sources and get back to you.I believe your fix should have worked but it gives the same error.

[Aruna]

More simple:

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

1.   StringGrid1 := TStringGrid.Create(Self);
2.   //StringGrid1.Visible := False;
3.   StringGrid1.LoadFromCSVFile('customer.csv', ',');
4.  

Thank you. I will try TStringGrid if I fail to resolve this problem with CSVDataset.

[wp]

I tried your fix. I am still getting the same error. I will try to see any other ways to clear,close and reset all the data sources and get back to you.

paweld's code is working. If the file that you try to open contains the field names in the first line, you must enable the FirstLineAsFieldNames option (CSVDataset1.CSVOptions.FirstLineAsFieldNames := True; ). If not, I think you cannot open the file in the TCSVDataset unless you explicitely create FieldDefs.

[Aruna]

I tried your fix. I am still getting the same error. I will try to see any other ways to clear,close and reset all the data sources and get back to you.

paweld's code is working. If the file that you try to open contains the field names in the first line, you must enable the FirstLineAsFieldNames option (CSVDataset1.CSVOptions.FirstLineAsFieldNames := True; ). If not, I think you cannot open the file in the TCSVDataset unless you explicitely create FieldDefs.

My apologies. Yes @paweld's code did the trick. I was changing stuff in my old code and was getting the same error. Finally, after reading your post I deleted my code and decided to cut and paste paweld's code and it does work. Thank you so much everyone and especially @paweld for all your help.

[Aruna]

I think I found out what causes things to break.

If I open a CSV file it works no issues. Open the second file again with no issues thanks to @paweld's fix. So on and so forth all other files open successfully and display in the DBGrid. Things work Great!

If I click on a column header the dataset does 'sort' as expected. If I type into the filter Tedit it 'filters' as asked. Again Great!

Now, if I try to open a different CSV file THEN things break with the field not found error. I will screen capture the desktop and post a link so you see exactly what is happening. What could be causing this behavior?

[Aruna]

Please have a look. This shows where and when the error happens