Dumping SQLite table into CSV file in Android Studio

Question

I need to dump all the contents from a table into a csv file. I googled a bit and scoured SO to find an answer but I can't seem to find what I'm looking for exactly, here's what I found : Android - Generate CSV file from table values and http://howtodoinjava.com/2014/08/12/parse-read-write-csv-files-opencsv-tutorial/. Here's my DB :

public class DBhelper extends SQLiteOpenHelper {

//TABLE COLUMNS
private static final String[] COLUMNS = {DBhelper.ID, DBhelper.GIFTCARDS_NUMBER, DBhelper.GIFTCARDS_CREATED,
        DBhelper.GIFTCARDS_CREATOR, DBhelper.GIFTCARDS_BALANCE};
private static final String ID = "_id";
private static final String GIFTCARDS_NUMBER = "number";
private static final String GIFTCARDS_CREATED = "created";
private static final String GIFTCARDS_CREATOR = "creator";
private static final String GIFTCARDS_BALANCE = "balance";

//DATABASE INFORMATION
static final String DB_NAME = "GiftcardsDB";

//DATABSE VERSION
static final int DB_VERSION = 1;

// TABLE QUERY
private static final String CREATE_TABLE = "CREATE TABLE giftcards ( " + ID +
        " INTEGER PRIMARY KEY AUTOINCREMENT, " + GIFTCARDS_NUMBER + " TEXT NOT NULL, " + GIFTCARDS_CREATED +
        " TEXT NOT NULL, " + GIFTCARDS_CREATOR + " INTEGER NOT NULL, " + GIFTCARDS_BALANCE + " REAL);";

public DBhelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);

}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS Giftcards");
    onCreate(db);
}

I'd like some explanation on how does CSVWriter and/or ResultSet works. Thanks in advance !

Loop through your rows (adding a separator between column values), and append each one to a text file. Flush and close the file. — Phantômaxx, Jul 13 '15 at 15:19
So I only use a cursor and not CSVWriter and ResultSet ? Sorry I'm very new to Android! — Christophe Roberge, Jul 13 '15 at 15:22
A Cursor **is** a resultset, in the fact that it is a collection of rows. I tend to use the least possible of 3rd party libraries. So, yes, I do so. — Phantômaxx, Jul 13 '15 at 15:28
AH! Now it makes wayyyy more sense. Thanks for your timely response! :) — Christophe Roberge, Jul 13 '15 at 15:30
I've created the file in external storage in it's own folder and created a cursor like this : Cursor cursor = db.rawQuery("SELECT * FROM giftcards",null); Now I need to append each row in my file right ? I just don't know how to use the cursor to do this. — Christophe Roberge, Jul 13 '15 at 15:51
Once your cursor is populated by your query, simply make a loop and move through the returned rows. Join the columns with a separator (typically **,** or **;** - less frequently TAB or other fancy characters, such as **|**). Append each string to the open file. — Phantômaxx, Jul 13 '15 at 15:59

Zerp · Answer 1 · 2015-07-13T17:45:00.337

completely untested, and a bit crude, but something like this should do the trick.

    DatabaseHelper dbh = new DatabaseHelper(context);
    SQLiteDatabase db = dbh.getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT * FROM giftcards",null);
    FileOutputStream outputStream;

    if(!cursor.moveToFirst()){
        // something went wrong - bad sql or no results
    }

    File file = new File(context.getFilesDir(), "output.csv");
    try {
        outputStream = new FileOutputStream(file);


        do{

            // if any of the columns have commas in their values, you will have to do more involved
            // checking here to ensure they are escaped properly in the csv

            // the numbes are column indexes. if you care about the order of the columns in your
            // csv, you may want to move them around

            outputStream.write(cursor.getString(0).getBytes());
            outputStream.write(",".getBytes());
            outputStream.write(cursor.getString(1).getBytes());
            outputStream.write(",".getBytes());
            outputStream.write(cursor.getString(2).getBytes());
            outputStream.write(",".getBytes());
            outputStream.write(cursor.getString(3).getBytes());
            outputStream.write(",".getBytes());
            outputStream.write(cursor.getString(4).getBytes());
            outputStream.write("\n".getBytes());


        } while(cursor.moveToNext());

        outputStream.flush();
        outputStream.close();

    } catch (Exception e){

        e.printStackTrace();
    }


    cursor.close();

You better flush the stream, before closing it. – Phantômaxx Jul 13 '15 at 17:34 — Phantômaxx, Jul 13 '15 at 17:34
Thanks for catching that. Fixed. – Zerp Jul 13 '15 at 17:45 — Zerp, Jul 13 '15 at 17:45

score 0 · Answer 2 · answered Jul 13 '15 at 20:13

Here's how I did it. I then made a method to send the csv file by email.

public String createCSV(){

    boolean var = true;
    File folder = new File(Environment.getExternalStorageDirectory() + "/Folder");
    if (!folder.exists())
        var = folder.mkdir();
    final String filename = folder.toString() + "/" + "Giftcards.csv";

    Uri u1 = null;


    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT * FROM giftcards", null);


    try {
        FileWriter fw = new FileWriter(filename);

        if (cursor != null) {
            cursor.moveToFirst();
            for (int i = 0; i < cursor.getCount(); i++) {
                for (int j = 0; j < cursor.getColumnNames().length; j++) {
                    fw.append(cursor.getString(j) + ";");
                }
                fw.append("\n");

                cursor.moveToNext();
            }
            cursor.close();
        }
        fw.close();
    } catch(Exception e){
    }
    return filename;
}

score 0 · Answer 3 · answered Mar 01 '17 at 03:06

i think its too late but this is how i do it , hope it helps to others, after concerting to csv i send it via bluetooth

 SQLiteDatabase database = controller.getReadableDatabase();
            Cursor c = null;
            try
            {
                c = database.rawQuery("Select * FROM Countsheet" , null);
                int rowcount = 0;
                int colcount = 0;
                File sdCardDir = Environment.getExternalStorageDirectory();
                String filename = "/Physical Count.csv";
                File saveFile = new File(sdCardDir,filename);
                FileWriter fileWriter = new FileWriter(saveFile);

                BufferedWriter bw  = new BufferedWriter(fileWriter);
                rowcount = c.getCount();
                colcount = c.getColumnCount();

                if (rowcount > 0 )
                    c.moveToFirst();
                for (int i = 0 ; i < colcount; i++)
                {
                    if (i != colcount -1){
                        bw.write(c.getColumnName(i) + ",");
                    }
                    else {
                        bw.write(c.getColumnName(i));
                    }

                }
                bw.write("\r\n");
                for (int i = 0; i < rowcount;i++){
                    c.moveToPosition(i);
                    for (int j = 0;j < colcount;j++){
                        if (j != colcount-1)
                        {
                            bw.write(c.getString(j)+ ",");
                        }else
                        {
                            bw.write(c.getString(j));
                        }

                    }
                    bw.write("\r\n");
                    bw.flush();
//                       lbl.setText("Exported Successfully.");

                }
                File sourceFile = new File(Environment.getExternalStorageDirectory(),"Physical Count.csv");
                Intent intent = new Intent();
                intent.setAction(Intent.ACTION_SEND);
                intent.setType("text/plain");
                intent.putExtra(Intent.EXTRA_STREAM, Uri.fromFile(sourceFile));
                startActivity(intent);
            }
            catch (Exception ex)
            {
                if (database.isOpen()){
                    database.close();
                }
            }

Dumping SQLite table into CSV file in Android Studio

3 Answers3