Recent

Author Topic: Tools for manage CSV files  (Read 1133 times)

Root2

  • New Member
  • *
  • Posts: 23
Tools for manage CSV files
« on: August 26, 2022, 06:14:42 pm »
Dear community please receive a cordial greeting, I am starting with lazarus and I would like to ask for your help for the following, I have a table in PostgreSQL 14 that has the following structure:

ID    DATE             TIME     DATA     INGREDIENT
1    2022-08-26   11:15    12.25    122514

I have to fill this table with several CSV files, each CSV file has DATE, TIME, DATA, INGREDIENT data, that is in total 4 CSV files, the main problem I have at the moment is that the CSV data I receive are in a single row:

Data1, Data2, Data3...Data n.

In order to be able to import correctly to each field I need to sort that data in a single column:

Data1
Data2
Data3
.
.
Data n

I know that Lazarus is quite versatile and has many tools, what I don't know is which one is the most suitable to do all this.

Thank you for your comments.

Best regards.
« Last Edit: August 26, 2022, 07:27:02 pm by Root2 »

AlexTP

  • Hero Member
  • *****
  • Posts: 1957
    • UVviewsoft
Re: Tools for manage CSV files
« Reply #1 on: August 26, 2022, 11:08:37 pm »
I did not understand the question. Give pls the example of input data, and what is the result when it's 'sorted' like you say.
I want to see how to convert the data.

egsuh

  • Hero Member
  • *****
  • Posts: 1009
Re: Tools for manage CSV files
« Reply #2 on: August 27, 2022, 06:12:49 am »
I do not know how large are the csv files.

One way is 1) first make a single csv file which shows the same structure as your target database file, working on MS-Excel, etc. 2) and then incorporate it into PostgreSQL db.

Or, you may write simple program like following(haven't tested).

Code: Pascal  [Select][+][-]
  1. var
  2.     f1, f2, f3, f4: TextFile;
  3.     DATE, TIME, DATA, INGREDIENT: string;
  4.     // qr1: TSQLQuery;     // defined somewhere
  5.  
  6. begin
  7.      AssignFile(f1, 'date.csv');
  8.      AssignFile(f2, 'time.csv');
  9.      AssignFlle(f3, 'data.csv');
  10.      AssignFile(f4, 'ingredient.csv');
  11.  
  12.      qr1.sql.text := 'insert into table1 date, time, data, ingredient values (:date, :time, :data, :ingredient)';
  13.  
  14.      Reset(f1);
  15.      Reset(f2);
  16.      Reset(f3);
  17.      Reset(f4);
  18.  
  19.      while not eof(f1) do begin
  20.          read  (f1, date);
  21.          read  (f2, time);
  22.          read (f3, data);
  23.          read (f4, ingredient);
  24.  
  25.          qr1.Prepare;
  26.          qr1.Params[0].AsString := date;
  27.          qr1.Params[1].AsString := time;
  28.          qr1.Params[2].AsString := data;
  29.          qr1.Params[3].AsString := ingredient;
  30.  
  31.          qr1.ExecSQL;  
  32.          qr1.Transaction.Commit;
  33.     end;
  34.     CloseFile(f1);
  35.     CloseFile(f2);
  36.     CloseFile(f3);
  37.     CloseFile(f4);
  38. end;

Nicole

  • Sr. Member
  • ****
  • Posts: 464
Re: Tools for manage CSV files
« Reply #3 on: August 27, 2022, 06:41:26 pm »
Can this help you?

Code: Pascal  [Select][+][-]
  1. There are many times when you need to split a string into an array of strings by using a character as a separator.
  2. For example, a CSV ("comma" separated) file might have a line like "Zarko;Gajic;;DelphiGuide" and you want this line to be parsed into 4 lines (strings) "Zarko", "Gajic", "" (empty string) and "DelphiGuide" using the semi-colon character ";" as a delimiter.
  3.  
  4. Delphi provides several methods to parse a string, but you might find that neither one does exactly what you need. For example, the ExtractStrings RTL method always uses quote characters (single or double) for delimiters. Another approach is to use the Delimiter and DelimitedText properties of the TStrings class - but unfortunately there is a bug in the implementation ("inside" Delphi) where the space character is always used as a delimiter.
  5.  
  6. The only solution to parsing a delimited string is to write a method of your own:
  7.  
  8. ~~~~~~~~~~~~~~~~~~~~~~~~~
  9. procedure ParseDelimited(const sl : TStrings; const value : string; const delimiter : string) ;
  10. var
  11.    dx : integer;
  12.    ns : string;
  13.    txt : string;
  14.    delta : integer;
  15. begin
  16.    delta := Length(delimiter) ;
  17.    txt := value + delimiter;
  18.    sl.BeginUpdate;
  19.    sl.Clear;
  20.    try
  21.      while Length(txt) > 0 do
  22.      begin
  23.        dx := Pos(delimiter, txt) ;
  24.        ns := Copy(txt,0,dx-1) ;
  25.        sl.Add(ns) ;
  26.        txt := Copy(txt,dx+delta,MaxInt) ;
  27.      end;
  28.    finally
  29.      sl.EndUpdate;
  30.    end;
  31. end;
  32. ~~~~~~~~~~~~~~~~~~~~~~~~~
  33.  
  34. Usage (fills in Memo1) :
  35. ParseDelimited(Memo1.lines,'Zarko;Gajic;;DelphiGuide',';')

Thaddy

  • Hero Member
  • *****
  • Posts: 12174
Re: Tools for manage CSV files
« Reply #4 on: August 27, 2022, 06:55:45 pm »
That is not completely correct anymore. You can simply include sysutils and use the type helper for string.
Add example later. (But it should be already on this forum somewhere)
The current limitation is it should be ansistring.

I am referring to the split helpers, not the parse helpers.
« Last Edit: August 27, 2022, 07:04:58 pm by Thaddy »
Manuals, manuals, manuals first.
You have incompetence and sheer incompetence.

Root2

  • New Member
  • *
  • Posts: 23
Re: Tools for manage CSV files
« Reply #5 on: August 30, 2022, 03:11:12 pm »
Hi AlexTP, thanks for replying, basically what I am looking for is in a CSV file that is delimited by (,) to convert its rows to columns, I can not do it manually as this CSV is received from a PLC and once received the data must be loaded automatically to a DB.

Thank you.
« Last Edit: August 30, 2022, 03:13:48 pm by Root2 »

Root2

  • New Member
  • *
  • Posts: 23
Re: Tools for manage CSV files
« Reply #6 on: August 30, 2022, 04:49:17 pm »
Egsuh thanks for the answer, I think the solution is to join the CSV files in one, my main problem is that the CSV files are coming to me in rows, so it does not meet the structure of the DB, in order to insert the data correctly I must convert the CSV data to columns.

data1,data2,data3...

convert to.

data1
data2
data3
.
.

Thank you very much for your attention.

Zvoni

  • Hero Member
  • *****
  • Posts: 1494
Re: Tools for manage CSV files
« Reply #7 on: August 31, 2022, 08:25:33 am »
You're contradicting yourself.
Quote
each CSV file has DATE, TIME, DATA, INGREDIENT data
You say, that the CSV has 4 columns, but you only show one ("Data")
What now?
Are you getting each "needed" column in its own CSV, and there as a single row instead of columns?
Post an example of the CSV contents.
Sounds simple enough
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

SymbolicFrank

  • Hero Member
  • *****
  • Posts: 1131
Re: Tools for manage CSV files
« Reply #8 on: August 31, 2022, 10:23:05 am »
A TStringList has a Delimiter and a DelimitedText. If you set the Delimiter to ',' and assign the CSV file to it:

Code: Pascal  [Select][+][-]
  1. var
  2.   csv, l: TStringList;
  3. begin
  4.   csv := TStringList.Create;
  5.   csv.LoadFromFile('yourcsvfile.csv');
  6.   l := TStringList.Create;
  7.   l.Delimiter := ',';
  8.   l.DelimitedText := csv.Text;
  9.   // now all of them have their own entry in l.
  10.   l.Free;
  11.   csv.Free;
  12. end;

And you can use a third TStringList to assemble your new file:

Code: Pascal  [Select][+][-]
  1.   AllOfThem := TStringList.Create;
  2.   for i := 0 to l.Count - 1 do AllOfThem[i] := AllOfThem[i] + ';' + l[i];

But you should insert the first file directly into AllOfThem and only add all the others like that.

Zvoni

  • Hero Member
  • *****
  • Posts: 1494
Re: Tools for manage CSV files
« Reply #9 on: August 31, 2022, 12:17:36 pm »
A StringList is IMO overkill for a simple string delimited with commas.
Just split it.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

mas steindorff

  • Hero Member
  • *****
  • Posts: 518
Re: Tools for manage CSV files
« Reply #10 on: August 31, 2022, 09:48:42 pm »
A StringList is IMO overkill for a simple string delimited with commas.
Just split it.
IMO Stringlists are a good object to learn and are a simple fix for this issue.
I find I use them in most of my programs for one thing or another.  They are also integrated into a lot of components already so (like a memo) so you should not avoid them too long.
MAS
windows 7/10 - laz 2.0 / 1.2.6 general releases

Zvoni

  • Hero Member
  • *****
  • Posts: 1494
Re: Tools for manage CSV files
« Reply #11 on: August 31, 2022, 10:03:28 pm »
A StringList is IMO overkill for a simple string delimited with commas.
Just split it.
IMO Stringlists are a good object to learn and are a simple fix for this issue.
I find I use them in most of my programs for one thing or another.  They are also integrated into a lot of components already so (like a memo) so you should not avoid them too long.
MAS
Provided OP is actually coding an app with GUI (since you mentioned a Memo)

Bottom line: not enough information about OP‘s problem
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

egsuh

  • Hero Member
  • *****
  • Posts: 1009
Re: Tools for manage CSV files
« Reply #12 on: September 01, 2022, 01:53:29 am »
Quote
Egsuh thanks for the answer, I think the solution is to join the CSV files in one, my main problem is that the CSV files are coming to me in rows, so it does not meet the structure of the DB, in order to insert the data correctly I must convert the CSV data to columns.

data1,data2,data3...

convert to.

data1
data2
data3
.
.

Thank you very much for your attention.

Do the coversion in Excel manually. If your file is small,

          1.  Open it in Excel. Then there would be a long row. 
          2.  Copy the row, and paste it with column/row converted in a new sheet. 

This is the simplelist. Just a few mouse clicks.
Once all four files are done, copy the columns into one Sheet and save it as a new CSV file.


If your files are very large, I suggested to use read statement, similar to FORTRAN method.

              read (f1, aninteger)
 
will read in only one integer number (not the whole line), if your data are delimited with blank (not sure about comma).  So,

Code: Pascal  [Select][+][-]
  1.         while not eol(f1) do begin
  2.              read (f1, aninteger);
  3.              writeln(f2, aninteger);
  4.         end;
will convert row to column.

If your files are medium sized, using TStringList may be better.

Code: Pascal  [Select][+][-]
  1.              readln (inf1, s); StringList1.CommaText := s;  
  2.              readln(inf2, s); StringList2.Commatext := s;
  3.              readln(inf3, s); StringList3.Commatext := s;
  4.              readln(inf4, s); StringList4.Commatext := s;
  5.              for ti:= 0 to AStringList.Count-1 do
  6.                   writeln(outf, StringList1[ti], ',', StringList2[ti],',',
  7.                                   StringList3[ti],',',  StringList4[ti]);
  8.  
You may make a new file in this way.

egsuh

  • Hero Member
  • *****
  • Posts: 1009
Re: Tools for manage CSV files
« Reply #13 on: September 01, 2022, 04:38:21 am »
Hi, I'm sorry that I did not notice that "read" statement cannot read TDate type directly from string.
I made up a sample project. Of course there are way-arounds.
Displaying in memo1 is just to visually check.

Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. var
  3.    f : TextFile;
  4.    i: integer;
  5.    s: string;
  6.  
  7.    DateList,
  8.    TimeList,
  9.    DataList,
  10.    IngList : TStringList;
  11.  
  12. begin
  13.    DateList := TStringList.Create;
  14.    TimeList := TStringList.Create;
  15.    DataList := TStringList.Create;
  16.    IngList := TStringList.Create;
  17.  
  18.    try
  19.       AssignFile(f, 'date.txt');
  20.       Reset(f);
  21.       readln(f, s);
  22.       DateList.Commatext := s;
  23.       CloseFile(f);
  24.  
  25.       AssignFile(f, 'time.txt');
  26.       Reset(f);
  27.       readln(f, s);
  28.       TimeList.Commatext := s;
  29.       CloseFile(f);
  30.  
  31.       AssignFile(f, 'data.txt');
  32.       Reset(f);
  33.       readln(f, s);
  34.       DataList.Commatext := s;
  35.       CloseFile(f);
  36.  
  37.       AssignFile(f, 'ingredient.txt');
  38.       Reset(f);
  39.       readln(f, s);
  40.       IngList.Commatext := s;
  41.       CloseFile(f);
  42.  
  43.       AssignFile (f, 'AllData.csv');
  44.       Rewrite(f);
  45.  
  46.       for i:= 0 to DateList.Count-1 do begin
  47.          s:= Format('%d, %s, %s, %s, %s',
  48.                    [i+1, DateList[i], TimeList[i], DataList[i], IngList[i]]);
  49.          memo1.lines.Add(s);  // This is just to show. Not necessary.
  50.          writeln(f, s);
  51.       end;
  52.       Closefile(f);
  53.    finally
  54.       DateList.Free;
  55.       TimeList.Free;
  56.       DataList.Free;
  57.       IngList.Free;
  58.    end;
  59. end;

440bx

  • Hero Member
  • *****
  • Posts: 3136
Re: Tools for manage CSV files
« Reply #14 on: September 01, 2022, 06:36:25 am »
If the objective is just to put each comma delimited string into its own line, sounds like the Lazarus text editor could do it in a single command "replace comma, crlf"

save the file (possibly with a new name)... done.


FPC v3.0.4 and Lazarus 1.8.2 on Windows 7 SP1 64bit.

 

TinyPortal © 2005-2018