Room database Export/Import CSV on Android

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/

Aman Gupta 1 August 2019

. . .

4 comments

Zandro Zulueta Mar 17, 2020 at 8:43 pm

count is always 0 in if (count == 0). Can you check if your logic is correct? i am trying to read a csv file now and putting it into my Room dB

Zandro Zulueta Mar 17, 2020 at 11:14 pm
count = 1 should be after the for loop in order to read the values in the file. count =0 indicates that it is reading the first line with the column names

Reply
Zandro Zulueta Mar 17, 2020 at 11:15 pm
count =1 should be added after the for loop. count =1 indicates it can now read values in lines 2 and above while count = 0 indicates it is reading the column headers

Reply
- Armitage Shanks Oct 8, 2022 at 6:15 pm
  I believe that the error is in line 15 which reads
  for (int i = 0; i < curCSV.getColumnCount() – 1; i++)
  so that it never reads the last column
  
  and should read either
  
  for (int i = 0; i < curCSV.getColumnCount() ; i++)
  
  OR
  
  for (int i = 0; i <= curCSV.getColumnCount() – 1; i++)
  
  Reply

Room database Export/Import CSV on Android

Export Database to CSV file

Import Database from CSV file

Extract Data from CSV file

Put that data into the Database

Leave a Comment Cancel Reply

4 comments

Message Sent!