I want to use Lazarus to Import a CSV file into a MYSQL table. lazarus / 16-Databases ---------------------------------------------------------------------- asdlinux · 2018-07-24 07:37 Hello Everyone, I am creating a CRM System using MYSQL (MariaDB 5.5.51), It is based on PHP/MYSQL. Now ... I need to import a large CSV file that need to be filtered and processed, So i choosed to try Lazarus for this (Never used Pascal before, So far i am loving it). I have figures out to correctly filter and sort my data but now i am kind of stuck how to insert into the MYSQL table. After the data is processed i just want a simple INSERT INTO query to save the row into the table. The application is very simple, Just load the file and show a progress bar while working. But I can't really figure the MYSQL part in Pascal, I don't need gui components, I just want to "talk" the database directly, dumping the result. Can any one give me some suggestions, Maybe point me in the right direction. Perhaps a tutorial? Thank you   BTW ... I find Lazarus great, Thanks for all the work put in to it ---------------------------------------------------------------------- tudi_x · 2018-07-24 07:57 might help. there is info on insert, just point to your db: http://wiki.freepascal.org/Working_With_TSQLQuery ---------------------------------------------------------------------- asdlinux · 2018-07-24 08:22 Thank you... I will look into this... I think it can be what i am looking for. I do know SQL, I am just not sure of what Libraries/Components I need to implement it in Lazarus If there is another ways, Please let me know too. I am always curious to find out new stuff i can do ---------------------------------------------------------------------- tudi_x · 2018-07-24 08:36 might help, you can follow the see also links maybe you find something usefull for you: http://wiki.lazarus.freepascal.org/SQLdb_Tutorial0 ---------------------------------------------------------------------- sash · 2018-07-24 10:29 A typical data pump pattern: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   //load SourceDataset here   with TSQLQuery.Create(nil) do    try      // set connection, transaction.....      SQL.Text := 'insert into table1 (f1,f2) values (:f1, :f2)';      Prepare;       SourceDataset.First;    while not SourceDataset.Eof do begin         Params.CopyParamValuesFromDataset(SourceDataset); //if fields are the same        // or copy manually ---v        // ParamByName('f1').Value := SourceDataset.FieldByName('f1').Value;        ExecSQL;        SourceDataset.Next;         // (batch) commit if requited      end;     // commit if requited     finally      Free    end; ---------------------------------------------------------------------- mtournay · 2018-07-24 13:35 You can use TCSVdataset as source but be careful, actual version in 1.8.4 is buggy, but corrected in trunk : Actually you cannot change FirstLineAsFieldNames and CSV delimiter in options... Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   result := TCSVDataset.Create(self);  result.CSVOptions.Delimiter := ';';  result.CSVOptions.FirstLineAsFieldNames := false;  result.FieldDefs.add('f1', ftString,27);  result.FieldDefs.add('f2', ftString,50); ---------------------------------------------------------------------- mangakissa · 2018-07-25 06:54 QuoteYou can use TCSVdataset as source....Or you build your own parser with TStringlist Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var Importfile : TStringlist;      ImportLine : TStringlist;      teller : integer;begin  Importfile := TStringlist.create;  ImportLine := TStringlist.create;  try    ImportLine.Delimiter := ';';    ImportLine.StrictDelimiter := true;    ImportLine.Delimited := true;  ImportFile.loadfromfile('myfile.csv');    for teller := 0 to Importfile.count - 1 do    begin      ImportLine.delimitedtext := Importfile[teller];      // do your thing    end;  finally    ImportFile.free;    ImportLine.Free;  end;end; ---------------------------------------------------------------------- egsuh · 2018-07-25 09:05 If your csv file is from MS Excel, the easiest way is to use TStringList.CommaText. Assuming that   1)  your SQL Query has the following SQL statement,        insert into TableName (f1, f2, ... , fn) values (:f1, :f2, :f3, ... , :fn)    2)  In your Excel file, the last column is filled in all rows.    3)  All data format in the Excel file are correct (No characters in number field).   Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i const     filename = '.....'; var     AStrList: TStringList;      InputFile: TextFile;      s : string;      i : integer;      begin     AStrList := TStringList.Create;      AssignFile (InputFile, filename);      Reset(Inputfile);      readln(inputfile, s); // if the first row has titles, you should have to read this line out.      while not eof(TextFile) do begin           readln(inputfile, s);          AStrList.CommaText := s;            SQLQuery1.Close;          SQLQuery1.Prepare;          for i := 0 to AStrList.Count-1 do              SQLQuery1.Params[i].AsString := AStrList[i];           // ... possibly start transaction ...          SQLQuery1.ExecSQL;          // ... transaction.commit...    end; // while    CloseFile (Inputfile);  AStrList.Free; end; If MySQL's systax of "insert" SQL is different, then you should modify it accordingly. The style of reading text file is rather old-fashioned, but it's not a big deal. ---------------------------------------------------------------------- Thaddy · 2018-07-25 09:12 There is a good reason that FPC contains many example sourcecode. Why not have a look at /fpc311/packages/fcl- db/examples/dbftool.lpr...? ---------------------------------------------------------------------- ASerge · 2018-07-25 23:31 After reading the comments on how to import CSV files, I would like to give a warning. Many applications, including Excel, save CSV file in DSV format. For example Excel. It depends on local user settings when exporting: Text code page The field separator is equal to DefaultFormatSettings.ListSeparator The row separator is CRLF Multiple lines in a cell are separated by only CR (and in double quotes) Double quote in cell is repeated twice. Other applications may use different rules. As a result, it is considered that CSV files are a bad option for data transfer, because a lot of refinements are required during import. ---------------------------------------------------------------------- taazz · 2018-07-26 01:57 Quote from: ASerge on July 26, 2018, 01:31:48 amAfter reading the comments on how to import CSV files, I would like to give a warning. Many applications, including Excel, save CSV file in DSV format. For example Excel. It depends on local user settings when exporting: Text code page The field separator is equal to DefaultFormatSettings.ListSeparator The row separator is CRLF Multiple lines in a cell are separated by only CR (and in double quotes) Double quote in cell is repeated twice. Other applications may use different rules. As a result, it is considered that CSV files are a bad option for data transfer, because a lot of refinements are required during import. there are only 2 groups of text files, csv which is a generic group that describes dynamically sized fields separated by a known character usually comma or semicolon and flv which are fixed length values that use specific known character for the empty space eg space for text fields and 0 for numeric. Any attempt to characterize the type of file outside this two is (although brave) misplaced. To put it simple just because a xml file uses a tag you have never seen before it does no stop being a xml file. And no it is not expected to see only cr in the "cell value" (which is a data field) for multiline values, crlf is also an acceptable choice, the only thing that makes it a single field value is the double (or single) quotes enclosing the value (yes the quotes are configurable too). As for the two double quotes it is the same practice with any other data language (ee sql) there is no predefined escape character to make things more complex so two consecutive control characters are considered as a single value character and not a control character. All this is taken care for you in the csvdocument class that comes with fpc big chimp and I made sure that it worked according to the csv specifications a couple of years back. ---------------------------------------------------------------------- SeregaKR · 2018-07-26 05:05 Other way is to use FPSpreadSheet - this component allow you to work with xls, xlsx, odt, csv files. Look here  http://wiki.freepascal.org/FPSpreadsheet . Recently I had to do the similar thing - open xls file, work with it and then save it to dbf for future work. ---------------------------------------------------------------------- ASerge · 2018-07-26 23:36 Quote from: taazz on July 26, 2018, 03:57:33 amthere are only 2 groups of text files, ...(the obvious things are omitted)... Any attempt to characterize the type of file outside this two is (although brave) misplaced. It's just your point of view. As I've shown above, the Excel (microsoft) developers have their own opinion of what a csv file should look like. QuoteAnd no it is not expected to see only cr in the "cell value" (which is a data field) for multiline values, crlf is also an acceptable choice... Of course, I did not claim it. I just gave an example that Excel exports this way. QuoteAll this is taken care for you in the csvdocument class that comes with fpc big chimp and I made sure that it worked according to the csv specifications a couple of years back. But to make it work, it also needs to be adjusted to the file. Have you looked at the @mangakissa and @mtournay posts above? They explicitly specify Delimiter:=';', that is, in their local properties, the delimiter is not a comma, but a semicolon. And you need to know this before you use TCSVDocument. For example, this fail: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i program Project1;{$MODE OBJFPC}{$LONGSTRINGS ON}{$APPTYPE CONSOLE} uses csvdocument; const  CCSVText = 'Col0;Col1;Col2' + sLineBreak + 'Data0;Data1;Data2';var  Doc: TCSVDocument;begin  Doc := TCSVDocument.Create;  try//    Doc.Delimiter := ';';    Doc.CSVText := CCSVText;    Writeln(Doc.Cells[1, 1]);    Readln;  finally    Doc.Free;  end;end.And if you uncomment line 15, it will work. But initially, you need to know about it! ---------------------------------------------------------------------- taazz · 2018-07-26 23:58 Quote from: ASerge on July 27, 2018, 01:36:15 amQuote from: taazz on July 26, 2018, 03:57:33 amthere are only 2 groups of text files, ...(the obvious things are omitted)... Any attempt to characterize the type of file outside this two is (although brave) misplaced. It's just your point of view. As I've shown above, the Excel (microsoft) developers have their own opinion of what a csv file should look like. QuoteStandardization The name "CSV" indicates the use of the comma to separate data fields. Nevertheless, the term "CSV" is widely used to refer a large family of formats, which differ in many ways. Some implementations allow or require single or double quotation marks around some or all fields; and some reserve the very first record as a header containing a list of field names source https://en.wikipedia.org/wiki/Comma-separated_values so not just my point of view its a bit more wide spread. ---------------------------------------------------------------------- ASerge · 2018-07-27 00:14 Quote from: taazz on July 27, 2018, 01:58:43 amsource https://en.wikipedia.org/wiki/Comma-separated_values so not just my point of view its a bit more wide spread. Of course, I know that, but the reality is that some standards are not being met, as I warned.