8

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

content of testing.csv

Improve this question
8
  • 1
    See the link at CSV Import Commented Dec 9, 2014 at 5:22
  • 1
    thanks, but i referred to that link only for planning the script. it is not of much help in present situation. Commented Dec 9, 2014 at 5:23
  • 1
    Is the table already created before importing ? Can you show the content of testing.csv ? Commented Dec 9, 2014 at 5:28
  • 1
    yes, i created the table before importing data. i have attached the screenshot of testing.csv for your reference. Commented Dec 9, 2014 at 5:40
  • 1
    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. Commented Dec 9, 2014 at 6:09

4 Answers 4

Reset to default
9

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

6

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.

Improve this answer

2 Comments

i will definitely try with your solution and update. thanks.
(echo .mode csv; echo .import path/to/file.csv table_name) | sqlite3 filename.db
6

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"
Improve this answer

1 Comment

This should be the accepted answer. Way more clean then the echo solutions.
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!

Improve this answer

2 Comments

I like this option, but it leaves me inside the sqlite shell.
Thanks for your contribution... it led me to try this (which worked): sqlite3 -separator ',' stuff.db ".import myfile.csv t_table_name"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.