Sqlite loads CSV files

Sqlite loading CSV files sqlite loading external CSV files is a common requirement. assume there is an sqlite database test. there is a table named test in the db. the corresponding SQL statement is: CREATETABLEtest (idINTEGERNOTNULL, nameTEXTNOTNULL, phoneTEXTNOTNULL, PRIMARYKEY (id); there is an external CSV file dat

Sqlite loading CSV files sqlite loading external CSV files is a common requirement. assume there is an sqlite database test. there is a table named test in the db. the corresponding SQL statement is: CREATE TABLE test (id INTEGER NOT NULL, name TEXT NOT NULL, phone TEXT NOT NULL, PRIMARY KEY (id); there is an external CSV file dat

Sqlite loads CSV files

Loading external CSV files by sqlite is a common requirement.

Assume that there is a table named test in test. db of sqlite database. The corresponding SQL statement is:

CREATE TABLE "test" (""id"  INTEGER NOT NULL,""name"  TEXT NOT NULL,""phone"  TEXT NOT NULL,""PRIMARY KEY ("id"));

There is an external csvfile data.csv with the following content:

5, aaaa, 139 ******

2, bbbb, 138 ******

3, cccc, 136 ******

There are two ways to get the CSV data to the test table in the database.

1. parse the CSV file and INSERT the data INTO the table row by row.

Because you already know the file format, it is not difficult to parse such CSV files. Some languages (or some libraries) have spilt for string processing.

There may be a lot of data. Don't directly insert into. It takes a lot of time to enable the transaction to save energy. The Qt code I wrote below is for your reference.

[Html] QFile file (filePath); if (! File. open (QFile: ReadOnly) {qDebug () <"file opening failed"; qDebug () <file. errorString () ;}qtextstream in (& file); pSqlDatabase _-> transaction (); while (! In. atEnd () {QString str; in> str; qDebug () <str; QStringList strs = str. split (","); if (3 = strs. size () {QString cmd = QString ("insert into test VALUES ('% 1',' % 2', '% 3 ')"). arg (strs. at (0 )). arg (strs. at (1 )). arg (strs. at (2); qDebug () <cmd; pQuery _-> exec (cmd) ;}} pSqlDatabase _-> commit ();

2. Use the sqlite. import file table command.

The method here is not so straightforward. Before you execute the. import file table command, you need to call. separator '. 'set the delimiter in the command line is roughly like this:

sqlite3 test.db.separator ','.import data.csvtest

However, many language libraries can open external programs, but it hurts to interact after opening them. Here we use a curve to save the country.

Take Windows as an example. For other platforms, modify the script and write a Windows batch of load. bat content as follows:

@ Echo off echo. separator ','> tmp echo. import data.csv test> tmp sqlite3.exe test. db 0
 
  
 
  

  
Then you can call this batch from the external.