How to convert SQLite database in android studio and then share it as csv file

Question

Im making an app in which the users inserts values into a table (SQLite DB) and I want to make a share button which will share the the table as an excel file (.csv).

Answer 1

The following is a rudimentary example. That will write a very basic CSV file based upon a query that concatenates the columns with a , between the columns.

The table has 3 columns named :-

• _id
• _name
• _email

The query used is effectively SELECT _id||','||_name||','||_email FROM test1;, although the SQLiteDatabase query method is used to build the SQL.

The method getCSVRows of the DBHelper class returns a Cursor based upon the above (i.e. a single column, the CSV, that can be accessed via offset 0).

Each time the App is run two rows are added, via the addTest1Row method.

The App has a button that when clicked invokes the crtCSV method that basically :-

1. gets the column names as a CSV (really this method should be used to drive the getCSVRows method, as there is the potential to have the column names in the wrong place) and writes the line with these first.
2. retrieves the Cursor with the data as a CSV string and writes a line.

The output file is located in the mycsvfiles directory of the Downloads directory (note only the most basic of checks are done, so this may not work on some devices).

The file name will be unique as it is suffixed with the current timestamp.

This screenshot shows 3 such files (a taken from Android Studio's device Explorer) :-

This shows it opened in Android Studio :-

And lastly in Excel :-

Here's the code:-

First, as Permission is required the relevant section of the Manifest AndroidManifest.xml

<uses-permission
    android:name="android.permission.WRITE_EXTERNAL_STORAGE">
</uses-permission>

If the App is to be used for any device that uses an API greater than 22 then permission has to be requested. As such there is a class for this namely ExternalStoragePermissions.java for handling this request :-

class ExternalStoragePermissions {

    public int API_VERSION = Build.VERSION.SDK_INT;
    private static final int REQUEST_EXTERNAL_STORAGE = 1;
    private static String[] PERMISSIONS_STORAGE = {
            Manifest.permission.WRITE_EXTERNAL_STORAGE
    };

    public ExternalStoragePermissions() {}
    // Note call this method
    public static void verifyStoragePermissions(Activity activity) {
        int permission = ActivityCompat.checkSelfPermission(
                activity,
                Manifest.permission.WRITE_EXTERNAL_STORAGE);

        if(permission != PackageManager.PERMISSION_GRANTED) {
            ActivityCompat.requestPermissions(
                    activity,
                    PERMISSIONS_STORAGE,
                    REQUEST_EXTERNAL_STORAGE
            );
        }
    }
}

• invoked from Main.Activity

The DatabaseHelper (i.e. Subclass of SQLiteOpenHelper, as is often used) is DBHelper.java :-

public class DBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "mydb";
    public static final int DBVERSION = 1;

    public static final String TB_TEST1 = "test1";

    public static final String COL_TEST1_ID = BaseColumns._ID;
    public static final String COL_TEST1_NAME = "_name";
    public static final String COL_TEST1_EMAIL = "_email";
    private static final String crtTest1SQL = "CREATE TABLE IF NOT EXISTS " +
            TB_TEST1 +
            "(" +
            COL_TEST1_ID + " INTEGER PRIMARY KEY," +
            COL_TEST1_NAME + " TEXT," +
            COL_TEST1_EMAIL + " TEXT" +
            ")";


    SQLiteDatabase mDB;


    public DBHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();
    }

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

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {
    }

    public long addTest1Row(String name, String email) {
        ContentValues cv = new ContentValues();
        cv.put(COL_TEST1_NAME,name);
        cv.put(COL_TEST1_EMAIL,email);
        return mDB.insert(TB_TEST1,null,cv);
    }

    public Cursor getCSVRows(String table) {
        Cursor rv;
        String[] columns;
        switch (table) {
            case TB_TEST1:
                columns = new String[]{COL_TEST1_ID + "||','||" + COL_TEST1_NAME + "||','||" + COL_TEST1_EMAIL};
                break;
                default:
                    return null;
        }
        return mDB.query(table,columns,null,null,null,null,null);
    }

    public String getColumnsAsCSV(String table) {
        StringBuilder sb = new StringBuilder("");
        if (ifTableExists(table)) {
            Cursor csr = mDB.rawQuery("PRAGMA table_info(" +
                    table +
                    ")",null);
            boolean after_first_row = false;
            int rowsdone = 0;
            while (csr.moveToNext()) {
                if (after_first_row) {
                    sb.append(",");
                } else {
                    after_first_row = true;
                }
                sb.append(csr.getString(csr.getColumnIndex("name")));
            }
        }
        return sb.toString();
    }

    private boolean ifTableExists(String table) {
        String whereclause = "name=?";
        String[] whereargs = new String[]{table};
        Cursor csr = mDB.query("sqlite_master",null,whereclause,whereargs,null,null,null);
        int rowcount = csr.getCount();
        csr.close();
        return rowcount > 0;
    }
}

Last is the Activity MainActivity.java

public class MainActivity extends AppCompatActivity {

    Button mConvert;
    DBHelper mDBHlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        // Need to have permission to External Storage
        if(Build.VERSION.SDK_INT >= 23) {
            ExternalStoragePermissions.verifyStoragePermissions(this);
        }

        mConvert = this.findViewById(R.id.convert_table);
        mConvert.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                crtCSV(DBHelper.TB_TEST1 + String.valueOf(System.currentTimeMillis()),DBHelper.TB_TEST1);
            }
        });
        mDBHlpr = new DBHelper(this);
        mDBHlpr.addTest1Row("Fred","[email protected]");
        mDBHlpr.addTest1Row("Mary","[email protected]");
    }

    private int crtCSV(String filename, String table) {
        if(!Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED)){
            return -1;
        }
        File dir = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS),"mycsvfiles");
        String directory = dir.getPath();
        if (!dir.exists()) {
            dir.mkdirs();
        }
        File f = new File(directory,filename);
        try {
            f.createNewFile();
        } catch (IOException e) {
            e.printStackTrace();
            return -2;
        }
        f.delete();

        FileOutputStream fo;
        try {
            fo = new FileOutputStream(f);
        } catch (IOException e) {
            e.printStackTrace();
            return -3;
        }
        Cursor csr = mDBHlpr.getCSVRows(table);
        BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(fo));
        try {
            bw.write(mDBHlpr.getColumnsAsCSV(table));
            bw.newLine();
        } catch (IOException e) {
            e.printStackTrace();
            try {
                fo.close();
            } catch (IOException e1) {
                e1.printStackTrace();
                f.delete();
                return -4;
            }
            f.delete();
            return -5;
        }
        while (csr.moveToNext()) {
            String line = csr.getString(0);
            try {
                bw.write(line);
                bw.newLine();
            } catch (IOException e) {
                e.printStackTrace();
                try {
                    fo.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                    f.delete();
                    return -6;
                }
                f.delete();
                return -7;
            }
        }
        csr.close();
        try {
            bw.close();
            fo.flush();
            fo.close();
        } catch (IOException e) {
            e.printStackTrace();
            return -8;
        }
        return 0;
    }
}

Answer 2

The easiest way, use CSVWriter like this:

implementation 'com.opencsv:opencsv:3.7'

And in MainActivity

 try {
            CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
            SQLiteDatabase db = myDb.getWritableDatabase();
            Cursor curCSV = db.rawQuery("select * from " + "your_database_table", null);

            csvWrite.writeNext(curCSV.getColumnNames());

            while (curCSV.moveToNext()) {

                String[] arrayStr = {curCSV.getString(0), curCSV.getString(1),
                        curCSV.getString(2), curCSV.getString(3),
                        curCSV.getString(4), curCSV.getString(5)};
                csvWrite.writeNext(arrStr);
            }
            csvWrite.close();
            curCSV.close();
           
        } catch (IOException e) {
            e.printStackTrace();
           
        }

where curCSV.getString() detect the column title, so start from 0 to number of your table column.