Importing data to sqlite

Question

Somebody, please help me, because my head going to explode.

I'm trying to import data in sqlite3. I have promos.sql and promos.csv files. I've tried to run this in the sqlite shell:

.read promos.sql

Or do this at a unix prompt:

sqlite3 development.sqlite3<promos.sqlite3

and each time I get error

Error: near line 1: near ",": syntax error  
Error: near line 18: near ",": syntax error e.t.c.

In file are following rows:

INSERT INTO promos (name, promo_type, category, phone, email, message, created_at)  VALUES  
('John', 1, 3, '+111 11 111 111 11', '[email protected]', 'Some message', '2009-09-24 12:17:17'),etc

so, it complains on error in each line where goes columns enumeration.

If I try

.import promos.csv promos

it says:

Error: promos.csv line 1: expected 9 columns of data but found 1

but in file are 9 columns as expected:

"13","John","1","3","+111 11 111 111 11","[email protected]","Some message","2009-09-24 15:17:17", "2009-09-24 15:17:17"

Why is it not working?

Rory · Accepted Answer · 2012-05-26 15:19:56Z

5

For the sql problem: do you have commas at the end of your INSERT statments instead of semicolons like you should have? Please post several lines of your sql file that you're trying to run, and also post the CREATE TABLE statement for promos.

For the csv problem: sqlite uses | as the default separator, so you'll have to set it to use comma. Furthermore, sqlite import doesn't recognise quoted columns so if you'll have commas in your data you're best using a different method to import the data. See this article for details.

answered May 26, 2012 at 15:19

Rory

42.2k56 gold badges192 silver badges271 bronze badges

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

2 Comments

RaskolnikOFF Over a year ago

Thanks a lot. You've got me a right idea. Originally I've used sentece like INSERT INTO tbl_name (col_A,col_B) VALUES (1,2,3), (4,5,6), (7,8,9); but seems sqlite doesn't support this format. So I've made new SQL-file with separate queries for each record, and it works.

Rory Over a year ago

yeah, SQL generally doesn't support that syntax. You either need separate INSERT statements or you can do something like

INSERT INTO mytable SELECT 'aValue', 'another value' UNION SELECT '2nd row', 'some value' UNION SELECT 'this is the 3rd row', 'xxx'

...

Collectives™ on Stack Overflow

Importing data to sqlite

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related