How to import load a sql or csv file into SQLite

Question

I need to dump a  sql or  csv file into SQLite  I m using SQLite3 API   I ve only found documentation for importing loading tables  not entire databases  Right now  when I type   sqlite3prompt gt   import FILENAME TABLE    I get a syntax error  since it s expecting a table and not an entire DB

User · Answer

This is how you can insert into an identity column:

CREATE TABLE my_table (id INTEGER PRIMARY KEY AUTOINCREMENT, name COLLATE NOCASE);
CREATE TABLE temp_table (name COLLATE NOCASE);

.import predefined/myfile.txt temp_table 
insert into my_table (name) select name from temp_table;

myfile.txt is a file in C:\code\db\predefined\

data.db is in C:\code\db\

myfile.txt contains strings separated by newline character.

If you want to add more columns, it's easier to separate them using the pipe character, which is the default.

User · Answer

Remember that the default delimiter for SQLite is the pipe      sqlite gt   separator      sqlite gt   import path filename txt tablename    http   sqlite awardspace info syntax sqlitepg01 htm sqlite010

User · Answer

SQLite is extremely flexible as it also allows the SQLite specific dot commands in the SQL syntax   although they are interpreted by CLI   This means that you can do things like this   Create a sms table like this     sqlite3 mycool db   schema sms  CREATE TABLE sms   id integer primary key autoincrement  Address VARCHAR  Display VARCHAR  Class VARCHAR  ServiceCtr VARCHAR  Message VARCHAR  Timestamp TIMESTAMP NOT NULL DEFAULT current timestamp     Then two files      echo  1 ADREZZ DizzPlay CLAZZ SMSC DaTestMessage 2015-01-24 21 00 00  gt test csv    cat test sql  mode csv  header on  import test csv sms   To test the import of the CSV file using the SQL file  run     sqlite3 -csv -header mycool db   read test sql    In conclusion  this means that you can use the  import statement in SQLite SQL  just as you can do in any other RDB  like MySQL with LOAD DATA INFILE etc  However  this is not recommended

User · Answer

Try doing it from the command like   cat dump sql   sqlite3 database db   This will obviously only work with SQL statements in dump sql  I m not sure how to import a CSV

User · Answer

Import your csv or sql to sqlite with phpLiteAdmin  it is excellent

User · Answer

if you are using it in windows  be sure to add the path to the db in    and also to use double slash   in the path to make sure windows understands it

User · Answer

To go from SCRATCH with SQLite DB to importing the CSV into a table    Get SQLite from the website  At a command prompt run sqlite3  lt your db file name gt   It will be created as an empty file  Make a new table in your new database  The table must match your CSV fields for import  You do this by the SQL command  CREATE TABLE  lt table Name gt    lt field name1 gt   lt Type gt    lt field name2 gt   lt type gt      Once you have the table created and the columns match your data from the file then you can do the above      mode csv  lt table name gt   import  lt filename gt   lt table name gt

User · Answer

The sqlite3  import command won t work for ordinary csv data because it treats any comma as a delimiter even in a quoted string    This includes trying to re-import a csv file that was created by the shell   Create table T  F1 integer  F2 varchar   Insert into T values  1   Hey     Insert into T values  2   Hey  You       mode csv  output test csv select   from T   Contents of test csv  1 Hey  2  Hey  You    delete from T    import test csv T Error  test csv line 2  expected 2 columns of data but found 3   It seems we must transform the csv into a list of Insert statements  or perhaps a different delimiter will work   Over at SuperUser I saw a suggestion to use LogParser to deal with csv files  I m going to look into that

User · Answer

If you are happy to use a  python  script then there is a python script that automates this at  https   github com rgrp csv2sqlite  This will auto-create the table for you as well as do some basic type-guessing and data casting for you  so e g  it will work out something is a number and set the column type to  real

User · Answer

Check out termsql  https   gitorious org termsql https   gitorious org termsql pages Home  It converts text to SQL on the command line   CSV is just text   Example   cat textfile   termsql -o sqlite db   By default the delimiter is whitespace  so to make it work with CSV that is using commata  you d do it like this   cat textfile   termsql -d     -o sqlite db   alternatively you can do this   termsql -i textfile -d     -o sqlite db   By default it will generate column names  COL0    COL1   if you want it to use the first row for the columns names you do this   termsql -i textfile -d     -1 -o sqlite db   If you want to set custom column names you do this   termsql -i textfile -d     -c  id name age color  -o sqlite db

User · Answer

To import from an SQL file use the following   sqlite gt   read  lt filename gt    To import from a CSV file you will need to specify the file type and destination table   sqlite gt   mode csv  lt table gt  sqlite gt   import  lt filename gt   lt table gt

[database] How to import load a .sql or .csv file into SQLite?

Examples related to database

Examples related to sqlite

Examples related to import

Examples related to sqlite-shell