Small. Fast. Reliable.
Choose any three.
CSV Files

For simple CSV files, you can use the SQLite shell to import the file into your SQLite database. First create the table, then designate the separator, and finally import the file.

  sqlite> create table test (id integer, datatype_id integer, level integer, meaning text);
  sqlite> .separator ","
  sqlite> .import no_yes.csv test

Unfortunately, not all CSV files are simple. For instance, the CSV line

  "Last, First", 1234

means two columns in Excel (a name and an integer), but three columns with embedded quote marks in SQLite. Be wary when trying to import CSV files.

Some problems you would encounter importing CSV files using the SQLite shell:

There is no standard as to what a CSV file should look like, and the SQLite shell does not even attempt to handle all the intricacies of interpreting a CSV file. If you need to import a complex CSV file and the SQLite shell doesn't handle it, you may want to try a different front end, such as SQLite Database Browser.

Import XML to SQLite using PHP

The attached PHP script will import a large record set by parsing an XML file using SimpleXML. This works for PHP 5.1 with PDO setup for SQLite.

File will need to be edited to reflect the XML nodes in your file but is setup to handle a simple flat model with record / columns structure easily mapped to the new DB Table.

Import using a virtual table

The attached file csvfile.zip includes an implementation of a virtual table to read CSV files.

It needs to be compiled as an sqlite extension and loaded using the ".load" mechanism.

Then use it like this:

  CREATE VIRTUAL TABLE [<database>.]<table_name>
        USING CSVFILE( ?file? [, <charset> [, <delims>] ] );

You may need to tweak the code for your system.

Correction for attachment csvmbpg.tgz: In file virtual_csvmbpg.c:

  char charset[32] = "ISO-8859-1";

instead of

  char charset[32] = "";

Attachments:

  • XMLtoSQLite.php 2067 bytes added by anonymous on 2009-May-08 00:48:46 UTC.
  • csvfile.zip 6216 bytes added by anonymous on 2009-Sep-27 17:26:27 UTC.
    A SQLite 3 virtual table and extension module to read CSV files.
  • csvmbpg.tgz 4930 bytes added by anonymous on 2009-Nov-06 10:44:22 UTC.
    A SQLite 3 virtual table and extension module to read csv files as exported by postgresql, allows the delimiter character to be part of a field contents and allow multibyte character (e.g. BIG5 0xa5 0x7c) which may contain the delimiter character (e.g. 0x7c)