database - How to bulk insert a CSV file into SQLite C#

Question

I have seen similar questions (1, 2), but none of them discuss how to insert CSV files into SQLite. About the only thing I could think of doing is to use a CSVDataAdapter and fill the SQLiteDataSet, then use the SQLiteDataSet to update the tables in the database:

The only DataAdapter for CSV files I found is not actually available:

CSVDataAdapter CSVda = new CSVDataAdapter(@"c:MyFile.csv");

CSVda.HasHeaderRow = true;

DataSet ds = new DataSet(); // <-- Use an SQLiteDataSet instead

CSVda.Fill(ds);

To write to a CSV file:

CSVDataAdapter CSVda = new CSVDataAdapter(@"c:MyFile.csv");

bool InclHeader = true;

CSVda.Update(MyDataSet,"MyTable",InclHeader);

I found the above code @ http://devintelligence.com/2005/02/dataadapter-for-csv-files/
The CSVDataAdapter was supposed to come with OpenNetCF's SDF, but it doesn't seem to be available anymore.

Does anybody know where I can get a CSVDataAdapter? Perhaps somebody knows the much simpler thing: how to do bulk inserts of CSV files into SQLite... your help would be greatly appreciated!

Answer 1

Addressing the last part of your question:

Perhaps somebody knows the much simpler thing: how to do bulk inserts of CSV files into SQLite...

Given you need to import a few thousand (or a cpl of million) records into sqlite from a CSV file,
When there is no direct support for csv data import via the select or insert commands,
And the iterative row by row reading & inserting is not performant
Then a practical alternative is to use the "sqlite?.exe" & the import command via shell execute from your c# code.

loadcsvtosqlite.cs

Process proc = new Process {
    StartInfo = new ProcessStartInfo {
        FileName = @"loadcsvtosqlite.bat",
        Arguments = @"",
        UseShellExecute = true,
        RedirectStandardOutput = false,
        CreateNoWindow = true
    }
};
proc.Start();
proc.WaitForExit();

loadcsvtosqlite.bat

sqlite3.exe "db name" < loadcsv.sql

loadcsv.sql

drop table if exists <table name>;
create table <table name> (field1 datatype, field2 datatype ....);
.separator ","
.import <csv file name> <table name>