Room database Export/Import CSV on Android

Aman Gupta

1 August 2019

In this blog, I am sharing my experience with the room database to export and import the data into the CSV file.

CSV is a simple file format used to store tabular data, such as a spreadsheet or database. Files in the CSV format can be imported to and exported from programs that store data in tables, such as Microsoft Excel or OpenOffice Calc. CSVstands for “comma-separated values”.

This is a very powerful database which is based on ORM. The room database provides an abstraction layer over SQLite to allow fluent database access while harnessing the full power of SQLite.

The library helps you create a cache of your app’s data on a device that’s running your app. This cache, which serves as your app’s single source of truth, allows users to view a consistent copy of key information within your app, regardless of whether users have an internet connection.

If you are a newbie in a room database then first you have to read these below blogs,

Android Room Persistence Library

Android Basic Structure – Kotlin and Room Database android

So let’s continue to further without any more introduction about Room database.

Export Database to CSV file

This is a simple and straight forward method for export data into CSV,

File exportDir = new File(Environment.getExternalStorageDirectory(), ""); if (!exportDir.exists()) { exportDir.mkdirs(); } File file = new File(exportDir, fileName + ".csv"); try { file.createNewFile(); CSVWriter csvWrite = new CSVWriter(new FileWriter(file)); Cursor curCSV = db.query("SELECT * FROM " + TableName, null); csvWrite.writeNext(curCSV.getColumnNames()); while (curCSV.moveToNext()) { //Which column you want to exprort String arrStr[] = new String[curCSV.getColumnCount()]; for (int i = 0; i < curCSV.getColumnCount() - 1; i++) arrStr[i] = curCSV.getString(i); csvWrite.writeNext(arrStr); } csvWrite.close(); curCSV.close(); ToastHelper.showToast(this, "Exported", Toast.LENGTH_SHORT); } catch (Exception sqlEx) { Log.e("MainActivity", sqlEx.getMessage(), sqlEx); }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

File exportDir = new File(Environment.getExternalStorageDirectory(), "");
        if (!exportDir.exists()) {
            exportDir.mkdirs();
        }

        File file = new File(exportDir, fileName + ".csv");
        try {
            file.createNewFile();
            CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
            Cursor curCSV = db.query("SELECT * FROM " + TableName, null);
            csvWrite.writeNext(curCSV.getColumnNames());
            while (curCSV.moveToNext()) {
                //Which column you want to exprort
                String arrStr[] = new String[curCSV.getColumnCount()];
                for (int i = 0; i < curCSV.getColumnCount() - 1; i++)
                    arrStr[i] = curCSV.getString(i);
                csvWrite.writeNext(arrStr);
            }
            csvWrite.close();
            curCSV.close();
            ToastHelper.showToast(this, "Exported", Toast.LENGTH_SHORT);
        } catch (Exception sqlEx) {
            Log.e("MainActivity", sqlEx.getMessage(), sqlEx);
        }

Note: You can take the CSVWriter file from this URL: https://github.com/rogerta/secrets-for-android/tree/master/app/src/main/java/au/com/bytecode/opencsv

Import Database from CSV file

For importing, I am dividing this section into 2 parts,

Extract data from CSV file
Put that data into the database

Extract Data from CSV file

CSVReader csvReader = new CSVReader(new FileReader(Environment.getExternalStorageDirectory() + "/" + TableName));
String[] nextLine;
int count = 0;
StringBuilder columns = new StringBuilder();
StringBuilder value = new StringBuilder();

while ((nextLine = csvReader.readNext()) != null) {
      // nextLine[] is an array of values from the line
         for (int i = 0; i < nextLine.length - 1; i++) {
             if (count == 0) {
                 if (i == nextLine.length - 2)
                     columns.append(nextLine[i]);
                 else
                     columns.append(nextLine[i]).append(",");
             } else {
                 if (i == nextLine.length - 2)
                    value.append("'").append(nextLine[i]).append("'");
                 else
                    value.append("'").append(nextLine[i]).append("',");
             }
        }

Log.d(TAG, columns + "-------" + value);

CSVReader csvReader = new CSVReader(new FileReader(Environment.getExternalStorageDirectory() + "/" + TableName));

String[] nextLine;

int count = 0;

StringBuilder columns = new StringBuilder();

StringBuilder value = new StringBuilder();

while ((nextLine = csvReader.readNext()) != null) {

// nextLine[] is an array of values from the line

for (int i = 0; i < nextLine.length - 1; i++) {

if (count == 0) {

if (i == nextLine.length - 2)

columns.append(nextLine[i]);

else

columns.append(nextLine[i]).append(",");

} else {

if (i == nextLine.length - 2)

value.append("'").append(nextLine[i]).append("'");

else

value.append("'").append(nextLine[i]).append("',");

}

Log.d(TAG, columns + "-------" + value);

Put that data into the Database

We are further dividing into two parts,

We have to create a raw query function into your DAO

@RawQuery Boolean insertDataRawFormat(SupportSQLiteQuery query);

1
2

@RawQuery
Boolean insertDataRawFormat(SupportSQLiteQuery query);

Using that query put the data into the Database

SimpleSQLiteQuery query = new SimpleSQLiteQuery("Insert INTO " + tableName + " (" + columns + ") " + "values(" + value + ")",
       new Object[]{});
getDb().cashDrawerDao().insertDataRawFormat(query);

SimpleSQLiteQuery query = new SimpleSQLiteQuery("Insert INTO " + tableName + " (" + columns + ") " + "values(" + value + ")",

new Object[]{});

getDb().cashDrawerDao().insertDataRawFormat(query);

A basic implementation of SupportSQLiteQuery which receives a query and its args and binds args based on the passed in Object type.

That’s done. Now you are able to export your database in CSV file and import from CSV.

Reference: The job saver of every developer 🙂 – https://stackoverflow.com/