Print Page - Tools for manage CSV files

Programming => Databases => Topic started by: Root2 on August 26, 2022, 06:14:42 pm

Title: Tools for manage CSV files
Post by: Root2 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.

Title: Re: Tools for manage CSV files
Post by: AlexTP 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.

Title: Re: Tools for manage CSV files
Post by: egsuh 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][+]

var 
    f1, f2, f3, f4: TextFile; 
    DATE, TIME, DATA, INGREDIENT: string; 
    // qr1: TSQLQuery;     // defined somewhere
 
begin 
     AssignFile(f1, 'date.csv'); 
     AssignFile(f2, 'time.csv'); 
     AssignFlle(f3, 'data.csv');
     AssignFile(f4, 'ingredient.csv'); 
 
     qr1.sql.text := 'insert into table1 date, time, data, ingredient values (:date, :time, :data, :ingredient)'; 
 
     Reset(f1); 
     Reset(f2); 
     Reset(f3); 
     Reset(f4); 
 
     while not eof(f1) do begin 
         read  (f1, date); 
         read  (f2, time); 
         read (f3, data); 
         read (f4, ingredient); 
 
         qr1.Prepare; 
         qr1.Params[0].AsString := date; 
         qr1.Params[1].AsString := time; 
         qr1.Params[2].AsString := data; 
         qr1.Params[3].AsString := ingredient; 
 
         qr1.ExecSQL;  
         qr1.Transaction.Commit; 
    end; 
    CloseFile(f1);
    CloseFile(f2);
    CloseFile(f3);
    CloseFile(f4);
end; 

Title: Re: Tools for manage CSV files
Post by: Nicole on August 27, 2022, 06:41:26 pm

Can this help you?

Code: Pascal [Select][+]

There are many times when you need to split a string into an array of strings by using a character as a separator.
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.
 
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.
 
The only solution to parsing a delimited string is to write a method of your own:
 
~~~~~~~~~~~~~~~~~~~~~~~~~
procedure ParseDelimited(const sl : TStrings; const value : string; const delimiter : string) ;
var
   dx : integer;
   ns : string;
   txt : string;
   delta : integer;
begin
   delta := Length(delimiter) ;
   txt := value + delimiter;
   sl.BeginUpdate;
   sl.Clear;
   try
     while Length(txt) > 0 do
     begin
       dx := Pos(delimiter, txt) ;
       ns := Copy(txt,0,dx-1) ;
       sl.Add(ns) ;
       txt := Copy(txt,dx+delta,MaxInt) ;
     end;
   finally
     sl.EndUpdate;
   end;
end;
~~~~~~~~~~~~~~~~~~~~~~~~~
 
Usage (fills in Memo1) :
ParseDelimited(Memo1.lines,'Zarko;Gajic;;DelphiGuide',';') 

Title: Re: Tools for manage CSV files
Post by: Thaddy 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.

Title: Re: Tools for manage CSV files
Post by: Root2 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.

Title: Re: Tools for manage CSV files
Post by: Root2 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.

Title: Re: Tools for manage CSV files
Post by: Zvoni 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

Title: Re: Tools for manage CSV files
Post by: SymbolicFrank 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][+]

var
  csv, l: TStringList;
begin
  csv := TStringList.Create;
  csv.LoadFromFile('yourcsvfile.csv');
  l := TStringList.Create;
  l.Delimiter := ',';
  l.DelimitedText := csv.Text;
  // now all of them have their own entry in l.
  l.Free;
  csv.Free;
end;

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

Code: Pascal [Select][+]

  AllOfThem := TStringList.Create;
  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.

Title: Re: Tools for manage CSV files
Post by: Zvoni on August 31, 2022, 12:17:36 pm

A StringList is IMO overkill for a simple string delimited with commas.
Just split it.

Title: Re: Tools for manage CSV files
Post by: mas steindorff on August 31, 2022, 09:48:42 pm

Quote from: Zvoni on August 31, 2022, 12:17:36 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

Title: Re: Tools for manage CSV files
Post by: Zvoni on August 31, 2022, 10:03:28 pm

Quote from: mas steindorff on August 31, 2022, 09:48:42 pm

Quote from: Zvoni on August 31, 2022, 12:17:36 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

Title: Re: Tools for manage CSV files
Post by: egsuh 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][+]

        while not eol(f1) do begin
             read (f1, aninteger);
             writeln(f2, aninteger); 
        end; 

will convert row to column.

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

Code: Pascal [Select][+]

             readln (inf1, s); StringList1.CommaText := s;   
             readln(inf2, s); StringList2.Commatext := s; 
             readln(inf3, s); StringList3.Commatext := s; 
             readln(inf4, s); StringList4.Commatext := s; 
             for ti:= 0 to AStringList.Count-1 do 
                  writeln(outf, StringList1[ti], ',', StringList2[ti],',', 
                                  StringList3[ti],',',  StringList4[ti]);
 

You may make a new file in this way.

Title: Re: Tools for manage CSV files
Post by: egsuh 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][+]

procedure TForm1.Button1Click(Sender: TObject);
var
   f : TextFile;
   i: integer;
   s: string;
 
   DateList,
   TimeList,
   DataList,
   IngList : TStringList;
 
begin
   DateList := TStringList.Create;
   TimeList := TStringList.Create;
   DataList := TStringList.Create;
   IngList := TStringList.Create;
 
   try
      AssignFile(f, 'date.txt');
      Reset(f);
      readln(f, s);
      DateList.Commatext := s;
      CloseFile(f);
 
      AssignFile(f, 'time.txt');
      Reset(f);
      readln(f, s);
      TimeList.Commatext := s;
      CloseFile(f);
 
      AssignFile(f, 'data.txt');
      Reset(f);
      readln(f, s);
      DataList.Commatext := s;
      CloseFile(f);
 
      AssignFile(f, 'ingredient.txt');
      Reset(f);
      readln(f, s);
      IngList.Commatext := s;
      CloseFile(f);
 
      AssignFile (f, 'AllData.csv');
      Rewrite(f);
 
      for i:= 0 to DateList.Count-1 do begin
         s:= Format('%d, %s, %s, %s, %s',
                   [i+1, DateList[i], TimeList[i], DataList[i], IngList[i]]);
         memo1.lines.Add(s);  // This is just to show. Not necessary.
         writeln(f, s);
      end;
      Closefile(f);
   finally
      DateList.Free;
      TimeList.Free;
      DataList.Free;
      IngList.Free;
   end;
end;

Title: Re: Tools for manage CSV files
Post by: 440bx 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.