Import CSV to SQLite database in command line or Python

Question

At Bernie's request I'm trying to condense this to a simpler example:

I have a CSV file, which contains a month where the days of the week are the column headers:

Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday
1,2,3,4,5,6,7
8,9,10,11,12,13,14
15,16,17,18,19,20,21
22,23,24,25,26,27,28

In the command line, I've created an SQLite table, days:

sqlite> CREATE TABLE days(
   ...> Monday int,
   ...> Tuesday int,
   ...> Wednesday int,
   ...> Thursday int,
   ...> Friday int,
   ...> Saturday int,
   ...> Sunday int
   ...> );

When attempting to import the data from the csv, here's what I get:

sqlite> .import example.csv days
Error: example.csv line 1: expected 7 columns of data but found 1

How can I import this csv file to the database such that it recognizes every new row? Thanks!

Sorry--I just started learning about this a few days ago; could you explain how I could do that? — Brett Shollenberger
– Brett Shollenberger, Commented Feb 18, 2013 at 23:52
(No need to apologise. Everyone has to start somewhere.) You might, for example, create a very small CSV file and test out your code on that. Once you have that code working try it with your real-world project. — mechanical_meat
– mechanical_meat, Commented Feb 18, 2013 at 23:55
Well I'm glad a community like this exists to help! I updated my example above so it's more manageable. — Brett Shollenberger
– Brett Shollenberger, Commented Feb 19, 2013 at 0:07
Ah, yes that's how I ended up with the other error I'd gotten: Error: example.csv line 1: expected 7 columns of data but found 31 — Brett Shollenberger
– Brett Shollenberger, Commented Feb 19, 2013 at 0:13
Hm. I'm still producing the same issue. Is there something I'm missing? Should I not be doing this in the command line? — Brett Shollenberger
– Brett Shollenberger, Commented Feb 19, 2013 at 0:19

kfblake · Accepted Answer · 2013-07-14 16:43:47Z

1

You need to include the following line prior to executing the .import command:

.separator ,

This tells the import command to look for the separator (in this case, comma).

You can find more about sqlite command line commands here: http://www.sqlite.org/sqlite.html

answered Jul 14, 2013 at 16:43

kfblake

1281 silver badge9 bronze badges

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

Comments

Roger Binns · Accepted Answer · 2013-07-15 03:54:14Z

The SQLite shell is rather finnicky. The current version doesn't do row headers, and its behaviour diverges between the standard (RFC 4180) and common practise. The upcoming 3.8 release will do row headers.

Since you are using Python, you may find the APSW Shell useful (disclosure: I am the author). You can use it from the command line just like the SQLite shell, and you can use it programmatically including adding your own commands.

Of note is that it has an autoimport command that just figures everything out including headers, separators, data types etc.

sqlite> .help autoimport

.autoimport FILENAME ?TABLE?  Imports filename creating a table and
                              automatically working out separators and data
                              types (alternative to .import command)

The import command requires that you precisely pre-setup the table and schema,
and set the data separators (eg commas or tabs).  In many cases this information
can be automatically deduced from the file contents which is what this command
does.  There must be at least two columns and two rows.

If the table is not specified then the basename of the file will be used.

Additionally the type of the contents of each column is also deduced - for
example if it is a number or date.  Empty values are turned into nulls.  Dates
are normalized into YYYY-MM-DD format and DateTime are normalized into ISO8601
format to allow easy sorting and searching.  4 digit years must be used to
detect dates.  US (swapped day and month) versus rest of the world is also
detected providing there is at least one value that resolves the ambiguity.

Care is taken to ensure that columns looking like numbers are only treated as
numbers if they do not have unnecessary leading zeroes or plus signs.  This is
to avoid treating phone numbers and similar number like strings as integers.

This command can take quite some time on large files as they are effectively
imported twice.  The first time is to determine the format and the types for
each column while the second pass actually imports the data.

user3573558 · Accepted Answer · 2014-12-03 01:38:01Z

0

Check out termsql, it's a tool made for purposes like this one. Your task would be very easy with it.

Manual: http://tobimensch.github.io/termsql/

Check the examples at the bottom of the page. There's one for CSV import there, and also check what the different options are.

Project: https://github.com/tobimensch/termsql

answered Dec 3, 2014 at 1:38

user3573558

1901 silver badge1 bronze badge

Collectives™ on Stack Overflow

Import CSV to SQLite database in command line or Python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related