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,

1. Extract data from CSV file
2. Put that data into the database

Extract Data from CSV file

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);

  1 2 3

  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/