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 ???

Boehike

See the link at CSV Import – Salmagundi

thanks, but i referred to that link only for planning the script. it is not of much help in present situation. – Boehike

Is the table already created before importing ? Can you show the content of testing.csv ? – Salmagundi

yes, i created the table before importing data. i have attached the screenshot of testing.csv for your reference. – Boehike

I think the command should be echo -e '.separator "," \n.import testing.csv aj_test' | sqlite3 ajtest.db because the table you are importing into is aj_test. – Salmagundi

sorry I did a typo during posting the question. it is indeed aj_test – Boehike

The script works on me. Can you show me the content of testing.csv which is the result of cat testing.csv. – Salmagundi

echo -e 'attach database 'ajtest.db' as 'aj';\n.separator ","\n.import testing.csv aj_test' | sqlite3 ajtest.db This worked !!! I guess i needed to use an-- attach database ' ' as ' ' but why ??? – Boehike

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.

Boehike

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.

Kozlowski

i will definitely try with your solution and update. thanks. – Boehike

(echo .mode csv; echo .import path/to/file.csv table_name) | sqlite3 filename.db – Croissant

Answer 3

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"

Ap

This should be the accepted answer. Way more clean then the echo solutions. – Francis

Answer 4

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!

Impasto

I like this option, but it leaves me inside the sqlite shell. – Ap

Thanks for your contribution... it led me to try this (which worked): sqlite3 -separator ',' stuff.db ".import myfile.csv t_table_name" – Ap