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.  

Answer 1

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

Answer 2

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

Answer 3

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?