export sqlite into csv

Question

im trying to take data from my sqlite database and write it into csv file and email the information when done writing.i only have one Table with 3 columns in the database.i have DBAdapter and a class that have the Button that the user must press to be able to export the data.

Here is the code i have tried Exportdata.java

try {
    root = Environment.getExternalStorageDirectory();                   
    Log.i(TAG,"path.." +root.getAbsolutePath());  

    //check sdcard permission  
    if (root.canWrite()) {  
        File fileDir = new File(root.getAbsolutePath()+"/fun/");  
        fileDir.mkdirs();  

        //   Log.d("DATABASE", db.getAllBYname());

        File file= new File(fileDir, "itisfun.csv");  
        FileWriter filewriter = new FileWriter(file);  
        BufferedWriter out = new BufferedWriter(filewriter);  
        out.write("I m enjoying......dude..............."  ); 
        out.close();  
    }  
} catch (IOException e) {  
    Log.e("ERROR:---", "Could not write file to SDCard" + e.getMessage());  
} 

This code does create the file in SDcard but the problem is i cant make this to take sqlite database data and write it in the file as shown on the code.im stuck please help me with example or edit my code with the right solution.

Audry

Answer 1

I use this routine to dump an SQLite database to a file on the SD card in CSV format:

private Boolean backupDatabaseCSV(String outFileName) {
    MyLog.d(TAG, "backupDatabaseCSV");
    Boolean returnCode = false;
    int i = 0;
    String csvHeader = "";
    String csvValues = "";
    for (i = 0; i < GC.CURCOND_COLUMN_NAMES.length; i++) {
        if (csvHeader.length() > 0) {
            csvHeader += ",";
        }
        csvHeader += "\"" + GC.CURCOND_COLUMN_NAMES[i] + "\"";
    }

    csvHeader += "\n";
    MyLog.d(TAG, "header=" + csvHeader);
    dbAdapter.open();
    try {
        File outFile = new File(outFileName);
        FileWriter fileWriter = new FileWriter(outFile);
        BufferedWriter out = new BufferedWriter(fileWriter);
        Cursor cursor = dbAdapter.getAllRows();
        if (cursor != null) {
            out.write(csvHeader);
            while (cursor.moveToNext()) {
                csvValues = Long.toString(cursor.getLong(0)) + ",";
                csvValues += Double.toString(cursor.getDouble(1))
                        + ",";
                csvValues += Double.toString(cursor.getDouble(2))
                        + ",";
                csvValues += "\"" + cursor.getString(3) + "\",";
                csvValues += Double.toString(cursor.getDouble(4))
                        + ",";
                csvValues += Double.toString(cursor.getDouble(5))
                        + ",";
                csvValues += "\"" + cursor.getString(6) + "\",";
                csvValues += Double.toString(cursor.getDouble(7))
                        + ",";
                csvValues += Double.toString(cursor.getDouble(8))
                        + ",";
                csvValues += Double.toString(cursor.getDouble(9))
                        + "\n";
                out.write(csvValues);
            }
            cursor.close();
        }
        out.close();
        returnCode = true;
    } catch (IOException e) {
        returnCode = false;
        MyLog.d(TAG, "IOException: " + e.getMessage());
    }
    dbAdapter.close();
    return returnCode;
}

GC is my Global Constants class which among other things contains the table column names. The column names are used to make the header row in the CSV file. The getAllRows is in the database adapter and returns all the rows in the table. The while dumps all the rows that were returned. The long values are comma separated and the text values are quoted as well as separated by a comma. The MyLog.d does a Log.d in test mode and does nothing when in production mode. The dbAdapter is defined outside of the function as a global variable:

DatabaseAdapter dbAdapter = null;

It is initialized in onCreate as:

dbAdapter = new DatabaseAdapter(getApplicationContext());

The dbAdapter is used in multiple functions in the activity. If you globally define your Database adapter, be sure you pair every dbAdapter.open() with a dbAdapter.close() whenever you use it. Also, don't forget to close every cursor you open.

Note: csvValues is a concatenation of the columns returned in each SQLite row. For each row returned in the SQLite cursor, csvValues is written as a row in the CSV table.

Trivia