Recent

Author Topic: Import and export CSV files to and from SQLite database  (Read 4817 times)

MoellerCLaus

  • Full Member
  • ***
  • Posts: 110
    • Vig Foreningsprogram
Import and export CSV files to and from SQLite database
« on: July 25, 2021, 09:34:24 pm »
I would love to be able to export and import approx 43000 records into a SQLLite database.

The classical approach take between 13-15 minutes - which is a long time. I read it is because of the disc speed.

In sqllite there is though a possibility to import csv directly. But I would like to that do in my code.

Has anybody done likewise and can I see a code snippet

Thanks.

wp

  • Hero Member
  • *****
  • Posts: 8907
Re: Import and export CSV files to and from SQLite database
« Reply #1 on: July 25, 2021, 10:06:29 pm »
What is the "classical approach"? I cannot believe that import/export take more than 10 seconds. Are there any visual components hooked to the SQLite database? Unhook them, or call Dataset.DisableControls/.EnableControls.
« Last Edit: July 25, 2021, 10:29:49 pm by wp »
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

wp

  • Hero Member
  • *****
  • Posts: 8907
Re: Import and export CSV files to and from SQLite database
« Reply #2 on: July 25, 2021, 11:46:01 pm »
I don't get it... Your title is that you want to import and export CSV files to/from SQLite. Now there's also an Access DB. Do you want to export the records from the Access DB to SQLite and use CSV as an intermediate format? What is your intention?
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

MoellerCLaus

  • Full Member
  • ***
  • Posts: 110
    • Vig Foreningsprogram
Re: Import and export CSV files to and from SQLite database
« Reply #3 on: July 26, 2021, 07:56:26 am »
Sorry my mistake.

I need to import and export approx. 43.000 records. I have tried several methods; mdb (the original), csv (converted via access).

Then I read that Sqllite has a direct possiblity to read CSV. I found examples via the command tool.
Code: Pascal  [Select][+][-]
  1. sqlite> .mode csv table_name
  2. sqlite> .import file_name.csv table_name
But I am looking for the fastest method via Lazarus-code to read in data from a file in format x into Sqllite.
Hopes this makes it clearer :-X

af0815

  • Hero Member
  • *****
  • Posts: 734
Re: Import and export CSV files to and from SQLite database
« Reply #4 on: July 26, 2021, 10:00:29 am »
BTW: Normally you can speedup insert in a DB for import, if you decide to deactivate the transaction control or use only one transaction for the whole import. If you insert row for row, you have normal a transaction open, insert, transaction commit package. And this will slow down all inserts.
regards
Andreas

egsuh

  • Hero Member
  • *****
  • Posts: 855
Re: Import and export CSV files to and from SQLite database
« Reply #5 on: July 26, 2021, 05:08:08 pm »
I don't get your issue correctly. Anyway general approach would be like (assuming you have same field orders between source and target tables):


Code: Pascal  [Select][+][-]
  1.    SrcQuery.SQL.Text:= 'select * from source_table';
  2.    Targetquery.SQL.Text:= 'insert into target_table values (:v1, :v2, .... :vn)';   // as many as field number
  3.    transaction.active:= true;
  4.    SrcQuery.Open;
  5.    Srcquery.First;
  6.    while not SrcQuery do begin
  7.         TargetQuery.Prepare;
  8.         for ti:= 0 to SrcQuery.Fields.count - 1 do
  9.              Params[ti].AsString := SrcQuery.Fields[ti].AsString;
  10.         TargetQuery.ExecSQL;
  11.         SrcQuery.Next;
  12.    end;
  13.    transaction.commit;
  14.  


43K records... I don't think this will take more than...  seconds or tens of seconds.
« Last Edit: July 26, 2021, 05:11:24 pm by egsuh »

y.ivanov

  • Sr. Member
  • ****
  • Posts: 290
Re: Import and export CSV files to and from SQLite database
« Reply #6 on: July 26, 2021, 06:04:33 pm »
As for SQLite - it inserts very slowly (in the default configuration) as long as you don't open a transaction and insert all rows in a single batch as af0815 suggested. Then it works pretty fast.

MoellerCLaus

  • Full Member
  • ***
  • Posts: 110
    • Vig Foreningsprogram
Re: Import and export CSV files to and from SQLite database
« Reply #7 on: July 27, 2021, 10:05:19 am »
 ;) Thanks for your kind advice.
I decided to load a csv file in to a hidden/not visible stringgrid.

Then the folloving code did it in seconds..
Code: Pascal  [Select][+][-]
  1.   ZqueryOcto.Connection.AutoCommit:=False;
  2.   While A < StringGrid1.RowCount Do
  3.     Begin
  4.       ZQueryOcto.Append;
  5.       ZQueryOcto.Edit;
  6.       ZQueryOcto.FieldByName('1').AsString := Stringgrid1.Cells[0,A];
  7.       ZQueryOcto.FieldByName('2').AsString := Stringgrid1.Cells[1,A];
  8.       ZQueryOcto.FieldByName('3').AsString := Stringgrid1.Cells[2,A];
  9.       .....
  10.       ZQueryOcto.Post;
  11.       Progressbar1.StepIt;
  12.       Inc(A);
  13.       Inc(TimeToSave);
  14.       If TimeToSave > 1000 Then
  15.         Begin
  16.           TimeToSave := 0;
  17.           ZqueryOcto.Connection.Commit;
  18.         end;
  19.     end;
  20.   ZQueryOcto.Connection.Commit;
  21.   ZQueryOcto.Connection.AutoCommit := True;
  22.   ShowMessage('Done');
  23.  

 

TinyPortal © 2005-2018