Import CSV to SQLite

Question

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.

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.

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

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;
   

before .import command, type ".mode csv"