Exporting SQLite Database to CSV File in Android

Exporting SQLite Database to csv file in android

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

private void exportDB() {

        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);
        }
}

Sqlite Data To CSV file in android

you need to check runtime permission for Api 23:

try this:

if(isStoragePermissionGranted()){
        Save_file();
    }

Now add this methods in your activity:

    public void Save_file(){    
        File exportDir = new File(Environment.getExternalStorageDirectory(), ""); //

                if (!exportDir.exists())
                {
                    exportDir.mkdirs();
                }
                File file = new File(exportDir, "Abc.csv"); //File Name
                //writing data to a txt file
                try {

                    file.createNewFile();
                    CSVWriter csvWrite = new CSVWriter(new FileWriter(file));   //Writing data in csv file
                    for (int index = 0; index < list.size();) {
                        String arrStr[] = { String.valueOf(list.get(0))};
                        csvWrite.writeNext(arrStr);

                    }
                    csvWrite.close();

                    //But Above Code is not working for me

                } catch (IOException e) {

                    e.printStackTrace();
                }
            }
        }

public  boolean isStoragePermissionGranted() {
    if (Build.VERSION.SDK_INT >= 23) {
        if (checkSelfPermission(android.Manifest.permission.WRITE_EXTERNAL_STORAGE)
                == PackageManager.PERMISSION_GRANTED) {
            Log.v(TAG,"Permission is granted");
            return true;
        } else {

            Log.v(TAG,"Permission is revoked");
            ActivityCompat.requestPermissions(this, new String[]{Manifest.permission.WRITE_EXTERNAL_STORAGE}, 1);
            return false;
        }
    }
    else { //permission is automatically granted on sdk<23 upon installation
        Log.v(TAG,"Permission is granted");
        return true;
    }

}

@Override
public void onRequestPermissionsResult(int requestCode, String[] permissions, int[] grantResults) {
    super.onRequestPermissionsResult(requestCode, permissions, grantResults);
    if(grantResults[0]== PackageManager.PERMISSION_GRANTED){
        Log.v(TAG,"Permission: "+permissions[0]+ "was "+grantResults[0]);
        //resume tasks needing this permission
        Save_file();
    }
}

How to implement Export sqlite To excel/csv file in android?

Guys here is answer that I have implemented successfully

//new async task for file export to csv
private class ExportDatabaseCSVTask extends AsyncTask<String, String, Boolean> {
    private final ProgressDialog dialog = new ProgressDialog(SearchResultActivity.this);
    boolean memoryErr = false;

    // to show Loading dialog box
    @Override
    protected void onPreExecute() {
        this.dialog.setMessage("Exporting database...");
        this.dialog.show();
    }

    // to write process 
    protected Boolean doInBackground(final String... args) {

        boolean success = false;

        String currentDateString = new SimpleDateFormat(Constants.SimpleDtFrmt_ddMMyyyy).format(new Date());

        File dbFile = getDatabasePath("HLPL_FRETE.db");
        Log.v(TAG, "Db path is: " + dbFile); // get the path of db
        File exportDir = new File(Environment.getExternalStorageDirectory() + File.separator + Constants.FileNm.FILE_DIR_NM, "");

        long freeBytesInternal = new File(getApplicationContext().getFilesDir().getAbsoluteFile().toString()).getFreeSpace();           
        long megAvailable = freeBytesInternal / 1048576;

        if (megAvailable < 0.1) {               
            System.out.println("Please check"+megAvailable);
            memoryErr = true;               
        }else {             
            exportDirStr = exportDir.toString();// to show in dialogbox
            Log.v(TAG, "exportDir path::" + exportDir);
            if (!exportDir.exists()) {
                exportDir.mkdirs();
            }   
            try {
                List<SalesActivity> listdata = salesLst;
                SalesActivity sa = null;
                String lob = null;
                for (int index = 0; index < listdata.size();) {
                    sa = listdata.get(index);
                    lob = sa.getLob();
                    break;
                }
                if (Constants.Common.OCEAN_LOB.equals(lob)) {

                    file = new File(exportDir, Constants.FileNm.FILE_OFS + currentDateString + ".csv");
                } else {
                    file = new File(exportDir, Constants.FileNm.FILE_AFS + currentDateString + ".csv");
                }
                file.createNewFile();
                CSVWriter csvWrite = new CSVWriter(new FileWriter(file));

                // this is the Column of the table and same for Header of CSV
                // file
                if (Constants.Common.OCEAN_LOB.equals(lob)) {
                    csvWrite.writeNext(Constants.FileNm.CSV_O_HEADER);
                }else{
                    csvWrite.writeNext(Constants.FileNm.CSV_A_HEADER);
                }
                String arrStr1[] = { "SR.No", "CUTSOMER NAME", "PROSPECT", "PORT OF LOAD", "PORT OF DISCHARGE" };
                csvWrite.writeNext(arrStr1);

                if (listdata.size() > 0) {
                    for (int index = 0; index < listdata.size(); index++) {
                        sa = listdata.get(index);
                        String pol;
                        String pod;
                        if (Constants.Common.OCEAN_LOB.equals(sa.getLob())) {
                            pol = sa.getPortOfLoadingOENm();
                            pod = sa.getPortOfDischargeOENm();
                        } else {
                            pol = sa.getAirportOfLoadNm();
                            pod = sa.getAirportOfDischargeNm();
                        }
                        int srNo = index;
                        String arrStr[] = { String.valueOf(srNo + 1), sa.getCustomerNm(), sa.getProspectNm(), pol, pod };
                        csvWrite.writeNext(arrStr);
                    }
                    success = true;
                }
                csvWrite.close();

            } catch (IOException e) {
                Log.e("SearchResultActivity", e.getMessage(), e);
                return success;
            }
        }
        return success;
    }

    // close dialog and give msg
    protected void onPostExecute(Boolean success) {
        if (this.dialog.isShowing()) {
            this.dialog.dismiss();
        }
        if (success) {
            dialogBox(Constants.Flag.FLAG_EXPRT_S);
        } else {                
            if (memoryErr==true) {
                dialogBox(Constants.Flag.FLAG_MEMORY_ERR);
            } else {
                dialogBox(Constants.Flag.FLAG_EXPRT_F);
            }
        }
    }
}

Efficient export of Android SQLite database to CSV

Other questions on Stack Overflow led me to find that this model is called the entry-attribute-value (EAV) model and is very much not-preferred for data storage. Unfortunately, the format of our data and additional metatdata collected requires us to use this format. To convert this into a true relational format, I used the multi-join query provided in this answer: What is best performance for Retrieving MySQL EAV results as Relational Table

---

Related Topics