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 TA

Solution 1:

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

privatevoidexportDB() {

        DBHelperdbhelper=newDBHelper(getApplicationContext());
        FileexportDir=newFile(Environment.getExternalStorageDirectory(), "");
        if (!exportDir.exists())
        {
            exportDir.mkdirs();
        }

        Filefile=newFile(exportDir, "csvname.csv");
        try
        {
            file.createNewFile();
            CSVWritercsvWrite=newCSVWriter(newFileWriter(file));
            SQLiteDatabasedb= dbhelper.getReadableDatabase();
            CursorcurCSV= 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);
        }
}

Solution 2:

In case someone still stumbles upon this question, I will post my solution, which is slightly more general than the accepted one. You should be able to export all tables in an sqlite database almost by copying the two classes below. Only other changes needed should be related to getting the application context and importing open csv.

Some parts are pretty much copy paste from other stackoverflow threads, but I couldn't find those anymore.

Sqlite exporter:

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.opencsv.CSVWriter;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * Can export an sqlite databse into a csv file.
 *
 * The file has on the top dbVersion and on top of each table data the name of the table
 *
 * Inspired by
 * https://stackoverflow.com/questions/31367270/exporting-sqlite-database-to-csv-file-in-android
 * and some other SO threads as well.
 *
 */publicclassSqliteExporter {
    privatestaticfinalStringTAG= SqliteExporter.class.getSimpleName();

    publicstaticfinalStringDB_BACKUP_DB_VERSION_KEY="dbVersion";
    publicstaticfinalStringDB_BACKUP_TABLE_NAME="table";

    publicstatic String export(SQLiteDatabase db)throws IOException{
        if( !FileUtils.isExternalStorageWritable() ){
            thrownewIOException("Cannot write to external storage");
        }
        FilebackupDir= FileUtils.createDirIfNotExist(FileUtils.getAppDir() + "/backup");
        StringfileName= createBackupFileName();
        FilebackupFile=newFile(backupDir, fileName);
        booleansuccess= backupFile.createNewFile();
        if(!success){
            thrownewIOException("Failed to create the backup file");
        }
        List<String> tables = getTablesOnDataBase(db);
        Log.d(TAG, "Started to fill the backup file in " + backupFile.getAbsolutePath());
        longstarTime= System.currentTimeMillis();
        writeCsv(backupFile, db, tables);
        longendTime= System.currentTimeMillis();
        Log.d(TAG, "Creating backup took " + (endTime - starTime) + "ms.");

        return backupFile.getAbsolutePath();
    }

    privatestatic String createBackupFileName(){
        SimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM-dd_HHmm");
        return"db_backup_" + sdf.format(newDate()) + ".csv";
    }

    /**
     * Get all the table names we have in db
     *
     * @param db
     * @return
     */publicstatic List<String> getTablesOnDataBase(SQLiteDatabase db){
        Cursorc=null;
        List<String> tables = newArrayList<>();
        try{
            c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
            if (c.moveToFirst()) {
                while ( !c.isAfterLast() ) {
                    tables.add(c.getString(0));
                    c.moveToNext();
                }
            }
        }
        catch(Exception throwable){
            Log.e(TAG, "Could not get the table names from db", throwable);
        }
        finally{
            if(c!=null)
                c.close();
        }
        return tables;
    }

    privatestaticvoidwriteCsv(File backupFile, SQLiteDatabase db, List<String> tables){
        CSVWritercsvWrite=null;
        CursorcurCSV=null;
        try {
            csvWrite = newCSVWriter(newFileWriter(backupFile));
            writeSingleValue(csvWrite, DB_BACKUP_DB_VERSION_KEY + "=" + db.getVersion());
            for(String table: tables){
                writeSingleValue(csvWrite, DB_BACKUP_TABLE_NAME + "=" + table);
                curCSV = db.rawQuery("SELECT * FROM " + table,null);
                csvWrite.writeNext(curCSV.getColumnNames());
                while(curCSV.moveToNext()) {
                    intcolumns= curCSV.getColumnCount();
                    String[] columnArr = newString[columns];
                    for( inti=0; i < columns; i++){
                        columnArr[i] = curCSV.getString(i);
                    }
                    csvWrite.writeNext(columnArr);
                }
            }
        }
        catch(Exception sqlEx) {
            Log.e(TAG, sqlEx.getMessage(), sqlEx);
        }finally {
            if(csvWrite != null){
                try {
                    csvWrite.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if( curCSV != null ){
                curCSV.close();
            }
        }
    }

    privatestaticvoidwriteSingleValue(CSVWriter writer, String value){
        writer.writeNext(newString[]{value});
    }
}

FileUtils:

publicclassFileUtils {

    publicstaticStringgetAppDir(){
        returnApp.getContext().getExternalFilesDir(null) + "/" + App.getContext().getString(R.string.app_name);
    }

    publicstaticFilecreateDirIfNotExist(String path){
        File dir = newFile(path);
        if( !dir.exists() ){
            dir.mkdir();
        }
        return dir;
    }

    /* Checks if external storage is available for read and write */publicstaticbooleanisExternalStorageWritable() {
        String state = Environment.getExternalStorageState();
        returnEnvironment.MEDIA_MOUNTED.equals(state);
    }

    /* Checks if external storage is available to at least read */publicstaticbooleanisExternalStorageReadable() {
        String state = Environment.getExternalStorageState();
        returnEnvironment.MEDIA_MOUNTED.equals(state) ||
                Environment.MEDIA_MOUNTED_READ_ONLY.equals(state);
    }
}

In addition to these two classes, you either need to pass context to the FileUtils, or have some other means of getting the context. In the above code App is just class extending Application for easy access to context.

Also remember to add Opencsv to gradle, i.e.

compile group: 'com.opencsv', name: 'opencsv', version: '4.1'

Then just call the export method of the Sqlite exporter class.

Solution 3:

First of all remove this line to have a valid csv formatted document.

printWriter.println("FIRST TABLE OF THE DATABASE");

Second, make sure you have data in your table and check if your query actually returns anything by debugging.