How to bulk insert a CSV file in SQLite C #

Question

I saw similar questions ( 1 , 2 ), but none of them discuss how to embed CSV files in SQLite. The only thing I could think of was to use the CSVDataAdapter and populate the SQLiteDataSet , and then use the SQLiteDataSet to update the tables in the database:

The only DataAdapter file for CSV that I found is actually not 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 code above @ http://devintelligence.com/2005/02/dataadapter-for-csv-files/
The CSVDataAdapter come with the OpenNetCF SDF , but it didn't seem to be available anymore.

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

Answer 1

try this - Import / Export CSV from SQLite from C # Code

Answer 2

You can use any of several tools to transfer data from a CSV file to a database, including:

• SQL Workbench Using WbCopy Command
• a ETL tool such as Pentaho Data Integration
• DDLUtils

Note. The first and third solutions require that you access the CSV file through the jdbc interface.

All this will allow you to some extent customize the migration process (for example, batch size), and all of them assume that you want to perform the migration manually, instead of running the C # code (which complicates the situation a bit).

Answer 3

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

Answer 4

I have a good personal experience with FileHelpers ( http://www.filehelpers.net/ ).

Answer 5

Turning to the last part of your question:

Perhaps someone knows a lot easier: how to do voluminous inserts of CSV files in SQLite ...

Given you need to import several thousand (or a million) records into sqlite from a CSV file,
When there is no direct support for importing csv data using select or insert commands, And the iterative line by line reading and pasting does not work
Then a practical alternative is to use "sqlite? .Exe" and the import command through the shell is executed 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> 

Answer 6

public void LoadCheckFiletoDatabase (string checkFilePath) {DataTable tempTable = GetDataTableFromCsv (checkFilePath);

  foreach (DataRow DataR in this.memDataTable.Columns) { Dictionary<string, object> Dic = new Dictionary<string, object>(); foreach (DataColumn DataCol in this.memDataTable.Columns) { string field = DataCol.ColumnName.ToString(); object value = (string)DataR[DataCol].ToString(); Dic.Add(field, value); } using (SQLiteConnection sQLiteConnection = new SQLiteConnection("data source=" + databasepath)) { using (SQLiteCommand sQLiteCommand = new SQLiteCommand(sQLiteConnection)) { System.Data.SQLite.SQLiteHelper sQLiteHelper = new SQLiteHelper(sQLiteCommand); sQLiteHelper.BeginTransaction(); sQLiteHelper.Insert(tempTable.TableName, Dic); } } } public static DataTable GetDataTableFromCsv(string path) { string pathOnly = Path.GetDirectoryName(path); string fileName = Path.GetFileName(path); string sql = @"SELECT * FROM [" + fileName + "]"; using(OleDbConnection connection = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly + ";Extended Properties=\"Text;HDR=" + "Yes" + "\"")) using(OleDbCommand command = new OleDbCommand(sql, connection)) using(OleDbDataAdapter adapter = new OleDbDataAdapter(command)) { DataTable dataTable = new DataTable(); dataTable.Locale = CultureInfo.CurrentCulture; adapter.Fill(dataTable); dataTable.TableName = fileName.TrimEnd(new char[] {'.','c','s','v'}); return dataTable; } }