CODEWITHSUNDEEP
sqlite
Molly Walters
Molly Walters

Reputation: 1771

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.

Upvotes: 174

Views: 269021

Answers (11)

user3573558
user3573558

Reputation: 190

With 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 and

Upvotes: 3

lukas_o
lukas_o

Reputation: 4236

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

Upvotes: 48

surfmuggle
surfmuggle

Reputation: 5944

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

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

    aaa,bbb,ccc CRLF

    zzz,yyy,xxx CRLF

  2. 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.

Upvotes: 0

zeo
zeo

Reputation: 101

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.

Upvotes: 9

NumesSanguis
NumesSanguis

Reputation: 6282

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

Upvotes: 221

Dupinder Singh
Dupinder Singh

Reputation: 7769

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

Upvotes: 1

Lalit Bangad
Lalit Bangad

Reputation: 186

Follow the steps:-

1] sqlite3 name

2] .mode csv tablename

3] .import Filename.csv tablename

Upvotes: 0

Luis Alejandro Quiroga Gomez
Luis Alejandro Quiroga Gomez

Reputation: 66

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.

Upvotes: 2

sivamani
sivamani

Reputation: 535

How to import csv file to sqlite3

  1. Create database

    sqlite3 NYC.db

  2. Set the mode & tablename

    .mode csv tripdata

  3. Import the csv file data to sqlite3 

    .import yellow_tripdata_2017-01.csv tripdata

  4. Find tables

    .tables

  5. Find your table schema

    .schema tripdata

  6. Find table data

    select * from tripdata limit 10;

  7. Count the number of rows in the table

    select count (*) from tripdata;

Upvotes: 21

gyaani_guy
gyaani_guy

Reputation: 3209

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

Upvotes: 34

adass
adass

Reputation: 345

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.

Upvotes: 3

Related Questions