How to speed up the import of data from a CSV file into a SQLite table (on Windows)?

Question

When I was looking for a tool to create and update SQlite databases for use in an Android application, I was recommended to use SQLite Database Browser. It has a Windows graphical interface and is quite powerful, offering, in particular, a menu option for importing data into a new table from a CSV file.

This turned out to be superbly capable of initially creating the database, and I used the CSV import option to update the database whenever I have new data to add.

When there were only a few records to import it was good, but as the volume of data grew, the process became very slow. A data file of 11,000 records (800 kilobytes) takes about 10 minutes to import to my slow laptop. Using SQLite Database Browser, the whole process of deleting the old table that runs the import command, and then adjusting the data types of the new table created by the import command, takes most of 15 minutes.

How to speed up imports?

Answer 1

You can use the built-in csv import (using the sqlite3 command line utility):

create table test (id integer, value text);
.separator ","
.import no_yes.csv test

Importing 10,000 records took less than 1 second on my laptop.

Answer 2

googling , , , , . , .

sqlite3.exe . , " CSV" SQLite Database Browser , SQL- "insert" CSV . sqlite3.exe "import", . : 11 000 .

, import , , Excel. , A1 Excel Joe Bloggs B1 123 Main Street, Anytown CSV: Joe Bloggs,"123 Main Street, Anytown" , sqlite3 , sqlite3 , Joe Bloggs, "123 Main Street Anytown" 3 .

( Excel) , , , , .

sqlite3.exe SQL (, "" ), . , ​​ , :

• SQL sqlite3.exe sqlite3.exe

• Windows (MS-DOS) sqlite3.exe .

:

• sqlite3.exe
• , , , .

• script, sqlite3.exe :

  drop table tblTableName;

  create table tblTableName(_id INTEGER PRIMARY KEY, fldField1 TEXT, fldField2 NUMERIC, .... );

  .mode tabs

  .import SubfolderName/DataToBeImported.tsv tblTableName

  (: SQL : sqlite3.exe ())

• .bat :

  cd "c:\users\UserName\FolderWhereSqlite3DatabaseFileAndScriptFileAreStored"

  sqlite3 DatabaseName < textimportscript.txt

, , , , , .

Answer 3

If you generate INSERT statements, wrap them in a single transaction, as stated in the official SQLite FAQ :

BEGIN; -- or BEGIN TRANSACTION;

INSERT ...;
INSERT ...;

END; -- can be COMMIT TRANSACTION; also

Answer 4

Have you tried wrapping all your updates in a transaction? I had a similar problem and it did not slip away.

Assume an Android device:

db.beginTransaction();
// YOUR CODE
db.setTransactionSuccessful();
db.endTransaction();

Try it :)