How to export Room Database to .CSV-CodePudding

How can I export my Room Database to a .CSV file. I would like it to be saved to device storage. I searched everything and no answer was suitable. I hope there is a way for this.

CodePudding user response：

You cannot just save a database as a CSV. However the database, if fully checkpointed, is just a file. If not fully checkpointed then it (unless write-ahead logging as been disabled) would be three files.

The database itself consists of various parts, a header (first 100 bytes of the file) and then blocks of data for the various components. Most of these dependant upon the schema (the tables), there are also system tables

sqlite_master is a table that holds the schema
if autogenerate = true is used for a integer type primary key then there is also the sqlite_sequence table
room itself has the room_master_table in which room stores a hash, this being compared against a compiled hash based upon the Room's expected schema.

To save all that data as a CSV, would be complex (and needless as you can just copy the database file(s)).

If what you want is a CSV of the app's data, then that would depend upon the tables. If you a single table then extracting the data as a CSV would be relatively simple but could be complicated if the data includes commas.

If there are multiple tables, then you would have to distinguish the data for the tables.

Again the simplest way, if just securing the data is to copy the file.

However as an example based upon :-

A database that has 3 tables (apart from the system tables)

PostDataLocal (see below for columns)
GroupDataLocal
AdminDataLocal
- an existing answer has been adapted for the example

Then:-

The following in an @Dao annotated interface (namely AllDao) :-

@Query("SELECT postId||','||content FROM postDataLocal")
fun getPostDataLocalCSV(): List<String>
@Query("SELECT groupPostIdMap||','||groupId||','||groupName FROM groupDataLocal")
fun getGroupDataLocalCSV(): List<String>
@Query("SELECT adminGroupIdMap||','||userId||','||adminName||','||avatar FROM adminDataLocal")
fun getAdminDataLocalCSV(): List<String>

And the following function where dao is an AllDao instance previously instantiated :-

private fun createCSV() {

    val sb = StringBuilder()
    var afterFirst = false
    sb.append("{POSTDATALOCAL}")
    for (s in dao.getPostDataLocalCSV()) {
        if(afterFirst) sb.append(",")
        afterFirst = true
        sb.append(s)
    }
    afterFirst = false
    sb.append("{GROUPDATALOCAL}")
    for (s in dao.getGroupDataLocalCSV()) {
        if (afterFirst) sb.append(",")
        afterFirst = true
        sb.append(s)
    }
    afterFirst = false
    sb.append("{ADMINDATALOCAL}")
    for (s in dao.getAdminDataLocalCSV()) {
        if ((afterFirst)) sb.append(",")
        afterFirst = true
        sb.append(s)
    }
    Log.d("CSV_DATA","CSV is :-\n\t$sb")

}

And then in an activity (where dao has been instantiated) the following:-

createCSV()

Then, when the database contains the following data (extracted via App Inspection) :-

PostDataLocal

GroupDataLocal

AdminDataLocal

The result written to the log (as could be written to a file rather than the log) is :-

D/CSV_DATA: CSV is :-
        {POSTDATALOCAL}1,Post001,2,Post002,3,Post003{GROUPDATALOCAL}1,1,Group001 (Post001),1,2,Group002 (Post001),1,3,Group003 (Post001),2,4,Group004 (Post002),2,5,Group005 (Post002),3,6,Group006 (Post003){ADMINDATALOCAL}1,1,Admin001,admin001.gif,1,2,Admin002,admin002.gif,1,3,Admin003,admin003.gif,2,4,Admin004,admin004.gif,2,5,Admin005,admin005.gif,3,6,Admin006,admin006.gif,4,7,Admin007,admin007.gif,5,8,Admin008,admin008.gif,6,9,Admin009,admin009.gif,6,10,Admin010,admin010.gif

Note how headers have been included to distinguish between the tables
of course no consideration has been given to the inclusion of commas in the data (the above is intended to just show that in-principle you can generate a CSV representation of the data relatively easily)

Additional

Here's a more automated version in which you don't need to create the @Query annotated functions, rather it interrogates sqlite_master to extract the tables and the uses the table_info pragma to ascertain the columns, building the respective SQL.

As such it should cater for any Room database.

It also allows for the replacement of commas in the data with an indicator of a comma that could then be replaced when processing the CSV.

The supportive (secondary/invoked by the primary) function being :-

private fun getTableColumnNames(tableName: String, suppDB: SupportSQLiteDatabase): List<String> {
    val rv = arrayListOf<String>()
    val csr = suppDB.query("SELECT name FROM pragma_table_info('${tableName}')",null)
    while (csr.moveToNext()) {
        rv.add(csr.getString(0))
    }
    csr.close()
    return rv.toList()
}

And the Primary function :-

private fun AutoCreateCSV(): String {
    val replaceCommaInData = "{COMMA}" /* commas in the data will be replaced by this */
    val rv = StringBuilder()
    val sql = StringBuilder()
    var afterFirstTable = false
    var afterFirstColumn = false
    var afterFirstRow = false
    val suppDb = db.getOpenHelper().writableDatabase
    var currentTableName: String = ""
    val csr = db.query("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE('sqlite_%') AND name NOT LIKE('room_%') AND name NOT LIKE('android_%')", null)
    while (csr.moveToNext()) {
        sql.clear()
        sql.append("SELECT ")
        currentTableName = csr.getString(0)
        if (afterFirstTable) rv.append(",")
        afterFirstTable = true
        afterFirstColumn = false
        rv.append("{$currentTableName},")
        for (columnName in getTableColumnNames(currentTableName,suppDb)) {
            if (afterFirstColumn) sql.append("||','||")
            afterFirstColumn = true
            sql.append("replace(`$columnName`,',','$replaceCommaInData')")
        }
        sql.append(" FROM `${currentTableName}`")
        val csr2 = db.query(sql.toString(),null)
        afterFirstRow = false
        while (csr2.moveToNext()) {
            if (afterFirstRow) rv.append(",")
            afterFirstRow = true
            rv.append(csr2.getString(0))
        }
        csr2.close()
    }
    csr.close()
    return rv.toString()
}

Using the same data and as the primary function returns a String the following code Log.d("CSV_DATA2",AutoCreateCSV()) results in :-

D/CSV_DATA2: {PostDataLocal},1,Post001,2,Post002,3,Post003,{GroupDataLocal},1,1,Group001 (Post001),1,2,Group002 (Post001),1,3,Group003 (Post001),2,4,Group004 (Post002),2,5,Group005 (Post002),3,6,Group006 (Post003),{AdminDataLocal},1,1,Admin001,admin001.gif,1,2,Admin002,admin002.gif,1,3,Admin003,admin003.gif,2,4,Admin004,admin004.gif,2,5,Admin005,admin005.gif,3,6,Admin006,admin006.gif,4,7,Admin007,admin007.gif,5,8,Admin008,admin008.gif,6,9,Admin009,admin009.gif,6,10,Admin010,admin010.gif

and if the data includes a comma e.g. Post001 is changed to be the value Post001, <<note the comma in the data>>

Then :-

D/CSV_DATA2: {PostDataLocal},1,Post001{COMMA} <<note the comma in the data>>,2,Post002,3 ....

this additional solution also fixes a little bug in the first where some separating commas were omitted between the header and the data.