How to import file with SQLitePCL

Question

I am using SQLitePCL to create a database and I want to read in a csv file. However, I get an exception when I go to Prepare any SQLite 'dot-command' such as

.import file.csv table

Is there any way to import CSV files with SQLitePCL?

Answer 1

Update

I was unsatisfied with the existing answers and wanted a simple portable import using a PCL of SQLite. For most of my code I've used SQLitePCL.Pretty and tried to import batches of 500 records at a time via INSERT and the PrepareStatement method - this was horrendously slow. Luckily, since SQLitePCL.Pretty relies on SQLitePCL.raw, I was able to mimic the SQLite shell.c commands for importing, and this is very fast (add your own error handling).

var _dbname = "my.db";
var filename = "myfile.csv";
var hasHeaderRow = true;
var tablename = "MyTable";
sqlite3 db;
raw.sqlite3_open(_dbname, out db);
raw.sqlite3_exec(db, "BEGIN");
sqlite3_stmt stmt;

using (StreamReader file = new StreamReader(filename))
{
    string line;
    if (hasHeaderRow)
        file.ReadLine();
    while ((line = file.ReadLine()) != null)
    {
        string sql = "INSERT INTO " + tablename + " VALUES (" + String.Join(@",", SanitizeSql(line.Split(delimiter))) + ");";
        raw.sqlite3_prepare_v2(db, sql, out stmt);
        raw.sqlite3_step(stmt);
        var rc = raw.sqlite3_reset(stmt);
        raw.sqlite3_finalize(stmt);
        stmt.Dispose();
    }
}
raw.sqlite3_exec(db, "COMMIT");
db.Dispose();

Old Answer

The "dot-commands" are only available through the sqlite command line shell, and hence, are not available through any sqlite DLL.

Importing CSVs can be done - just search SO for other sqlite-C# CSV import questions. You'll get answers about using FileHelpers, StreamReader, OleDbConnection... See this answer for instance or this example on CodeProject.

You could also invoke the sqlite binary through a C# Process and pass in the "dot-commands" as RedirectionInputs (or in .sql files).