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!
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.
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.
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.
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>
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With