I want to use Lazarus to Import a CSV file into a MYSQL table.

[asdlinux]

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]

might help. there is info on insert, just point to your db:
http://wiki.freepascal.org/Working_With_TSQLQuery

[asdlinux]

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]

might help, you can follow the see also links maybe you find something usefull for you:
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial0

[sash]

A typical data pump pattern:

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

1.   //load SourceDataset here
2.  
3.   with TSQLQuery.Create(nil) do
4.     try
5.       // set connection, transaction.....
6.       SQL.Text := 'insert into table1 (f1,f2) values (:f1, :f2)';
7.       Prepare;
8.  
9.       SourceDataset.First;
10.       while not SourceDataset.Eof do begin
11.  
12.         Params.CopyParamValuesFromDataset(SourceDataset); //if fields are the same
13.         // or copy manually ---v
14.         // ParamByName('f1').Value := SourceDataset.FieldByName('f1').Value;
15.         ExecSQL;
16.         SourceDataset.Next;
17.  
18.         // (batch) commit if requited
19.       end;
20.  
21.       // commit if requited
22.  
23.     finally
24.       Free
25.     end;
26.  
27.  

[mtournay]

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][+][-]

1.   result := TCSVDataset.Create(self);
2.   result.CSVOptions.Delimiter := ';';
3.   result.CSVOptions.FirstLineAsFieldNames := false;
4.   result.FieldDefs.add('f1', ftString,27);
5.   result.FieldDefs.add('f2', ftString,50);
6.  

[mangakissa]

You can use TCSVdataset as source....

Or you build your own parser with TStringlist

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

1. var Importfile : TStringlist;
2.       ImportLine : TStringlist;
3.       teller : integer;
4. begin
5.   Importfile := TStringlist.create;
6.   ImportLine := TStringlist.create;
7.   try
8.     ImportLine.Delimiter := ';';
9.     ImportLine.StrictDelimiter := true;
10.     ImportLine.Delimited := true;
11.     ImportFile.loadfromfile('myfile.csv');
12.     for teller := 0 to Importfile.count - 1 do
13.     begin
14.       ImportLine.delimitedtext := Importfile[teller];
15.       // do your thing
16.     end;
17.   finally
18.     ImportFile.free;
19.     ImportLine.Free;
20.   end;
21. end;
22.  

[egsuh]

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][+][-]

1. const
2.      filename = '.....';
3. var
4.      AStrList: TStringList;
5.      InputFile: TextFile;
6.      s : string;
7.      i : integer;
8.      
9. begin
10.      AStrList := TStringList.Create;
11.      AssignFile (InputFile, filename);
12.      Reset(Inputfile);
13.      readln(inputfile, s); // if the first row has titles, you should have to read this line out.
14.      while not eof(TextFile) do begin
15.           readln(inputfile, s);
16.           AStrList.CommaText := s;
17.  
18.           SQLQuery1.Close;
19.           SQLQuery1.Prepare;
20.           for i := 0 to AStrList.Count-1 do
21.                SQLQuery1.Params[i].AsString := AStrList[i];
22.  
23.          // ... possibly start transaction ...
24.          SQLQuery1.ExecSQL;
25.          // ... transaction.commit...
26.     end; // while
27.     CloseFile (Inputfile);
28.     AStrList.Free;
29. end;
30.  

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]

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]

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]

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.

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]

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]

there 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.

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

Of course, I did not claim it. I just gave an example that Excel exports this way.

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.

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][+][-]

1. program Project1;
2. {$MODE OBJFPC}
3. {$LONGSTRINGS ON}
4. {$APPTYPE CONSOLE}
5.  
6. uses csvdocument;
7.  
8. const
9.   CCSVText = 'Col0;Col1;Col2' + sLineBreak + 'Data0;Data1;Data2';
10. var
11.   Doc: TCSVDocument;
12. begin
13.   Doc := TCSVDocument.Create;
14.   try
15. //    Doc.Delimiter := ';';
16.     Doc.CSVText := CCSVText;
17.     Writeln(Doc.Cells[1, 1]);
18.     Readln;
19.   finally
20.     Doc.Free;
21.   end;
22. end.

And if you uncomment line 15, it will work. But initially, you need to know about it!

[taazz]

there 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.

Standardization
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]

source 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.