Importing .csv file to sqlite3 db table

Question

I wrote a single line shell script to import a .csv file to sqlite3 database table.

echo -e '.separator "," \n.import testing.csv aj_test' | sqlite3 ajtest.db

sqlite3 database = ajtest.db sqlite3 table in ajtest.db = new_test

the testing.csv has 3 columns, first one is int the rest two are texts; so accordingly the structure of new_test is also--

sqlite> .schema aj_test
CREATE TABLE aj_test(number integer not null,
first_name varchar(20) not null,
last_name varchar(20) not null);

when the script is run, it does not show any error, but it also does not import any data. any guidelines as to what I have missed ???

Answer 1

After much studies and discussion, I found an answer that is working properly,

echo -e ".separator ","\n.import /home/aj/ora_exported.csv qt_exported2" | sqlite3 testdatabase.db

the main thing is, that I needed to include the path of the .csv file in the import statement.

Answer 2

I found this to work:

(echo .separator ,; echo .import path/to/file.csv table_name) | sqlite3 filename.db

The accepted answer fails to work for me.

Answer 3

Why don't you take advantage of Sqlite's built-in command-line options to load CVS file to Sqlite database table? I assume you are writing bash shell script to load CSV files data to SQLite table.

Have a look on bellow single line bash script:

#!/bin/bash

sqlite3  -separator "," -cmd ".import /path/to/test.csv aj_test" ajtest.db

With my limited knowladge, I can't give you any example to automatically logout sqlite cli after being load done on db!

Answer 4

This worked best for my needs because it is straightforward (unlike the echo solutions) and it doesn't leave the sqlite shell open.

#!/bin/bash 

sqlite3 -separator ',' stuff.db ".import myfile.csv t_table_name"