Tcsvdataset issue

[Packs]

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

1. Serial No.,CITY CODE,BANK CODE,BRANCH CODE,BRANCH SOLE ID,MICR CODE,CUSTOMER ACCOUNT NO.,TRANSACTION CODE,CUSTOMER NAME,JOINT NAME1,JOINT NAME2,SIGHNING AUTHORITY1,SIGNING AUTHORITY2,SIGNING AUTHORITY3,CUSTOMER ADDRESS1,CUSTOMER ADDRESS2,CUSTOMER ADDRESS3,CUSTOMER ADDRESS4,CUSTOMER ADDRESS5,CUSTOMER CITY,CUSTOMER PIN,TEL NO. RESI.,TEL NO. OFFICE,MOBILE,NO OF BOOK,QUANTITY PER BOOK,BEARER OR ORDER,ATPAR FLAG,PRODUCT CODE,CHEQUE FROM NO,CHQUE END NO.,F1,F2,F3,F4,F5,F6,F7,F8,F9
2. 2438~400~082~304~~018310~002001000018310~10~XXXXX                      ~YYYY               ~& Others                                ~                                        ~                                        ~                                        ~R.8 xxxx.31 11A~~abc~abc CHS LTD MORI RD MAHIM~~MUMBAI~400016~~~8082601389~1~15~Bearer~N~10~526236~526250~20250319~20250319~1~010~           18310~10~ 18310~000002~
3.  
4. this data is inside text file
5.  
6.   with CSVdb do
7.   begin
8.     Close;
9.  
10.  
11.     FieldDefs.Clear;
12.     FieldDefs.Add('serialno'                    , ftString, 50);
13.     FieldDefs.Add('citycode'                    , ftString, 50);
14.     FieldDefs.Add('bankcode'                    , ftString, 50);
15.     FieldDefs.Add('branchcode'                  , ftString, 50);
16.     FieldDefs.Add('branchsoleid'                , ftString, 50);
17.     FieldDefs.Add('micrcode'                    , ftString, 50);
18.     FieldDefs.Add('customeraccountno'           , ftString, 50);
19.     FieldDefs.Add('transactioncode'             , ftString, 50);
20.     FieldDefs.Add('customername'                , ftString, 50);
21.     FieldDefs.Add('jointname1'                  , ftString, 50);
22.     FieldDefs.Add('jointname2'                  , ftString, 50);
23.     FieldDefs.Add('signingauthority1'           , ftString, 50);
24.     FieldDefs.Add('signingauthority2'           , ftString, 50);
25.     FieldDefs.Add('signingauthority3'           , ftString, 50);
26.     FieldDefs.Add('customeraddress1'            , ftString, 50);
27.     FieldDefs.Add('customeraddress2'            , ftString, 50);
28.     FieldDefs.Add('customeraddress3'            , ftString, 50);
29.     FieldDefs.Add('customeraddress4'            , ftString, 50);
30.     FieldDefs.Add('customeraddress5'            , ftString, 50);
31.     FieldDefs.Add('customercity'                , ftString, 50);
32.     FieldDefs.Add('customerpin'                 , ftString, 50);
33.     FieldDefs.Add('tel_no_resi'                 , ftString, 50);
34.     FieldDefs.Add('tel_no_office'               , ftString, 50);
35.     FieldDefs.Add('mobile'                      , ftString, 50);
36.     FieldDefs.Add('no_of_book'                  , ftString, 50);
37.     FieldDefs.Add('quantityperbook'             , ftString, 50);
38.     FieldDefs.Add('bearerororder'               , ftString, 50);
39.     FieldDefs.Add('atparflag'                   , ftString, 50);
40.     FieldDefs.Add('productcode'                 , ftString, 50);
41.     FieldDefs.Add('chequefromno'                , ftString, 50);
42.     FieldDefs.Add('chqueendno'                  , ftString, 50);
43.     FieldDefs.Add('f1'                          , ftString, 50);
44.     FieldDefs.Add('f2'                          , ftString, 50);
45.     FieldDefs.Add('f3'                          , ftString, 50);
46.     FieldDefs.Add('f4'                          , ftString, 50);
47.     FieldDefs.Add('f5'                          , ftString, 50);
48.     FieldDefs.Add('f6'                          , ftString, 50);
49.     FieldDefs.Add('f7'                          , ftString, 50);
50.     FieldDefs.Add('f8'                          , ftString, 50);
51.     FieldDefs.Add('f9'                          , ftString, 50);
52.     FieldDefs.Add('f10'                         , ftString, 50);
53.     CreateDataset;
54.     Open;
55.   end;
56.  
57.   OpenDialog.Execute;
58.  
59.   if OpenDialog.FileName = '' then
60.   begin
61.     ShowMessage('Select file ');
62.     Exit;
63.   end;
64.  
65.  
66.   with CSVdb do
67.   begin
68.      CSVOptions.Delimiter := '~';
69.      FileName := OpenDialog.FileName;
70.      Open;
71.   end;
72.  
73.  
74.  

Text is stored in file .I open the file and assign to tcsvdataset .
after it is overwrite my data and it store only fieldnames .

Please help us what I am doing wrong in this code

[Zvoni]

https://www.freepascal.org/daily/packages/fcl-db/csvdataset/tcsvoptions.html

type TCSVOptions = class(TCSVHandler)
public
  constructor Create; override;
  procedure Assign(); override;
published
  property FirstLineAsFieldNames: Boolean; [rw]
  property DefaultFieldLength: Word; [rw]
  property Delimiter;
  property QuoteChar;
  property LineEnding;
  property IgnoreOuterWhitespace;
  property QuoteOuterWhitespace;
end;

And what a crappy file.
In your first line (Columnheaders) the delimiter is a comma, in the datasection a Tilde.
Happy parsing......

[Packs]

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

1. Serial No.~CITY CODE~BANK CODE~BRANCH CODE~BRANCH SOLE ID~MICR CODE~CUSTOMER ACCOUNT NO.~TRANSACTION CODE~CUSTOMER NAME~JOINT NAME1~JOINT NAME2~SIGHNING AUTHORITY1~SIGNING AUTHORITY2~SIGNING AUTHORITY3~CUSTOMER ADDRESS1~CUSTOMER ADDRESS2~CUSTOMER ADDRESS3~CUSTOMER ADDRESS4~CUSTOMER ADDRESS5~CUSTOMER CITY~CUSTOMER PIN~TEL NO. RESI.~TEL NO. OFFICE~MOBILE~NO OF BOOK~QUANTITY PER BOOK~BEARER OR ORDER~ATPAR FLAG~PRODUCT CODE~CHEQUE FROM NO~CHQUE END NO.~F1~F2~F3~F4~F5~F6~F7~F8~F9
2. 2438~400~082~304~~018310~002001000018310~10~XXXXX                      ~YYYY               ~& Others                                ~                                        ~                                        ~                                        ~R.8 3RD XXXXXX.31 11A~~YYYY~NAVJIVAN TTTTT MAHIM~~MUMBAI~400016~~~8082601389~1~15~Bearer~N~10~526236~526250~20250319~20250319~1~010~           18310~10~ 18310~000002~
3.  

changed header line .

still it is not working

[paweld]

use TSdfDataSet

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

1. uses
2.   SdfData;
3.  
4. procedure TForm1.Button1Click(Sender: TObject);
5. var
6.   sdf: TSdfDataSet;
7.   i: Integer;
8. begin
9.   sdf := TSdfDataSet.Create(nil);
10.   sdf.FirstLineAsSchema := True;
11.   sdf.AllowMultiLine := False;
12.   sdf.Delimiter := '~';
13.   sdf.FileName := 'C:\data.csv';
14.   sdf.Open;
15.   sdf.First;
16.   i := 0;
17.   while not sdf.EOF do
18.   begin
19.     Inc(i);
20.     StringGrid1.Cells[0, i] := sdf.FieldByName('Serial No.').AsString;
21.     StringGrid1.Cells[1, i] := sdf.FieldByName('BANK CODE').AsString;
22.     StringGrid1.Cells[2, i] := sdf.FieldByName('CUSTOMER NAME').AsString;
23.     StringGrid1.Cells[3, i] := sdf.FieldByName('SIGHNING AUTHORITY1').AsString;
24.     StringGrid1.Cells[4, i] := sdf.FieldByName('CUSTOMER ADDRESS1').AsString;
25.     //etc.
26.     sdf.Next;
27.   end;
28.   sdf.Close;
29.   sdf.Free;
30. end;            
31.  

Of course, this can be combined with DBGrid using DataSource

[wp]

The problem is that TCSVDataset descends from TBufDataset and keeps the inherited storage method. So, when you access the data via CSVDataset.Filename it is assumed that data are stored in the way inherited from TBufDataset. In order to take advantage of the CSV features you must use the dedicated CSV methods: LoadFromCSVFile, LoadFromCSVStream, SaveToCSVFile, SaveToCSVStream.

See the attachment for a working demo. (Note that the number of your fielddefs does not match the number of columns in the file)

[Zvoni]

(Note that the number of your fielddefs does not match the number of columns in the file)

Praise the Lord i'm not the only one seeing that.
It's off by 1 (40 vs 41)

[Packs]

Thank you

[silvercoder70]

On an unrelated matter, when using execute() of the open, this is better -

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

1.   if OpenDialog1.Execute then
2.   begin
3.     {do something with OpenDialog1.Filename}
4.   end;
5.  

If a user selects a file and then presses cancel, Filename will contain something.

[Packs]

Tcs dataset Is having one issue . We can't add extra column.

It is not accepting extra column

[Packs]

because in some case I don't know how may field data is coming .

Yes I can do by using Tbufdataset and Tstring and loop through and update .

But tcsvdataset is having any property or method .

[Zvoni]

Alternative (Windows only?):
Create an ODBC-Connection with Driver="Microsoft Access Text Driver (*.txt, *.csv)"

https://www.connectionstrings.com/microsoft-text-odbc-driver/

In that case, the FOLDER where the csv is located is the "Database", the FILE itself a "Table"

[wp]

because in some case I don't know how may field data is coming .

Many CSV files contain a header row for the field names. Just set the FirstLineAsFieldNames property of the CSVDataset's CSVOptions to true, and the fields wil be created with the names given by the header row. You may also set CSVOptions.DefaultFieldLength to the field length required, it will be applied to all fields.