Populate Android Database From CSV file?

Question

Is it possible to take a csv file stored in the res/raw resource directory and use it to populate a table in the sqlite3 database?

My thought was that, if there was a way to do a bulk import for the entire file into the table then that would be cleaner and faster than iterating over each line in the file and executing individual insert statements...

I've found that there is a sqlite import command that allows this: How to import load a .sql or .csv file into SQLite?

...but I'm having trouble applying those statements in my Android application. My first thought was to try something like the following...but no luck:

db.execSQL("CREATE TABLE " + TABLE_NAME + "(id INTEGER PRIMARY KEY, name TEXT)");
db.execSQL(".mode csv");
db.execSQL(".import res/raw/MyFile.csv " + TABLE_NAME); 

Is this possible?

Should I be trying a different approach to populate my database?

UPDATE: I'm marking Josef's response as the answer (bulk insert using transactions) because it works fine and directly answers my question based on my title (thanks Josef). However, I'm am still looking for a way to do a bulk insert in an Android app from csv file into a sqlite3 table using the import statement. If you know how to do this please respond.

Thanks for you answers!

Answer 1

I spent a lot of time looking for a simple solution and came up with this little bit of code myself. For future people looking for this like I was you can use this:

BufferedReader in = new BufferedReader(your csv file source here);
String reader = "";
while ((reader = in.readLine()) != null){
    String[] RowData = reader.split(",");
    date = RowData[0];
    value = RowData[1];
    ContentValues values = new ContentValues();
    values.put(CsvProvider.DATE, date);
    values.put(CsvProvider.VALUE, value);
    getContentResolver().insert(CsvProvider.CONTENT_URI, values);
}
in.close();

My CSV file has no titles and only has 2 columns but more than two columns should not be a problem. Just remember to specify what is splitting your columns and for each column add another RowData[#](you have to start with 0). You want to make sure whatever you are going to do with each line is done before you call in.close(). I am using a content provider but you can really do whatever you want with the data like append it to a String[] or whatever else.

While I am using an input stream you can point the BufferedReader to wherever you want. As long as the BufferedReader can read it then it will work.

Answer 2

If you want to package static data with your application, I recommend preparing the database at development time (using any UI or csv-import command you like) and shipping the sqlite file inside the assets folder. You can then simply copy the entire sqlite file onto the device when your application is first run. These posts take you through this idea which is most likely the fastest way to setup a database (file copy speed).

If, for some reason you do need to insert a lot of data at run time, I recommend you look at ways to bulk insert using transactions to speed it up.