Exporting Room Database to csv file in android

Question

There are many tutorials available for exporting SQLite database to csv file but not enough stuff for exporting from room database.

Using sqlite export reference Exporting SQLite Database to csv file in android parsing each column of row manually for room. Following is my code:

     @Dao
     interface CategoryDao {
         @Query("SELECT * FROM Category")
         fun getAllCategory(): List<Category>
     }

//   Export csv logic

      val categoryList = categoryDao.getAllCategory()
      val csvWrite = CSVWriter(FileWriter(file))

      for (item in categoryList) {
         val arrStr = arrayOf<String>(item.categoryId, item.categoryName)
         csvWrite.writeNext(arrStr)
      }

Is there any other way to export csv. Even in room not getting columns name of table pragmatically so not able to create dynamically common logic for all table.

Solution 1

Try this
get Column data from Cursor

@Query("SELECT * FROM Category")
Cursor getAllCategory();

Solution 2

To Export the Room Database data into mydb.sqlite file and store into External storage, follow the steps.

  fun exportDatabase(){
      val sd = Environment.getExternalStorageDirectory()

      // Get the Room database storage path using SupportSQLiteOpenHelper 
      AppDatabase.getDatabase(applicationContext)!!.openHelper.writableDatabase.path

            if (sd.canWrite()) {
                val currentDBPath = AppDatabase.getDatabase(applicationContext)!!.openHelper.writableDatabase.path
                val backupDBPath = "mydb.sqlite"      //you can modify the file type you need to export
                val currentDB = File(currentDBPath)
                val backupDB = File(sd, backupDBPath)
                if (currentDB.exists()) {
                    try {
                        val src = FileInputStream(currentDB).channel
                        val dst = FileOutputStream(backupDB).channel
                        dst.transferFrom(src, 0, src.size())
                        src.close()
                        dst.close()
                    } catch (e: IOException) {
                        e.printStackTrace()
                    }
                }
            }
       }

Comments

[buzzingsilently]

Question is about exporting database and not about HOW TO USE PAGING LIBRARY!

[Jemshit Iskenderov]

It is about exorting db by iterating overvrows of tables. Because you might one to transform data while exporting. Exporting does not only mean dumping db and creating file from it with everything inside