Menu
Does anybody have any tips on utilities that can be used to bulk load data that is stored in delimited text files into an SQLite database?
Ideally something that can be called as a stand-alone program from a script etc.
A group I work with has an Oracle Database that's going to dump a bunch of data out to file and then load that data into an SQLite database for use on a mobile device and are looking for the easiest way to implement that sort of scenario.
679
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.
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 supports databases up to 281 terabytes in size, assuming you can find a disk drive and filesystem that will support 281-terabyte files.
SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update.
Check out the sqite .import command - it does exacty this.
You can set the separator with the .separator command
sqlite3 myDatabase create table myTable (a, b, c); .separator ',' .import myFile myTable Why do you want a text file?
Just use Java which does have easily available libraries for Oracle and SQLite access. Connect to both databases and just select from one db and insert into another with no additional complexity of CSV, which is not a very well defined format and will give you problems with character encoding, quotes, comas/tabs or semicolons, newlines etc. in your data.
45
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
