Fill an Android database from a CSV file?

Question

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

My thought was that if there was a way to mass import the entire file into a table, it would be cleaner and faster than repeating each line in the file and executing separate insert instructions ...

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

... but I'm having problems applying these statements in an Android app. 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 it possible?

Should I try to use a different approach to populate my database?

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

Thanks for answers!

Answer 1

If you want to pack static data with your application, I recommend preparing the database during development (using any UI command or csv-import that you like) and sending the sqlite file to the resource folder. Then you can simply copy the entire sqlite file to the device the first time you launch the application. These posts permeate this idea, which is most likely the fastest way to set up a database (file copy speed).

If for some reason you need to insert a large amount of data at runtime, I recommend that you take a look at the ways bulk insert using transactions to speed it up.

Answer 2

I spent a lot of time looking for a simple solution and came up with this little code myself. For future people who are looking for this, like me, 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 headers and has only 2 columns, but more than two columns should not be a problem. Remember to indicate what your columns are separating, and for each column add another RowData[#] (you need to start at 0). You want to make sure that everything you are going to do with each line will be executed before calling in.close() . I use a content provider, but you can really do whatever you want with data such as adding it to String[] or something else.

As long as I use the input stream, you can point the BufferedReader to wherever you want. While BufferedReader can read it, it will work.