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>

Answer 2

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 3

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

you can create OleDbConnection to CSV file (just google it, it is very easy) then load rows to DataSet, then put that dataset into Sqlite by SqliteConnection. Few lines of code.

Answer 4

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

Answer 5

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;
            }            
        }