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.

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

Answer 2

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

Answer 3

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.

Answer 4

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;
   

Answer 5

before .import command, type ".mode csv"

Answer 6

With Termsql you can do it in one line:

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

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

Answer 8

Follow the steps:-

1. 1] sqlite3 name 2] .mode csv tablename 3] .import Filename.csv tablename

Answer 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, becayse 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