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

You can use any of a number of tools to migrate data from a .csv file to a database, including:

• SQL Workbench, using the WbCopy command
• an ETL tool, like Pentaho Data Integration
• DDLUtils

Note: the first and third solution require that you access the .csv file through a jdbc interface.

All of these will allow you to tweak the migration process to some degree (e.g. batch size) and all of them assume you want to do the migration manually, rather than from running C# code (which would complicate things a bit).

Answer 2

try this -http://stackoverflow.com/questions/2386074/import-export-csv-from-sqlite-from-c-code

Answer 3

This link be really helpful http://procbits.com/2009/09/08/sqlite-bulk-insert/

Answer 4

I've got good personal experiences with FileHelpers (http://filehelpers.sourceforge.net/).