Quickly import CSV files to an SQLite local DB.
-
I need to code a database loader that can handle fairly large (10MB to 1GB) csv files at a decent speed.
What's the best way to do this?
Running an execSQL("insert...") for every row would be quite slow I imagine. Would something along the lines of this be better?:
-load csv file into a qt type
-send it to the DB with some sort of built in qt commandIs that possible? What qt type would I use?
Any suggestions or links to example code would be much appreciated.
-
-
ok, thanks. I couldn't get import to work so I just went with the following approach:
Read cvs file into a QVector<QStringList> created by reading line by line and split(",")
Send it to the Database by looping db.exec("insert ...")
The first part is really fast (might be cause I'm using an ssd, idk) but the second part was slow so I googled till I found the solution:
Wrap the entire insert loop in a transaction by calling db.transaction(); before it and db.commit(); after it.Benched with a 5MB file, it takes about 100 milisecs to read and 1 second to write. About 25% of the writing is text processing/formatting.
-
ok, thanks. I couldn't get import to work so I just went with the following approach:
Read cvs file into a QVector<QStringList> created by reading line by line and split(",")
Send it to the Database by looping db.exec("insert ...")
The first part is really fast (might be cause I'm using an ssd, idk) but the second part was slow so I googled till I found the solution:
Wrap the entire insert loop in a transaction by calling db.transaction(); before it and db.commit(); after it.Benched with a 5MB file, it takes about 100 milisecs to read and 1 second to write. About 25% of the writing is text processing/formatting.
@slicedtoad Hi "slicetoad", Can you share your code, I am working on similar project. Thanks in advance !!