Quickly import CSV files to an SQLite local DB.

[slicedtoad]

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 command

Is that possible? What qt type would I use?

Any suggestions or links to example code would be much appreciated.

[Seba84]

The main problem is that you have to read big text files. From my experience, I don't think you can reduce a lot the time to load.

Did you tried to use the sqlite3 import command?

.import FILE TABLE Import data from FILE into TABLE

Hope it helps!

[Seba84]

Sorry, to complete the answer to your question: I didn't found in documentation any function or class to import sql data from text files.

[slicedtoad]

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.

[rp123]

@slicedtoad Hi "slicetoad", Can you share your code, I am working on similar project. Thanks in advance !!