trying to import csv into a sqlite db fails with error - expected 9 columns but found 1 - filling the rest with NULL

Question

Background Information

I need to create a sqlite database from the command line (because all this will ultimately end up in a bash script) using a csv file as input.

Problem

When I try to run the .import command, I'm getting the error: expected 9 columns but found 1 - filling the rest with NULL

**Code / Command **

I'm trying to run the following command:

mydevbox:/tmp/# sqlite3 widgets.db ".import /tmp/widgets_2017-03-15-14-36.csv users"
/tmp/widgets_2017-03-15-14-36.csv:1: expected 9 columns but found 1 - filling the rest with NULL
/tmp/widgets_2017-03-15-14-36.csv:2: expected 9 columns but found 1 - filling the rest with NULL
/tmp/widgets_2017-03-15-14-36.csv:3: expected 9 columns but found 1 - filling the rest with NULL
/tmp/widgets_2017-03-15-14-36.csv:4: expected 9 columns but found 1 - filling the rest with NULL
/tmp/widgets_2017-03-15-14-36.csv:5: expected 9 columns but found 1 - filling the rest with NULL
/tmp/widgets_2017-03-15-14-36.csv:6: expected 9 columns but found 1 - filling the rest with NULL

The users table does not have a primary key. The schema looks like this:

mydevbox:/tmp# sqlite3 widgets.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE users(fname varchar(100), lname varchar(100), description varchar(100),grp INTEGER, group_name varchar(100), destination_id varchar(100), w_number varchar(10), destination varchar(100), class varchar(10));
sqlite>

I thought it might be because I'm not specifying a separator. So I tried this:

mydevbox:/tmp# sqlite3 widget.db ".separator ,; .import /tmp/widgets_2017-03-15-14-36.csv users"
Usage: .separator COL ?ROW?

As you can see it's failing with an error about the separator.

Here's what the first few rows of the csv looks like:

Jane,Doe,asdf,0,"",1,1234,4444,sip
John,Doe,,0,,2,12752,[email protected],smtp
John,Doe,,0,,2,12752,[email protected],smtp
JJ,Blaird,,0,,4,12477,[email protected],smtp

Any suggestions would be appreciated. FWIW, this csv is created via a select statement against a postgresql database.

Answer 1

just use the commandline flag for that:

sqlite3 widget.db -separator ',' '.import /tmp/widgets_2017-03-15-14-36.csv users'

That worked for me.