Feb 3, 2005
Importing CSV files to SQLite
Whilst experimenting with the new features in PHP5, I recalled that one of my current projects had a very weak Class file that was using a CSV file to look up some data – which struck me that it would benefit greatly from being switched to use the in-built SQLite support in PHP5. In fact, backing up my theory, the SQLite site has a page entitled Situations Where SQLite Works Well
, and enthused that “Raw data can be imported from CSV files”, and of topped off the excitement with the claim that “SQLite is different from most other SQL database engines in that its primary design goal is to be simple”.
And I always like simple. But of course, it’s anything but.
The problem soon arises that there’s er, a bit of a lack of documentation how to actually get a CSV file into a SQLite database. So, cutting a long story short, here’s how I did it:
- Make sure that the source file is stripped of quotation marks. I found the quickest way to do this on a larger file was to use the tiny freeware DOS utility Binsub with the following syntax:
binsub my.csv /034
… where 034 is the decimal ASCII code for a double quote. - Use the utility dgSQLite to create a new database, and subsequently import your CSV into a table of your choice. This utility does most of the grunt work for you, and I think requires TCL/Tk for Windows to run. I had previously tried and failed to get things moving with the slightly more user-friendly SQLite Browser, however that only worked with SQLite 2.x files. (And somehow along the way I decided I was using 3.x.)
Lastly, to see if it worked, you can try the following snippet in your PHP5 installation:
$db = new SQLiteDatabase("path/to/your/database.sqlite");
$result = $db->query('SELECT * FROM your_table_name ');
while ($result->valid()) {
$result = $result->current();
print_r($result);
$result->next();
}
You can also do this from the sqlite command line tool (sqlite3 in my case). I ran the following commands:
sqlite> .mode csv
sqlite> .import ./test.csv test
I created a really simple table to test with.
sqlite> .schema test
CREATE TABLE test (name varchar(255) not null, blah varchar(255) not null);
And my CSV file:
andrew,nklnsdf
blah,kls
joey,aknlkn
jim,asdf
After the import:
sqlite> select * from test;
andrew|nklnsdf
blah|kls
joey|aknlkn
jim|asdf
Sorry, but step 1 (Make sure that the source file is stripped of quotation marks.) already makes the rest of this website irrelevant (to me).
While your procedure may be valuable to some people, I often work with databases containing free text, including commas and quotationmarks.
I guess I’ll have to search on….
Things can change quickly. The home page for SQLite browser, http://sqlitebrowser.sourceforge.net/, claims that version 1.2, released 2 months after your post, is compatible with SQLite 3.
The easiest way to do this is with the tksqlite application available for download at:
http://reddog.s35.xrea.com/wiki/TkSQLite.html
I’m surprised it has not received more attention as it is (in my opinion) the best free tool for working with sqlite.
Stripping quotation marks out doesn’t really help with data that has commas in it.
1, “2nd Floor, 3rd Block”, NY
Stripping quote out would replace NY with 3rd Block which wouldn’t really help.
@Furbo: Thanks for the pointer. TkSQLite works like a charm!
Can I ask some question about sqlite3
I ‘ve already insert the comand like this
sqlite> create table fonepa(A,B,C);
sqlite> .seperater ” ”
sqlite> .import “Jan.csv” test
and it ’s said that cannot open Jan.csv
So what should I drop my file
I ‘ve drop my file at C:\Jan.csv
and I’ve done the command
sqlite> create table fonepa(A,B,C);
sqlite> .seperater ” ”
sqlite> .import “C:\Jan.csv” test
cannot open Jan.csv
again so what should I do
Right now I’ve already got the method
It’s just drop file in the same place that sqlite in !!!!!