SQLite Forum

How to import a csv file whose columns are separated by semicolons

How to import a csv file whose columns are separated by semicolons

(1) By Alexandre Loures (alexandreloures) on 2020-10-08 02:18:22 [link] [source]


I have a base with 81,372,577 observations and whose columns are separated by semicolons. I am using the following command to import this database:

.mode csv .separator ";" .import c: /users/inspiron/desktop/people2012.csv people2012

However, the result for this command is:

CREATE TABLE people2012 (...) failed: duplicate column name:

So, as the procedure didn't work, I tried to create the table first (with the columns separated by a comma):

CREATE TABLE people2012 ( cod_familiar_fam NUMERIC NULL, num_membro_fmla INT NULL, ... );

Then, follow these steps:

.separator ";" .import c: /users/inspiron/desktop/people2012.csv people2012

The result of which is:

c: /users/inspiron/desktop/pessoas2012.csv:11: expected 120 columns but found 1 - filling the rest with NULL

However, when specifying the base, there is no value in the columns. Only periods and commas appear.

sqlite> select * from people2012 limit 10; "??c";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"" ; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; " ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" "; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; "" ; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; " ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" ";" "; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; ""; "" ; ""; ""; ""; ""

(2) By Keith Medcalf (kmedcalf) on 2020-10-08 03:28:10 in reply to 1 [source]

For the first command the first row of the file contains duplicate column names.

For the second set of commands you forgot to set .mode csv

If the first row contains unique column names then you can import the file with:

.mode csv  
.separator ;  
.import file table  

If the first row of your SSV file does not contain unique column names then you must define the table to import the data into first then do the same commands:

create table t (...);  
.mode csv  
.separator ;  
.import file t

Are you sure you are using a version of SQLite that supports using different separator characters?

(3) By Warren Young (wyoung) on 2020-10-08 03:40:41 in reply to 1 [link] [source]

.separator ";"

You don't need the double quotes here. This works:

  .separator ;

.import c: /users/inspiron/desktop/people2012.csv

Why are you putting a space between the "c:" bit and the rest of the path? That's two paths, the first meaning "the current working directory on the C drive".

I assume this isn't a literal copy-and-paste from a SQLite command session, else later parts of your explanation wouldn't be working, which then leads me to ask, why are you posting commands here that differ from what you're actually typing? Why make us second-guess your description in order to make any sense of it?

duplicate column name:

It means the first row of your CSV file isn't a header, listing the names of the columns. You can pre-define the schema, as you discovered, to get around this.

Unless you depend on the column affinities being set up in a certain way, it's simpler to just add a header row to the input file. Several other programs expecting CSV/TSV style input also want that header.

Only periods and commas appear.

...and then you go on to show semicolons and double-quotes instead. Once again, one thing is happening on your local computer, but you then go to show us something different.

I expect this is just a reflection of your earlier .mode selection, telling it to use semicolons as separators, and that the difference is due to restarting sqlite3, causing the separator and other modes to be reset to their defaults.

That is, .separator also affects SQLite's output, not just its .csv input. Be enlightened:

  .separator ' ooga booga '
  select * from people2012 limit 10;

As for the rest, SQLite is telling you the problem: the separator isn't matching somehow. Can you post a few rows of the data table somewhere?