Importing csv data file in SQLite3

cookyjar - Importing csv data file in SQLite3

Importing csv data file in SQLite3

Monday, April 20, 2009 9:14:29 PM

sqlite3, csv, import, sqlite

Here I will tell an easy way to import a CSV data file as database in SQLite3.

For the sake of argument suppose you have a comma separated database file called "data.csv". Further suppose you want your SQLite3 database to be called "data.db". Before you begin there is a little quirk that you must take care of. SQLite3 csv import works when there are no double quotes character in the input file.

You can easily fix that using this simple UNIX command:

> cat data.csv | tr -d \" > out.csv

Once you have performed this cleanup step, we can proceed. We will assume you know the database schema that your csv file represents. Again for illustration, say your CSV file contains data in the format: Name,address,salary. And hence your corresponding SQL Table will have three columns: Name of type STRING, address of type STRING and salary of type INTEGER. These may vary depending on how you want to process your query results.

These are the steps that would create a SQLite3 compatible database from your original csv data file:

> sqlite3 data.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> CREATE TABLE data (name STRING, address STRING, salary INTEGER);
sqlite> .mode csv
sqlite> .import out.csv data
sqlite> .quit
>

Now you can see that there is a new database file created called "data.db". This is SQLite3 compatible database that you can now process and query from within several scripting languages including PHP. Note that I have used the cleaned up version of the csv file instead of the original csv file.

This database will have the table you created with table-name "data" and column names "name", "address", "salary". You can issue standard SQL queries against this database in SQLite3.

You can browse and query your newly created database by using this handy TCL/TK tool called TkSQLite. You can get it for free at: TkSQLite site. Enjoy!