sqlite error importing csv through command line

Question

$ sqlite3 test.sql
SQLite version 3.6.12
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test (id integer, author_id integer, title varchar(128), name text);
sqlite> .separator ";"
sqlite> .import sqlite.csv test
sqlite.csv line 3: expected 4 columns of data but found 1
sqlite> .separator ';'
sqlite> .import sqlite.csv test
sqlite.csv line 3: expected 4 columns of data but found 1
sqlite>

I am trying to import the csv table with ; as a seperator to sqlite but it wasn't able to find 4 columns. I export from sql to csv with checked 'Put fields names in the first row'. Could I be missing something here?

first 5 lines of csv

id;"author_id";"title";"poem"       
1;"92";"A Letter From Italy";"Salve magna parens frugum Saturnia tellus     
Magna virm! tibi res antiqu laudis et artis     
Aggredior    sanctos ausus recludere fontes.    
Virg. Geor. 2.

Can you show us the first 5 lines of your csv file? – MatBailie Jan 16 '12 at 10:33 — MatBailie, Jan 16 '12 at 10:33
@Dems just posted up the first 5 lines of csv file – merrill Jan 17 '12 at 01:14 — merrill, Jan 17 '12 at 01:14

score 6 · Answer 1 · answered Jan 19 '12 at 05:59

6

Since your separator is only a single character, try using the separator command without quotes around the semicolon. So:

sqlite> .separator ;
sqlite> .import sqlite.csv test

answered Jan 19 '12 at 05:59

Mike Monteiro

1,427
1
14
21

score 5 · Accepted Answer · edited May 23 '17 at 12:19

5

You can't import into a table with a primary key you have to import into a temp table first.

See the answer to this SO question

edited May 23 '17 at 12:19

Community

1
1

answered Jul 11 '12 at 06:03

bytebender

7,371
2
31
54

score 1 · Answer 3 · answered Jan 19 '12 at 05:56

1

Maybe there's a line break in one of your strings that isn't being properly escaped? So it thinks that the 2nd line ends after "tellus" and then tries to parse the text starting with Magna as the 3rd line, and finds no semicolon delimiters. Can you post a screenshot of what the CSV looks like when opened in textpad?

answered Jan 19 '12 at 05:56

Mike Monteiro

1,427
1
14
21

it looks exactly like how i posted up :) – merrill Jan 20 '12 at 06:24
exactly my point. In the sample you posted, the second line stops at the word "tellus" and the third line begins with "Magna." The error implies that it's able to parse the first 2 lines just fine (it's finding 3 ";"s and hence 4 columns) and is breaking on the third. This would imply that the line break between "tellus" and "Magna" isn't just formatting on stackoverflow.com, it's a line break that's in your text file. You need to either remove the linebreak or escape it. Let me know if you need help doing either of those things. – Mike Monteiro Jan 21 '12 at 00:51

sqlite error importing csv through command line

3 Answers3

Linked