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 gt  create table foo a  b   sqlite gt   separator   sqlite gt   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

User · Answer

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 gt   separator   t  ---IMPORTANT  should be in double quotes  sqlite gt   import afile csv tablename-to-import-to

User · Answer

With Termsql you can do it in one line   termsql -i mycsvfile CSV -d     -c  a b  -t  foo  -o mynewdatabase db

User · Answer

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 gt    mode csv sqlite gt    import test csv yourtable sqlite gt    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

User · Answer

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 gt  create table foo a  b   sqlite gt   mode csv sqlite gt   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

User · Answer

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

User · Answer

before  import command  type   mode csv

User · Answer

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 gt   separator   no need of this line   sqlite gt  create table cities name  population   sqlite gt   mode csv sqlite gt   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

User · Answer

Follow the steps -   1  sqlite3 name  2   mode csv tablename 3   import Filename csv tablename

User · Answer

How to import csv file to sqlite3   Create database  sqlite3 NYC db  Set the mode  amp  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

[sqlite] Import CSV to SQLite

Examples related to sqlite