CODEWITHSUNDEEP
pythonsqlite
Brett Shollenberger
Brett Shollenberger

Reputation: 225

Import CSV to SQLite database in command line or Python

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!

Upvotes: 0

Views: 2621

Answers (3)

user3573558
user3573558

Reputation: 190

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

Upvotes: 0

Roger Binns
Roger Binns

Reputation: 3348

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.

Upvotes: 0

kfblake
kfblake

Reputation: 128

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

Upvotes: 1

Related Questions