(cache) SQLite CVSTrac

This information is obsolete. You are looking at the CVSTrac source management system display for SQLite that was replaced by Fossil on 2009-08-11. The information shown here has not been updated since that cut-over. These pages are retained for historical reference only.

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:

Fields with commas in them. The SQLite shell will always split fields on the separator character, no matter what comes before or after it. Quotes or backslashes won't escape them.
Quoted fields. The SQLite shell will interpret quotes literally, so the imported database will have embedded quote marks in them.
Fields with carriage returns or newlines. The SQLite shell inteprets them as ending the row.

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>] ] );

<charset> is the character set name to pass to iconv(). The default character set is "CP1252" (Windows Western Europe).
<delims> is a string where the first character is the csv file field delimiter and the second character is the string delimiter. The default field delimiter is the ";" character and the default string delimiter is the "\"" character.

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)

sqlite - Importing Files

Attachments: