How to import load a .sql or .csv file into SQLite?
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> .import FILENAME TABLE 11 Answers 11
To import from an SQL file use the following:
To import from a CSV file you will need to specify the file type and destination table:
sqlite> .mode csv sqlite> .import
@krishna222, per the documentation, if the table does not exist, the first line of the CSV will be used as column names; if the table does exist, all rows are treated as data.
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.
I think this would work just the same, but the user would have to make sure that the sqlite3 settings were set up for .mode csv
Just want to make a comment here, this is actually 100% dead on, sqlite3 database.db < dump.sql is SOOOO SLOW. So use cat dump.sql | sqlite3 database.db instead! :D
@JavierBuzzi: Sorry but that makes no sense. Those two methods are equivalent. You must have had something else going on when you tried one method or the other. Under stable testing conditions I guarantee there will be no speed difference.
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 *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 ( , );
Once you have the table created and the columns match your data from the file then you can do the above.
@jacob just fyi, this answer is almost 4 years old and the person who posted it hasn’t been here in over three years.
You saved my weeks. I finished my work in 3 second. I converted 120MB CSV file to .db in mere 5 seconds.
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.
SQLite Import CSV
Files and folders are the necessary parts of any operating system. But, when you need to work on databases, the CSV files come in handy. The CSV files are also another name for Microsoft Excel documents used to store data in the form of rows and columns. The Excel or CSV files can be used to import data within the tables of a database. Therefore, we have decided to cover the method of importing a CSV file containing data in columns to the SQLite database table. Make sure to have SQLite C-library of SQL installed in your Ubuntu 20.04.
Let’s get started with today’s article by launching the console application within Ubuntu 20.04 with the Ctrl+Alt+T shortcut. After the successful launch of the console application, you need to update your Linux system with the help of an apt package preceded by the “sudo” keyword. It will ask for your currently working user account password to resume the updating process through this query. We have added the password, pressed the Enter key, and continued.
After a while, your system will be up to date. After this, you need to upgrade your system and its packages, i.e., modules and libraries. Try out the same instruction with the keyword “upgrade” instead of the word “update”.
Make sure to have some CSV files within your Linux’s home folder to use within the SQLite database, i.e., import its data to a table. Thus, we have created a new CSV file named “data.csv” within the home folder to be used in the table by importing. Let’s start the SQLite database on the shell terminal of the Ubuntu 20.04 system using the keyword “sqlite3”. The query area of an SQLite will be launched as shown below:
We have been starting this guide using the “.tables” instruction in the database to display the list of tables. Right now, we don’t have any table in this database.
To import CSV files, we need a table in the database. Thus, we have to create a table with the same column names and types as we have in the CSV file. So, we have been creating a new table named DATA with the CREATE TABLE instruction containing two columns, i.e., “Country” of TEXT type and column “People” of integer type. This newly created table has no records yet.
CREATE TABLE DATA ( «Country» TEXT , «People» INT ) ;
Let’s import the CSV file named “data.csv” within the DATA table using the “.import” instruction followed by the path to the file and the table’s name, as presented below:
After the successful query execution, we should confirm whether the import was proper and successful. For this, you need to try out the SELECT instruction for the table DATA once again on your SQLite shell. The output of this command shows that the import is successful. All the records from the data.csv file have been imported into the DATA table properly.
Let’s try out the condition within the SELECT query. We have added the WHERE clause to display all the records from the imported data of a table “DATA”, where the column “People” contains the values less than or equal to 550000. It returned a total of eight records.
Let’s try a SELECT query with the WHERE clause in the SQLite database to display all the records of a table “DATA” where the column “People” have a value between 550000 and 750000. For this, we have been using the BETWEEN keyword and have only three results.
Let’s look at another example of importing the same file with a different set of records. This time, the data set contains 15 rows with the three columns, i.e., Name, Age, and Salary. The column Name and Salary are Integer types, while the column “Name” is text type.
For this data, we have been creating a new table named “Detail” containing the same name in three columns as the data.csv file, i.e., Name, Age, and Salary. Before importing the data.csv file to the Detail table, we haven’t found any record within it per the SELECT instruction executed at the SQLite shell screen, i.e., empty table.
CREATE TABLE Detail ( Name TEXT , Age INT , Salary INT ) ;
To import the data of the data.csv file within the Detail table, we need to try out the “.import” instruction followed by the path to the “data.csv” file, i.e., /home/Linux/, and the name of a table “Detail” at the end of this query. This query will return nothing showing that the import has been successful. So, we will be using the SELECT instruction once again to confirm that the import was made perfectly and efficiently. The SELECT instruction with the “*” character followed by the name of a table “Detail” displayed all the records of the data.csv file just imported to the table. This means the import was successful.
Let’s apply the WHERE clause on the imported CSV column “Salary” of the table Detail to display only the records where the value of the Salary column is less than 35000. This query with the WHERE condition returns two records as a result.
Let’s use the WHERE clause within the SELECT query to display the records of a table “Detail” where the imported data has values between 35 and 47 in the “Age” column. This time, we have five records, as presented below.
Conclusion
This article used CSV files to import data records to the SQLite database table using the “import” instruction at the shell. You need to ensure that the CSV column names and the table column names must be the same, i.e., uppercase or lowercase. We also discussed the ways to display the imported data in tables using some conditions.
About the author
Omar Farooq
Hello Readers, I am Omar and I have been writing technical articles from last decade. You can check out my writing pieces.
How to insert CSV data into an SQLite table via a shell pipe?
I have written a program that outputs the result to the standard output in strict pure CSV form (every line represents a single record and contain the same set of comma-separated fields, fields only contain lowercase English letters, numbers and dots, no spaces, no quotes and no symbols that might need to be escaped/encoded). How do I redirect this output into an SQLite table that it fits into perfectly? It would be great if I could control whether I want constraint-breaking (e.g. having the same primary/secondary key as the records already in the table) replace existing records or be discarded silently. Of course I might build direct SQLite database output support in the program itself but I would prefer going the unix-way if possible.
4 Answers 4
I found an even simpler solution that still uses sqlite3 .import , but that doesn’t read /dev/stdin or a temporary named pipe. Instead, it uses .import with the pipe operator to invoke cat — to read directly from standard input.
#!/bin/bash function csv_to_sqlite() < local database_file_name="$1" local table_name="$2" sqlite3 -csv $database_file_name ".import '|cat -' $table_name" >database_file_name=$1 table_name=$2 csv_to_sqlite "$database_file_name" "$table_name" Note that this wouldn’t work in a Windows command shell.
GroupName,Groupcode,GroupOwner,GroupCategoryID System Administrators,sysadmin,13456,100 Independence High Teachers,HS Teachers,,101 John Glenn Middle Teachers,MS Teachers,13458,102 Liberty Elementary Teachers,Elem Teachers,13559,103 1st Grade Teachers,1stgrade,,104 2nd Grade Teachers,2nsgrade,13561,105 3rd Grade Teachers,3rdgrade,13562,106 Guidance Department,guidance,,107 1) With csvkit (a suite of command-line tools for converting to and working with CSV)
Import into sqlite3 database:
csvsql --db sqlite:///test_db --tables test_tbl --insert test.csv If no input csv file was specified it’ll accept csv data from stdin:
. | csvsql --db sqlite:///test_db --tables test_tbl --insert To extract data from sqlite database:
sql2csv --db sqlite:///test_db --query 'select * from test_tbl limit 3' GroupName,Groupcode,GroupOwner,GroupCategoryID System Administrators,sysadmin,13456,100 Independence High Teachers,HS Teachers,,101 John Glenn Middle Teachers,MS Teachers,13458,102 2) With sqlite3 command-line tool (allows the user to manually enter and execute SQL statements against an SQLite database)
Use the » .import » command to import CSV (comma separated value) data into an SQLite table. The » .import » command takes two arguments which are the name of the disk file from which CSV data is to be read and the name of the SQLite table into which the CSV data is to be inserted.
Note that it is important to set the «mode» to «csv» before running the » .import » command. This is necessary to prevent the command-line shell from trying to interpret the input file text as some other format.
$ sqlite3 sqlite> .mode csv sqlite> .import test.csv test_tbl sqlite> select GroupName,Groupcode from test_tbl limit 5; "System Administrators",sysadmin "Independence High Teachers","HS Teachers" "John Glenn Middle Teachers","MS Teachers" "Liberty Elementary Teachers","Elem Teachers" "1st Grade Teachers",1stgrade sqlite>