Steve Kamau Steve Kamau - 5 months ago 31
Android Question

Exporting SQLite Database to csv file in android

I am trying to export SQLite data to SD card in android as a CSV file on a directory.

So i have tried this method below and apparently it only shows this text printed out:

FIRST TABLE OF THE DATABASE

DATE,ITEM,AMOUNT,CURRENCY


In my DBHelper.java i have defined the function as follows:

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.
*/
SQLiteDatabase db = this.getReadableDatabase(); //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 = db.rawQuery("select * from contacts", null);
//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 title = curCSV.getString(curCSV.getColumnIndex("title"));
Float amount = curCSV.getFloat(curCSV.getColumnIndex("amount"));
String description = curCSV.getString(curCSV.getColumnIndex("description"));

/**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)) + "," + title + "," + amount + "," + description;
printWriter.println(record); //write the record in the .csv file
}

curCSV.close();
db.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;
}
}
}


And my columns are:

public static final String DATABASE_NAME = "MyDBName.db";
public static final String CONTACTS_TABLE_NAME = "contacts";
public static final String CONTACTS_COLUMN_ID = "id";
public static final String CONTACTS_COLUMN_TITLE = "title";
public static final String CONTACTS_COLUMN_AMOUNT = "amount";
public static final String CONTACTS_COLUMN_DESC = "description";


Let me know if you need more code.

Answer

Thanks for your suggestions guys which led me to this answer:

private void exportDB() {

        File dbFile=getDatabasePath("MyDBName.db");
        DBHelper dbhelper = new DBHelper(getApplicationContext());
        File exportDir = new File(Environment.getExternalStorageDirectory(), "");
        if (!exportDir.exists())
        {
            exportDir.mkdirs();
        }

        File file = new File(exportDir, "csvname.csv");
        try
        {
            file.createNewFile();
            CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
            SQLiteDatabase db = dbhelper.getReadableDatabase();
            Cursor curCSV = db.rawQuery("SELECT * FROM contacts",null);
            csvWrite.writeNext(curCSV.getColumnNames());
            while(curCSV.moveToNext())
            {
                //Which column you want to exprort
                String arrStr[] ={curCSV.getString(0),curCSV.getString(1), curCSV.getString(2)};
                csvWrite.writeNext(arrStr);
            }
            csvWrite.close();
            curCSV.close();
        }
        catch(Exception sqlEx)
        {
            Log.e("MainActivity", sqlEx.getMessage(), sqlEx);
        }