Sunday 26 January 2014

Exporting SQLite Database into .csv format

While developing my next app I came across an interesting problem: how do you export an SQLite database into .csv format? There are of course third party libraries that will do the job for you, but this is Android - The Technical Blog, so we want to understand "how stuff works" under the hood.


So let's start with Wikipedia...
According to Wikipedia csv stands for "comma separated values". CSV files are basically text files where values are separated by commas. There is not a formal standard defining .csv files, but the easiest way to convert an SQLite database into a .csv file is the following:

  1. first you create a text file with the .csv extension. This way you can easily read the file with Excel or OpenOffice Calc, but you can also use .txt suffix for reading the file with Notepad;
  2. you read the database one record after the other;
  3. different records in the database are represented by different lines in the .csv file, while the fields of each record are separeted by commas.
In the following example we use the java.io.FileWriter class, because we want to create a text file writing one character at a time (2 bytes), and the java.io.PrintWriter class as a Decorator (remember the Decorator pattern in Java?), because we want to write one line at a time in the file.

Here you can download the code in a more readable format.

public boolean exportDatabase() {
DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT, Locale.getDefault());

/**First of all we check if the external storage of the device is available for writing.
* Remember that the external storage is not necessarily the sd card. Very often it is
* the device storage.
*/
String state = Environment.getExternalStorageState();
if (!Environment.MEDIA_MOUNTED.equals(state)) { 
return false;
}
else {
//We use the Download directory for saving our .csv file.
   File exportDir = Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS);      
   if (!exportDir.exists()) 
   {
       exportDir.mkdirs();
   }
   
   File file;
   PrintWriter printWriter = null;
   try 
   {
    file = new File(exportDir, "MyCSVFile.csv");
       file.createNewFile();                
       printWriter = new PrintWriter(new FileWriter(file));
                  
       /**This is our database connector class that reads the data from the database.
        * The code of this class is omitted for brevity.
        */
    DBCOurDatabaseConnector dbcOurDatabaseConnector = new DBCOurDatabaseConnector(myContext);
    dbcOurDatabaseConnector.openForReading(); //open the database for reading
   
    /**Let's read the first table of the database.
    * getFirstTable() is a method in our DBCOurDatabaseConnector class which retrieves a Cursor
    * containing all records of the table (all fields).
    * The code of this class is omitted for brevity.
    */
    Cursor curCSV = dbcOurDatabaseConnector.getFirstTable();
    //Write the name of the table and the name of the columns (comma separated values) in the .csv file.
    printWriter.println("FIRST TABLE OF THE DATABASE");
    printWriter.println("DATE,ITEM,AMOUNT,CURRENCY");
       while(curCSV.moveToNext())
       {
        Long date = curCSV.getLong(curCSV.getColumnIndex("date"));
        String item = curCSV.getString(curCSV.getColumnIndex("item"));
        Double amount = curCSV.getDouble(curCSV.getColumnIndex("amount"));
        String currency = curCSV.getString(curCSV.getColumnIndex("currency"));
       
        /**Create the line to write in the .csv file. 
        * We need a String where values are comma separated.
        * The field date (Long) is formatted in a readable text. The amount field
        * is converted into String.
        */
        String record = df.format(new Date(date)) + "," + item + "," + importo.toString() + "," + currency;
   printWriter.println(record); //write the record in the .csv file
}
 
curCSV.close();
dbcOurDatabaseConnector.close();
   }
  
  catch(Exception exc) {
  //if there are any exceptions, return false
  return false;
  }
  finally {
  if(printWriter != null) printWriter.close();
  }
  
  //If there are no errors, return true.
  return true;
}
}

3 comments:

  1. can u please upload DBCOurDatabaseConnector class code..

    ReplyDelete
  2. it’s really nice and meaningful. it’s really cool blog. Linking is very useful thing.you have really helped lots of people who visit the blog and provide them useful information.

    Hadoop Training in HRBR Layout
    Hadoop Training in Kalyan Nagar
    Best Hadoop Training in Kalyan Nagar Bangalore

    ReplyDelete
  3. Its really nice blog. Thank you for sharing. Python Training in Bangalore is the most demanded training in the industry. With around 30% of jobs in the field of information technology demand good knowledge in Python programming. At Indian Cyber Security Solutions, we provide Python Training in Bangalore. This course is designed in such a way that it covers all topics from the basic to the advanced level. Python Course done by ICSS in Bangalore. Indian Cyber Security Solutions is the Best Python Institute in Bangalore.

    ReplyDelete