Recent

Author Topic: Opening second CSV File Fails  (Read 1760 times)

Aruna

  • Hero Member
  • *****
  • Posts: 513
Opening second CSV File Fails
« on: July 20, 2024, 01:59:49 am »
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.  
« Last Edit: July 20, 2024, 02:27:03 am by Aruna »

jamie

  • Hero Member
  • *****
  • Posts: 6734
Re: Opening second CSV File Fails
« Reply #1 on: July 20, 2024, 03:12:28 am »
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.
The only true wisdom is knowing you know nothing

Aruna

  • Hero Member
  • *****
  • Posts: 513
Re: Opening second CSV File Fails
« Reply #2 on: July 20, 2024, 03:30:34 am »
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.
Quote
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

  • Hero Member
  • *****
  • Posts: 6734
Re: Opening second CSV File Fails
« Reply #3 on: July 20, 2024, 03:35:11 am »
You need to test the file name returned after the first run and after the second, the initial path could be getting changed.



The only true wisdom is knowing you know nothing

Aruna

  • Hero Member
  • *****
  • Posts: 513
Re: Opening second CSV File Fails
« Reply #4 on: July 20, 2024, 03:53:11 am »
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

  • Hero Member
  • *****
  • Posts: 6734
Re: Opening second CSV File Fails
« Reply #5 on: July 20, 2024, 04:02:49 am »
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.

The only true wisdom is knowing you know nothing

Aruna

  • Hero Member
  • *****
  • Posts: 513
Re: Opening second CSV File Fails
« Reply #6 on: July 20, 2024, 04:26:32 am »
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

  • Hero Member
  • *****
  • Posts: 1268
Re: Opening second CSV File Fails
« Reply #7 on: July 20, 2024, 09:04:58 am »
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;
Best regards / Pozdrawiam
paweld

jcmontherock

  • Sr. Member
  • ****
  • Posts: 270
Re: Opening second CSV File Fails
« Reply #8 on: July 20, 2024, 09:35:05 am »
More simple:
Code: Pascal  [Select][+][-]
  1.   StringGrid1 := TStringGrid.Create(Self);
  2.   //StringGrid1.Visible := False;
  3.   StringGrid1.LoadFromCSVFile('customer.csv', ',');
  4.  
Windows 11 UTF8-64 - Lazarus 4RC1-64 - FPC 3.2.2

Aruna

  • Hero Member
  • *****
  • Posts: 513
Re: Opening second CSV File Fails
« Reply #9 on: July 20, 2024, 11:43:16 pm »
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

  • Hero Member
  • *****
  • Posts: 513
Re: Opening second CSV File Fails
« Reply #10 on: July 20, 2024, 11:45:59 pm »
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

  • Hero Member
  • *****
  • Posts: 12459
Re: Opening second CSV File Fails
« Reply #11 on: July 21, 2024, 12:13:43 am »
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

  • Hero Member
  • *****
  • Posts: 513
Re: Opening second CSV File Fails
« Reply #12 on: July 21, 2024, 12:49:41 am »
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

  • Hero Member
  • *****
  • Posts: 513
Re: Opening second CSV File Fails
« Reply #13 on: July 21, 2024, 02:46:20 am »
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

  • Hero Member
  • *****
  • Posts: 513
Re: Opening second CSV File Fails
« Reply #14 on: July 21, 2024, 03:26:06 am »
Please have a look. This shows where and when the error happens
« Last Edit: July 21, 2024, 03:36:02 am by Aruna »

 

TinyPortal © 2005-2018