Forum > Databases

Tools for manage CSV files

(1/3) > >>

Root2:
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.

AlexTP:
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:
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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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;

Nicole:
Can this help you?


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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',';')

Thaddy:
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.

Navigation

[0] Message Index

[#] Next page

Go to full version