'Import CSV to SQLite

I'm trying to import a csv file to an SQLite table.

Example csv:

1,2
5,6
2,7

Example command:

sqlite> create table foo(a, b);
sqlite> .separator ,
sqlite> .import test.csv foo
Error: test.csv line 1: expected 2 columns of data but found 4

I'm not even sure why it would find four columns with six pieces of data and two columns.

sqlite

Solution 1:^[1]

What also is being said in the comments, SQLite sees your input as 1, 25, 62, 7. I also had a problem with , and in my case it was solved by changing "separator ," into ".mode csv". So you could try:

sqlite> create table foo(a, b);
sqlite> .mode csv
sqlite> .import test.csv foo

The first command creates the column names for the table. However, if you want the column names inherited from the csv file, you might just ignore the first line.

--- New Versions of sqlite3 ---

The latest version of sqlite3 creates the table and columns for you if you let it.

You can also skip the line ".mode csv" if you append "--csv" to the end of the import statement like so:

sqlite> .import test.csv foo --csv

Solution 2:^[2]

I am merging info from previous answers here with my own experience. The easiest is to add the comma-separated table headers directly to your csv file, followed by a new line, and then all your csv data.

If you are never doing sqlite stuff again (like me), this might save you a web search or two:

In the Sqlite shell enter:

$ sqlite3 yourfile.sqlite
sqlite>  .mode csv
sqlite>  .import test.csv yourtable
sqlite>  .exit

If you haven't got Sqlite installed on your Mac, run

$ brew install sqlite3

You may need to do one web search for how to install Homebrew.

Solution 3:^[3]

Here's how I did it.

Make/Convert csv file to be seperated by tabs (\t) AND not enclosed by any quotes (sqlite interprets quotes literally - says old docs)
Enter the sqlite shell of the db to which the data needs to be added

sqlite> .separator "\t" ---IMPORTANT! should be in double quotes sqlite> .import afile.csv tablename-to-import-to

Solution 4:^[4]

How to import csv file to sqlite3

Create database
```
sqlite3 NYC.db
```
Set the mode & tablename
```
.mode csv tripdata
```

Import the csv file data to sqlite3

.import yellow_tripdata_2017-01.csv tripdata

Find tables
```
.tables
```
Find your table schema
```
.schema tripdata
```
Find table data
```
select * from tripdata limit 10;
```
Count the number of rows in the table
```
select count (*) from tripdata;
```

Solution 5:^[5]

before .import command, type ".mode csv"

Solution 6:^[6]

With Termsql you can do it in one line:

termsql -i mycsvfile.CSV -d ',' -c 'a,b' -t 'foo' -o mynewdatabase.db

Solution 7:^[7]

I had exactly same problem (on OS X Maverics 10.9.1 with SQLite3 3.7.13, but I don't think SQLite is related to the cause). I tried to import csv data saved from MS Excel 2011, which btw. uses ';' as columns separator. I found out that csv file from Excel still uses newline character from Mac OS 9 times, changing it to unix newline solved the problem. AFAIR BBEdit has a command for this, as well as Sublime Text 2.

Solution 8:^[8]

In my case I had to see the file I was trying to add, it had headers and the separator was a semicolon ;.

I tried first:

.mode csv
.import myfile.csv mytable

But it didn't work, so I tried:

.separator ";"
.import myfile.csv mytable

And it did work, so I had to set the separator manually.

Solution 9:^[9]

As some websites and other article specifies, its simple have a look to this one. https://www.sqlitetutorial.net/sqlite-import-csv/

We don't need to specify the separator for csv file, because csv means comma separated.

sqlite> .separator , no need of this line.

sqlite> create table cities(name, population);
sqlite> .mode csv
sqlite> .import c:/sqlite/city_no_header.csv cities

This will work flawlessly :)

PS: My cities.csv with header.


name,population
Abilene,115930
Akron,217074
Albany,93994
Albuquerque,448607
Alexandria,128283
Allentown,106632
Amarillo,173627
Anaheim,328014

Solution 10:^[10]

Follow the steps:-

1] sqlite3 name

2] .mode csv tablename

3] .import Filename.csv tablename

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	aero
Solution 2	charlesreid1
Solution 3	gyaani_guy
Solution 4	JaviMerino
Solution 5	zeo
Solution 6	Derek Mahar
Solution 7	adass
Solution 8	Luis Alejandro Quiroga Gomez
Solution 9	marc_s
Solution 10

'Import CSV to SQLite

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]

Solution 10:[10]