Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to bulk insert a CSV file into SQLite C#

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!

like image 438
Kiril Avatar asked Apr 22 '10 08:04

Kiril


People also ask

How do I import a CSV file into SQLite?

First, from the menu choose tool menu item. Second, choose the database and table that you want to import data then click the Next button. Third, choose CSV as the data source type, choose the CSV file in the Input file field, and choose the ,(comma) option as the Field separator as shown in the picture below.

Does SQLite support bulk insert?

SQLite doesn't have any special way to bulk insert data. To get optimal performance when inserting or updating data, ensure that you do the following: Use a transaction. Reuse the same parameterized command.

How do I import a file into SQLite?

You can import a CSV file into SQLite table by using sqlite3 tool and . import command. This command accepts a file name, and a table name. Here, file name is the file from where the data is fetched and the table name is the table where the data will be imported into.


2 Answers

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>
like image 193
RadicalFish Avatar answered Oct 19 '22 23:10

RadicalFish


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.

like image 23
SysAdmin Avatar answered Oct 20 '22 00:10

SysAdmin