2

I'm trying to add rows from a CSV file to an existing table.

In Mac Terminal, I start sqlite3:

sqlite> .open database.db

sqlite> .import test.csv table

. . .

test.csv:43200: expected 22 columns but found 1 - filling the rest with NULL

sqlite>

Notice there are 43200 rows in the csv file. The file itself has 22 comma separated columns, but sqlite only thinks there is 1 column.

cgaters
  • 21
  • 2
  • 5
  • Maybe your CSV file is not well formatted. Try to open it with notepad and check if every column are separated with comma. – probitaille Mar 26 '19 at 19:12
  • 1
    Here are the first 2 columns from the first row: 2019/03/21 00:00:01.813,-0.142040252686 all subsequent columns have the comma. In the first column is my timestamp. Is it possible that the space between the date and time is causing the problem? – cgaters Mar 26 '19 at 19:59
  • Your CSV is correct. I think your problem is related to this one: https://stackoverflow.com/questions/8878697/sqlite-error-importing-csv-through-command-line – probitaille Mar 26 '19 at 20:20
  • Thanks. I needed to change the mode to csv .mode csv – cgaters Mar 26 '19 at 20:29
  • Can you explain how you change this mode? – probitaille Mar 26 '19 at 20:30
  • 1
    In terminal, at the sqlite> prompt, type: .mode csv By default the mode is list. In list mode, the separator appears to be a pipe "|" – cgaters Mar 26 '19 at 20:51

1 Answers1

3

According to the finds of Cgaters and the official documentation of SQLite:

Use the ".import" command to import CSV (comma separated value) data into an SQLite table. The ".import" command takes two arguments which are the name of the disk file from which CSV data is to be read and the name of the SQLite table into which the CSV data is to be inserted.

Note that it is important to set the "mode" to "csv" before running the ".import" command. This is necessary to prevent the command-line shell from trying to interpret the input file text as some other format.

Example:

sqlite> .mode csv
sqlite> .import C:/work/somedata.csv tab1
Community
  • 1
  • 1
probitaille
  • 1,899
  • 1
  • 19
  • 37