Import and export CSV files to and from SQLite database
lazarus / 16-Databases

----------------------------------------------------------------------
MoellerCLaus · 2021-07-25 19:34

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 · 2021-07-25 20:06

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.

----------------------------------------------------------------------
wp · 2021-07-25 21:46

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?

----------------------------------------------------------------------
MoellerCLaus · 2021-07-26 05:56

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i sqlite>
.mode csv table_namesqlite> .import file_name.csv table_nameBut 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

----------------------------------------------------------------------
af0815 · 2021-07-26 08:00

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.

----------------------------------------------------------------------
egsuh · 2021-07-26 15:08

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
 SrcQuery.SQL.Text:= 'select * from source_table';  
 Targetquery.SQL.Text:= 'insert into target_table values (:v1, :v2,
.... :vn)';   // as many as field number   transaction.active:= true;
  SrcQuery.Open;    Srcquery.First;    while not SrcQuery do begin   
    TargetQuery.Prepare;         for ti:= 0 to SrcQuery.Fields.count -
1 do              Params[ti].AsString := SrcQuery.Fields[ti].AsString;
        TargetQuery.ExecSQL;         SrcQuery.Next;   end;  
 transaction.commit;  

43K records... I don't think this will take more than...  seconds or
tens of seconds.

----------------------------------------------------------------------
alpine · 2021-07-26 16:04

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 · 2021-07-27 08:05

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
ZqueryOcto.Connection.AutoCommit:=False;  While A <
StringGrid1.RowCount Do    Begin      ZQueryOcto.Append;     
ZQueryOcto.Edit;      ZQueryOcto.FieldByName('1').AsString :=
Stringgrid1.Cells[0,A];      ZQueryOcto.FieldByName('2').AsString :=
Stringgrid1.Cells[1,A];      ZQueryOcto.FieldByName('3').AsString :=
Stringgrid1.Cells[2,A];      .....      ZQueryOcto.Post;     
Progressbar1.StepIt;      Inc(A);      Inc(TimeToSave);      If
TimeToSave > 1000 Then        Begin          TimeToSave := 0;        
ZqueryOcto.Connection.Commit;        end;    end; 
ZQueryOcto.Connection.Commit;  ZQueryOcto.Connection.AutoCommit :=
True;  ShowMessage('Done');

----------------------------------------------------------------------
JollyHong · 2026-08-21 11:11

Have you tried using an online SQLite editor that can handle CSV files
efficiently? There's this tool that lets you open and edit SQLite
databases directly in your browser without needing a server. It also
allows you to import CSV files quickly. You might find it useful to
bypass the lengthy classical approach by using [this SQLite
editor](https://editsqlite.com/) for a more seamless import/export
process.

----------------------------------------------------------------------
JollyHong · 2026-08-21 11:14

Quote from: JollyHong on August 21, 2026, 01:11:05 pmHave you tried
using an online SQLite editor that can handle CSV files efficiently?
There's this tool that lets you open and edit SQLite databases
directly in your browser without needing a server. It also allows you
to import CSV files quickly. You might find it useful to bypass the
lengthy classical approach by using https://editsqlite.com/ for a more
seamless import/export process.