Export Room Database to CSV 📑

androidbites Feb 21, 2021

7 Easy steps 🚶‍♂ ️to export your Room Database into CSV File.

Featured On

It's fun being a part of TheCodeMonks, we are a FOSS organisation which solve problems by building better software solutions.

Recently SpikeySanju, has released another one of his android project called Expenso which is an expense tracker application.

Recently a feature request was made by coolnikhilmaurya, regarding the ability to export the transactions into a CSV file , you can track that issue here, for which I have raised a PR, which you can track here .

Preview

In this Article I will be breaking down how I have implemented this feature and how you can do the same for your own app in 7 simple steps.

I assume you have setup your Room Database correctly cause I will be sharing how to export it to CSV file in this article.

Step 1 : Setup and Dependencies

We will be using opencsv library in android to export our Room database, do override common-logging from the dependency as it will cause conflicts in gradle assemble.

Add these dependencies to your app-level gradle file

configurations {
    all {
    	// resolves conflicts of openCSV with platform common-logging
        exclude module: 'commons-logging'
    }
}

dependencies {
  ...
  // OpenCsv
  implementation ("com.opencsv:opencsv:5.3")
}

open csv gradle dependencies

Step 2 : Selecting Entity to Export

In Expenso app, we are exporting all the transaction details that has been logged by the user, which is stored using Transaction entity in Room database.


@Entity(tableName = "all_transactions")
data class Transaction(
    @ColumnInfo(name = "title")
    var title: String,
    @ColumnInfo(name = "amount")
    var amount: Double,
    @ColumnInfo(name = "transactionType")
    var transactionType: String,
    @ColumnInfo(name = "tag")
    var tag: String,
    @ColumnInfo(name = "date")
    var date: String,
    @ColumnInfo(name = "note")
    var note: String,
    @ColumnInfo(name = "createdAt")
    var createdAt: Long =
        System.currentTimeMillis(),
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    var id: Int = 0,
) : Serializable {
    val createdAtDateFormat: String
        get() = DateFormat.getDateTimeInstance()
            .format(createdAt) // Date Format: Jan 11, 2021, 11:30 AM
}

transaction entitiy from Expenso app

Step 3 : Setting up Service

Why Service? why not Repository? … IMO repositories are the data layer in your application , they are getters and setters for your data, while a service manipulates the data or performs some business logic.

Dirctory Strucrure
- packageName
  - services / csv / adapters / TransctionsCSV.kt 
  - services / csv / ExportService.kt
  - services / csv / ExportConfig.kt

Enjoying the Post?

a clap is much appreciated if you enjoyed. No sign up or cost associated :)

If you want to support my content do consider dropping a tip/coffee/donation💰

Step 4 : Setup Configuration for export

I have designed these classes to make export service easy to extend when more ways to export the transactions comes in.

For that I have created an exportable interface which will be the marker to every exportable entity of our application.

// Marker interface for exportable classes
interface Exportable

Currently app only supports CSV export, hence there is an Export sealed class, which has only CSV as hierarchy for now.

CSV is a data class which depends upon object of CSVConfig.

// List of supported export functionality
sealed class Exports {
    data class CSV(val csvConfig: CsvConfig) : Exports()
}

CSVConfig class is responsible to get csv file name and host path, as of now Android 11 uses scoped storage hence getExternalStorageDirectory shows deprecations.

You can replace it with your scoped storage implementation if you like. Currently our app will be supporting this feature till Android API 29. (I will be adding support for scoped storage later, then would be updating the article)

data class CsvConfig(
    private val prefix: String = "expenso",
    private val suffix: String = DateFormat
        .getDateTimeInstance()
        .format(System.currentTimeMillis())
        .toString()
        .replace(",","")
        .replace(" ", "_"),
    
    val fileName: String = "$prefix-$suffix.csv",
    @Suppress("DEPRECATION")
    val hostPath: String = Environment
        .getExternalStorageDirectory()?.absolutePath?.plus("/Documents/Expenso") ?: ""
)

By default the file name would be expenso-Jan_11_2021_11:30_AM and host path where file would be created will be /Documents/Expenso

Step 5 : Creating adapter/mapper classes

They are responsible to map/adapt your entities to model that would be exported out into CSV.

// We are transforming
Transaction Entity -> Transaction CSV which is Exportable Entity 
1. We create new data class for Transaction CSV which Extends exportable
2. We write a extension to convert list of transaction entity to transaction CSV

So we have created,

data class TransactionsCSV(
    @CsvBindByName(column = "title")
    val title: String,
    @CsvBindByName(column = "amount")
    val amount: Double,
    @CsvBindByName(column = "transactionType")
    val transactionType: String,
    @CsvBindByName(column = "tag")
    val tag: String,
    @CsvBindByName(column = "date")
    val date: String,
    @CsvBindByName(column = "note")
    val note: String,
    @CsvBindByName(column = "createdAt")
    val createdAtDate: String
) : Exportable

fun List<Transaction>.toCsv() : List<TransactionsCSV> = map {
    TransactionsCSV(
        title = it.title,
        amount = it.amount,
        transactionType = it.transactionType,
        tag = it.tag,
        date = it.date,
        note = it.note,
        createdAtDate = it.createdAtDateFormat,
    )
}

Here TransactionsCSV has properties marked by @CsvBindByName(column = "<column-Name>"), it’s an open-csv annotation that automatically creates a schema that binds object to the corresponding column and row.

Step 6 : Setup Export Service

In Export Service, I had exposed a function called export(type: Exports, content: List<T>) which takes type of export you want to perform and content you need to export, to enforce that content has been designed for exporting your content need to be Exportable type.

This export function delegate its work to writeToCSV which creates the host directory, if not already created then write the transaction list to csv file.

object ExportService {

    fun <T : Exportable> export(type: Exports, content: List<T>) : Flow<Boolean> = 
      when (type) {
          is Exports.CSV -> writeToCSV<T>(type.csvConfig, content)
      }

    @WorkerThread
    private fun <T : Exportable> writeToCSV(csvConfig: CsvConfig, content: List<T>) = 
      flow<Boolean>{
        with(csvConfig) {
            
            hostPath.ifEmpty { throw IllegalStateException("Wrong Path") }
            val hostDirectory = File(hostPath)
            if (!hostDirectory.exists()) {
                hostDirectory.mkdir() // 👈 create directory 
            }
            
            // 👇 create csv file
            val csvFile = File("${hostDirectory.path}/$fileName")
            val csvWriter = CSVWriter(FileWriter(csvFile)) 
            
            // 👇 write csv file
            StatefulBeanToCsvBuilder<T>(csvWriter)
                .withSeparator(CSVWriter.DEFAULT_SEPARATOR)
                .build()
                .write(content)

            csvWriter.close()
        }
        // 👇 emit success
        emit(true)
    }
}

Since it is a flow we can catch any failure in the catch chain no need to explicit use the try-catch, this keeps api simple and flat.

Step 7 : How to Trigger file export?

You can see the project code for demonstration from the file TransactionViewModel.kt, here is what the snippet looks like :

fun exportTransactionsToCsv() = viewModelScope.launch(IO) {
    // 👇 state manager for loading | error | success
    _exportCsvState.value = ViewState.Loading uccess
    
    // 👇 get all trasnaction detail from repository  
    val transactions = transactionRepo.getAllTransactions().first()
    
    // 👇 call export function from Export serivce
    ExportService.export<TransactionsCSV>(
        type = Exports.CSV(CsvConfig()), // 👈 apply config + type of export
        content = transactions.toCsv() // 👈 send transformed data of exportable type
    ).catch { error ->
        // 👇 handle error here
        _exportCsvState.value = ViewState.Error(error) 
    }.collect { _ ->
        // 👇 do anything on success
        _exportCsvState.value = ViewState.Success(emptyList())
    }
}