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();
}