6

According to https://www.sqlite.org/cli.html#csv,

sqlite> .mode csv
sqlite> .import C:/work/somedata.csv tab1

There are two cases to consider: (1) Table "tab1" does not previously exist and (2) table "tab1" does already exist.

In the first case, when the table does not previously exist, the table is automatically created and the content of the first row of the input CSV file is used to determine the name of all the columns in the table. In other words, if the table does not previously exist, the first row of the CSV file is interpreted to be column names and the actual data starts on the second row of the CSV file.

For the second case, when the table already exists, every row of the CSV file, including the first row, is assumed to be actual content. If the CSV file contains an initial row of column labels, that row will be read as data and inserted into the table. To avoid this, make sure that table does not previously exist.

I tried this and got an error:

$ cd /tmp
$ rm -f database.sqlite3
$ ( echo 'a,b,c' ; echo '1,2,3' ) > somedata.csv
$ sqlite3 database.sqlite3
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .version
SQLite 3.7.13 2012-07-17 17:46:21 65035912264e3acbced5a3e16793327f0a2f17bb
sqlite> .mode csv
sqlite> .import somedata.csv sometable
Error: no such table: sometable

However, if I create the table first, then it "works", but I end up with an unwanted header row since it is case (2):

sqlite> CREATE TABLE sometable (a, b, c);
sqlite> .import somedata.csv sometable
sqlite> SELECT * FROM sometable;
a,b,c
1,2,3

I've reproduced this problem using /usr/bin/sqlite3 3.7.13 in OS X 10.9.5 (as shown above) and version 3.7.3 in Debian 6.

Am I looking at the wrong documentation for my version of SQLite? Is there some mode I've neglected to set? Or is the SQLite documentation totally inaccurate?

  • I think what you've done is create a table with 1 field 'a, b, c' and 1 record ('1, 2, 3'). What happens if you try SELECT a FROM sometable? See here. – Vérace Apr 29 '15 at 22:01
  • @Vérace No, it works. The second excerpt is a continuation of the first, so .mode csv is still active. – 200_success Apr 29 '15 at 22:04
4

The documentation page you linked to is for the current version (3.8.9) of SQLite.

You could dig up the old version of cli.html for 3.7.13, but at that time, .import was pretty much undocumented.

0

You can use this tool to convert a CSV file to SQLite - http://converttosqlite.com/ - it will create a new table for you in the final SQLite DB

0

I had this exact problem. Updating to the latest version of SQLite3 (3.18) fixed it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.