Import CSV to SQLite

Asked 19/2, 2013 at 0:41 Answered 20/3, 2023 at 13:3

168

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.

Bartie answered 19/2, 2013 at 0:41 Comment(9)

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 – Subternatural 19/2, 2013 at 0:52

Can I specify a line-terminator or is there one missing from my csv? – Bartie 19/2, 2013 at 1:1

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. – Subternatural 19/2, 2013 at 1:6

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... – Bartie 19/2, 2013 at 1: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! – Bartie 19/2, 2013 at 1:34

Use SQLite Expert Professional 3 – Mastersinger 31/10, 2014 at 8:56

Possible duplicate of How can I import load a .sql or .csv file into sqlite? – Dosh 22/11, 2015 at 21:9

This website can convert a CSV (or Excel) file to SQLite - converttosqlite.com – Nadeau 27/12, 2015 at 20:6

You forgot the period before separator; it should be .separator. – Anzus 25/2, 2017 at 6:25

210

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

Logo answered 5/7, 2014 at 0:46 Comment(8)

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. – Bibliopole 3/11, 2015 at 20:56

@EarlCrapstone: could you elaborate? It doesn't seem to work for me. – Unpack 14/1, 2016 at 7:58

@Unpack Look at these examples. 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. – Bibliopole 14/1, 2016 at 13:16

Why are you setting .mode? Isn't that only for output? – Hiatus 3/6, 2016 at 17:51

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. – Shayneshays 15/10, 2016 at 18:5

For me running .mode csv meant my import failed, and the database file wasn't created, so I ran only the last line – Brickbat 17/2, 2017 at 16:15

@Bibliopole 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. – Die 26/10, 2017 at 9:33

you can skip .mode csv if you append ` --csv` to the end of the last statement like so: .import test.csv foo --csv – Supat 2/4, 2022 at 18:5

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.

In the SQLite shell enter:

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

Appoggiatura answered 4/5, 2017 at 14:52 Comment(6)

How to handle unescaped " character alerts? – Gunshot 8/8, 2017 at 19:52

If you create a table before importing, then it will not look for headers. – Utilize 28/2, 2018 at 17:13

I have to put file in same folder where sqlite3.exe is located. Can i use another path for csv file ? – Lillianalillie 11/6, 2019 at 4:3

Minor point: "If you haven't got Sqlite installed on your Mac" ... AFAIK it is installed by default. – Amanuensis 12/2, 2020 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. – Comptometer 21/11, 2021 at 17:56

Tried brew install sqlite3 FormulaUnavailableError: No available formula with the name "/usr/local/opt/python@2/.brew/[email protected]". – Boniface 6/5, 2022 at 18:35

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

Acedia answered 3/4, 2015 at 18:49 Comment(2)

for multiple rows, my tsv needed to specify the ROW separator with this command .separator "\t" "\r" – Antilepton 13/5, 2016 at 21:3

When discussing file formats, tsv != csv See tools.ietf.org/html/rfc4180 – Hiatus 2/6, 2016 at 17:7

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

Westbrook answered 28/5, 2019 at 4:40 Comment(0)

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.

Verified answered 11/3, 2015 at 6:59 Comment(2)

.mode is for output only – Dash 15/12, 2015 at 12:41

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. – Inflation 19/9, 2016 at 14:18

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.

Recor answered 12/1, 2014 at 13:51 Comment(2)

It's easier to use tr -s '\r' '\n' and that will work with all sorts of random files. – Ibidem 12/1, 2014 at 18:5

sed, tr, and other unix text processing tools are perfect for this job. They already exist on OSX, Linux, BSD, Plan9, and WSL. Not everyone is going to have Sublime or OSX and should be able to use the tools provided by the OS. – Borrero 10/10, 2023 at 3:32

With termsql you can do it in one line:

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

From Termsql

Convert text from a file or from stdin into SQL table and query it instantly. Uses sqlite as backend. The idea is to make SQL into a tool on the command line or in scripts.

Termsql requires python and sqlite3

Okapi answered 23/11, 2014 at 19:52 Comment(2)

link was broken, changed it to the github repo - please clarify! – Parted 17/11, 2015 at 15:47

please explain why readers should use yet another two tools as the question is originally about sqlite itself and that sqlite can parse standard SQL directly from STDIN (e.g a unix pipe such as echo .import mydata.csv foo --csv | sqlite3 mynewdatabase.db). – Borrero 10/10, 2023 at 3:30

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.

Malapropism answered 18/8, 2021 at 13:51 Comment(0)

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

Gregor answered 22/4, 2020 at 20:2 Comment(1)

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 😅 – Ejecta 7/5, 2020 at 0:24

Follow the steps:-

1] sqlite3 name

2] .mode csv tablename

3] .import Filename.csv tablename

Sophistic answered 22/4, 2020 at 11:34 Comment(0)

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.

Tergal answered 20/3, 2023 at 13:3 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags