How does one export a Room table to a CSV format?

Question

I have an app right now that stores data in a Room database using several entities. What I need is to take the contents of a table, convert the data into a CSV format, and then to send that format via email. I have the very last part buttoned down, as it simply involves sending the file through an intent with ACTION_SEND. That's easy.

I understand that I can get the path to the actual database and manipulate that file. However, the designers of Room went to such great lengths to put SQLite into a black box that this seems like a bad idea.

I know that I can also write a query method in one of the DAOs that returns a string in CSV format that can then be turned into a File object. However, that is a lot of work, and I'm not going to waste my time if someone else has done so for me.

So, here's my question: How do I export a Room database table into a CSV formatted string? If there are multiple ways of doing so, is there a preferred way?

Bonus question: Is there any preferred way to back up the entire database?

Does this answer your question? [Exporting Room Database to csv file in android](https://stackoverflow.com/questions/51055184/exporting-room-database-to-csv-file-in-android) — Michele Dorigatti, Aug 18 '20 at 09:47

score 9 · Accepted Answer · answered Mar 06 '18 at 14:47

I understand that I can get the path to the actual database and manipulate that file. However, Google has gone to such lengths to obscure the SQLite details that this seems like a bad idea.

Google offers getOpenHelper() on your RoomDatabase, if you have a reason to want to work with the database more directly (e.g., execute arbitrary SQL that is not in your DAO).

I know that I can also write a query method in one of the DAOs that returns a string in CSV format that can then be turned into a File object

IMHO, creating a CSV file is not the responsibility of a DAO, any more than populating widgets is the responsibility of a DAO.

How do I export a Room database table into a CSV formatted string?

Create some sort of Report class that can query the DAO, generate the CSV, and write it to your desired location.

Or, find some existing code that can take a Cursor and generate a CSV from it. Use getOpenHelper() to get a SupportSQLiteDatabase, on which you can query() with your desired SQL to generate the Cursor.

Is there any preferred way to back up the entire database?

IMHO, close() the RoomDatabase, then copy the files (using getDatabasePath() and Java file I/O).

As to your last statement: how would I go about importing the file I obtain? — Patrick, Mar 06 '18 at 14:52
@Patrick: Reverse the process: copy the file from wherever your backup resides back to the location indicated by `getDatabasePath()`. — CommonsWare, Mar 06 '18 at 15:17

score 3 · Answer 2 · answered Jul 24 '20 at 10:06

It's really easy following these steps:

On Android Studio go to View - Tools - Device Files Explorer
Navigate to /data/data/<your.package>/databases/<your_app_name> and export the file.
Download http://sqlitebrowser.org
Open de DB on SQLite Browser, click Browse Data, select the table.
Go to File - Export - Export table(s) as CSV file...

Source: https://medium.com/@mattyskala/browse-sqlite-database-in-android-studio-4fbba6cca105

score 2 · Answer 3 · answered Jun 03 '20 at 09:18

It's really easy to export Room DB table to CSV file in Kotlin using kotlin-csv library.

Just get the list of DB table objects:

@Dao
interface DirectorDao {
    @get:Query("SELECT * FROM director ORDER BY full_name ASC")
    val allDirectors: LiveData<List<Director>>
}

Then user CSV writer to write into the file by going through all list items and adding the column values to CSV rows, one by one, like this:

csvWriter().open(csvFile) {
    // Header
    writeRow(listOf("[id]", "[name]", "[age]"))

    directorDao.allDirectors.value.forEachIndexed { index, director ->
        writeRow(listOf(index, director.fullName, director.age))
    }
}

I explain the whole flow here.

How does one export a Room table to a CSV format?

3 Answers3

Linked