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.  

Crowded

Can you show us the first 5 lines of your csv file? – Testimonial

@Dems just posted up the first 5 lines of csv file – Crowded

Answer 1

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

Decolorize

Answer 2

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

Serieswound

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?

Serieswound

it looks exactly like how i posted up :) – Crowded

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. – Serieswound

Answer 4

I had a similar issue importing a file with ; as the separator using the command line:

foo.csv:1: expected 1 columns but found 3 - extras ignored
foo.csv:2: expected 1 columns but found 3 - extras ignored
foo.csv:3: expected 1 columns but found 3 - extras ignored

I created the ImportBatch.bat file that can be use to import a file like this

C:\src>ImportBatch.bat C:/csv/input/foo.csv

Above foo.csv contains the records that you want to import. ImportBatch.bat reads a semicolon ; separated-file into a new in-memory-database and writes the database to disk. The batch file has this content

@echo off

if "%1"=="" (
    echo Usage: %0 [source_csv_file]
    exit /b 1
)

set SOURCE_CSV_FILE=%~1
set SQLITE_PATH=c:\Apps\SQLite\sqlite_3.40.1
set DEST_DB_FILE=%SOURCE_CSV_FILE%.db


(    
    echo .open ":memory:"
    echo .mode csv
    echo .separator ;
    echo .import %SOURCE_CSV_FILE% myTable
    echo .save %DEST_DB_FILE%
) | %SQLITE_PATH%\sqlite3.exe

It

• creates an in-memory database and
• imports the specified file (here foo.csv) with ; as a column separator and
• saves the imported rows to the same folder of the imported file under foo.csv.db

This is how it looks

Mathers