Import CSV to SQLite

Question

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.

It appears that the command expects column headers in the first line, and that the line-terminator is not being recognized as such. `6 - 2 = 4` — mechanical_meat, Feb 19 '13 at 00:52
Can I specify a line-terminator or is there one missing from my csv? — Molly Walters, Feb 19 '13 at 01:01
It doesn't look like you can specify the line-terminator from the SQLite command-line tool. Do you have a text-editor (like Notepad on Windows, but better) that will show you the line-terminating characters? There are three main variations: `\r\n` on Windows, `\n` on *nix (includes newer Macs), `\r` on older Macs. — mechanical_meat, Feb 19 '13 at 01:06
I'm using BBEdit, but I don't see any of those characters at the end of the lines. I tried adding them manually, but it doesn't seem to do anything... — Molly Walters, Feb 19 '13 at 01:17
I don't know what was wrong with what I was doing, but I just imported everything successfully using the SQLite Manager extension for Firefox. Thanks for your help! — Molly Walters, Feb 19 '13 at 01:34
Possible duplicate of [How can I import load a .sql or .csv file into sqlite?](http://stackoverflow.com/questions/1045910/how-can-i-import-load-a-sql-or-csv-file-into-sqlite) — Ciro Santilli OurBigBook.com, Nov 22 '15 at 21:09
This website can convert a CSV (or Excel) file to SQLite - http://converttosqlite.com/ — Code Slinger, Dec 27 '15 at 20:06
You forgot the period before `separator`; it should be `.separator`. — Zenexer, Feb 25 '17 at 06:25

score 201 · Answer 1 · edited Apr 08 '22 at 12:39

201

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

edited Apr 08 '22 at 12:39

aero

1,654
1
21
31

answered Jul 05 '14 at 00:46

NumesSanguis

5,832
6
41
76

107

For other people who land here from a search, if the first line of your csv file contains the column names, then you can omit the first `create table` command and sqlite will use the column names from the csv file. – EarlCrapstone Nov 03 '15 at 20:56
3

@EarlCrapstone: could you elaborate? It doesn't seem to work for me. – d33tah Jan 14 '16 at 07:58
6

@d33tah Look at these [examples](https://en.wikipedia.org/wiki/Comma-separated_values#Example). Notice how the first line contains the column names `Year,Make,Model,Description,Price` and not actual data. If that's the case with your CSV file, then you do not need to manually create the table using the `create table` command. The `.import` command will use the first line in your file to determine the column names and create the table accordingly. You still have to include the _name_ of the table in the command. The same info [from the SQLite docs](https://www.sqlite.org/cli.html#csv). – EarlCrapstone Jan 14 '16 at 13:16
1

Why are you setting `.mode`? Isn't that only for output? – Alan Jun 03 '16 at 17:51
1

If you find that the generated table groups multiple columns together, check that you aren't using a reserved word such as `type` for a CSV column name. – paulvs Oct 15 '16 at 18:05
For me running `.mode csv` meant my import failed, and the database file wasn't created, so I ran only the last line – Jonathon Blok Feb 17 '17 at 16:15
@EarlCrapstone There is a problem with doing that which is that the column types will all be TEXT regardless of the type of the input data. – C. E. Oct 26 '17 at 09:33
1

you can skip `.mode csv` if you append ` --csv` to the end of the last statement like so: `.import test.csv foo --csv` – aero Apr 02 '22 at 18:05

score 45 · Answer 2 · edited Oct 02 '17 at 11:58

45

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.

edited Oct 02 '17 at 11:58

charlesreid1

4,360
4
30
52

answered May 04 '17 at 14:52

lukas_o

3,776
4
34
50

2

How to handle `unescaped " character` alerts? – TMOTTM Aug 08 '17 at 19:52
1

If you create a table before importing, then it will not look for headers. – Rolf Feb 28 '18 at 17:13
I have to put file in same folder where sqlite3.exe is located. Can i use another path for csv file ? – Jaydeep Karena Jun 11 '19 at 04:03
Minor point: "If you haven't got Sqlite installed on your Mac" ... AFAIK it is installed by default. – András Aszódi Feb 12 '20 at 17:10
All columns are text columns. If quantitative data perhaps less work to type in the table definition beforehand, although the open source GUI tool "DB Browser for SQLite" can alter column types on the fly. – Lori Nov 21 '21 at 17:56
Tried brew install sqlite3 FormulaUnavailableError: No available formula with the name "/usr/local/opt/python@2/.brew/python@2.rb". – tricknology May 06 '22 at 18:35

score 34 · Answer 3 · answered Apr 03 '15 at 18:49

34

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

answered Apr 03 '15 at 18:49

gyaani_guy

3,191
8
43
51

for multiple rows, my tsv needed to specify the ROW separator with this command `.separator "\t" "\r"` – mfink May 13 '16 at 21:03
3

When discussing file formats, tsv != csv See https://tools.ietf.org/html/rfc4180 – Alan Jun 02 '16 at 17:07

score 20 · Answer 4 · edited Nov 27 '19 at 13:09

20

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;
```

edited Nov 27 '19 at 13:09

JaviMerino

619
10
18

answered May 28 '19 at 04:40

sivamani

465
7
10

score 9 · Answer 5 · edited Feb 22 '23 at 19:16

9

before .import command, type ".mode csv"

Quote from Importing files as CSV or other formats

Note that it may be important to set the "mode" before running the ".import" command. This is prudent to prevent the command-line shell from trying to interpret the input file text as some format other than how the file is structured. If the --csv or --ascii options are used, they control import input delimiters.

And

To import data with arbitrary delimiters and no quoting, first set ascii mode (".mode ascii"), then set the field and record delimiters using the ".separators" command. This will suppress dequoting. Upon ".import", the data will be split into fields and records according to the delimiters so specified.

edited Feb 22 '23 at 19:16

surfmuggle

5,527
7
48
77

answered Mar 11 '15 at 06:59

zeo

101
1
2

5

.mode is for output only – DeliriumTremens Dec 15 '15 at 12:41
14

Apparently not so. From the .import doc section: 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. – Richard - Rogue Wave Limited Sep 19 '16 at 14:18

score 4 · Answer 6 · edited Jan 13 '20 at 17:54

4

With Termsql you can do it in one line:

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

edited Jan 13 '20 at 17:54

Derek Mahar

27,608
43
124
174

answered Nov 23 '14 at 19:52

user3573558

192
1
1

1

link was broken, changed it to the github repo - please clarify! – Nov 17 '15 at 15:47

score 3 · Answer 7 · answered Jan 12 '14 at 13:51

3

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.

answered Jan 12 '14 at 13:51

adass

333
2
14

2

It's easier to use `tr -s '\r' '\n'` and that will work with all sorts of random files. – Donal Fellows Jan 12 '14 at 18:05

score 1 · Answer 8 · edited Dec 27 '21 at 08:51

1

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

edited Dec 27 '21 at 08:51

marc_s

732,580
175
1,330
1,459

answered Apr 22 '20 at 20:02

Dupinder Singh

7,175
6
37
61

2

Don't forget to run `.save someFileName` in the end. Took me hour to find this to finally export this in memory database file to disk – ioopl May 07 '20 at 00:24

score 1 · Answer 9 · answered Aug 18 '21 at 13:51

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.

Lalit Bangad · Answer 10 · 2021-09-26T22:49:56.937

0

Follow the steps:-

1] sqlite3 name

2] .mode csv tablename

3] .import Filename.csv tablename

edited Sep 26 '21 at 22:49

answered Apr 22 '20 at 11:34

Lalit Bangad

186
1
10

score 0 · Answer 11 · answered Mar 20 '23 at 13:03

In my case i had a similar problem the lines in the csv file did contain a \n instead of an actual a CRLF (line break).

     Alias, Name, Agency\n
     007  , James Bond, MI5\n
     Q    , Mister Q, MI6\n
     Moneypenny, Miss Moneypenny, MI5\n

From Common Format and MIME Type for Comma-Separated Values (CSV) Files

Each record is located on a separate line, delimited by a line break (CRLF). For example:

aaa,bbb,ccc CRLF

zzz,yyy,xxx CRLF

The last record in the file may or may not have an ending line break. For example:

aaa,bbb,ccc CRLF

zzz,yyy,xxx

After i replaced the line separators \n with a line break (using SHIFT+ENTER in vs code) the import under windows was possible using

c:\Apps\SQLite\sqlite_3.40.1>sqlite3.exe
                SQLite version 3.40.1 2022-12-28 14:03:47
                Enter ".help" for usage hints.
                Connected to a transient in-memory database.
                Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open C:/csv/myDataCRLF.db
sqlite> .import --csv C:/csv/source_file_crlf.csv myTable
sqlite> .save C:/csv/myDataCRLF.db
                Error: database is locked
sqlite> .save C:/csv/myDataCRLF_2.db

The database file myDataCRLF_2.db has now a table myTable with the records from the csv-file.

Import CSV to SQLite

11 Answers11

Linked