How to export sqlite database to a csv file

Sqlite database is a database which is embedded in android phone by default .It is  an opensource database which we can use to create,delete and edit data. If you are not having knowledge about how to create an sqlite database in android please refer Android Sqlite Example before start implementing this example. In this tutorial we will learn how to export sqlite database to csv file in android.

Steps

  • Create new project in android studio.
  • Create an sqlite database in application
  • Insert data into sqlite database
  • Export sqlite database to csv
  • Sharing the exported csv file

Create new project in android studio

Refer Android beginners app development guide if you are beginner or if you don’t know how to create project in android studio.

Create an sqlite database in application

For creating an sqlite database you need to have a DbHelper class which will implement the methods of SqliteOpenHelper and make use of SqliteDatabase class, so create a file in the same location and use the below code. All the functions required for processing sqlite database will come inside this file. Here i am implementing only create,insert,fetching actions only.

DbHelper.java

public class DBHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "codesfor.db";
    public static final String TABLE_PERSON = "table_person";
    public static final String NAME = "name";
    public static final String EMAIL = "email";

    Context context;

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub

        db.execSQL(
                "create table table_person " +
                        "(id integer primary key,name text, email text)"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }

//for inserting into database

    public boolean insert_person(PersonModel personModel) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", personModel.getName());
        contentValues.put("email", personModel.getEmail());
        try{
            db.insert(TABLE_PERSON, null, contentValues);
        }catch (Exception e){

            Log.d("e",e.toString());
        }

        return true;
    }

    //for displaying contents in database
    public ArrayList<String> GetAllNames() {
        ArrayList<String> persons = new ArrayList<String>();

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("SELECT * FROM " + TABLE_PERSON , new String[]{});
        res.moveToFirst();

        while (res.isAfterLast() == false) {

            persons.add(res.getString(res.getColumnIndex(NAME)));
            res.moveToNext();
        }
        return persons;
    }

    //function used for fetching data for exporting database

    public Cursor raw() {

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("SELECT * FROM " + TABLE_PERSON , new String[]{});

        return res;
    }
}

Insert data into sqlite database

In the above step we created a database codesfor.db with a table of  two coloums. So first we need to have a model class which will help us to pass all data together. This class will contain the getters and setters methods which will help us to insert or display data.so create a file under src/your-package/PersonModel.java and add the following code.

PersonModel.java

public class PersonModel {
    String name, email;

    public PersonModel(String name, String email) {
        this.name = name;
        this.email = email;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

Now simply add the following code in your onCreate method or in the action where you need to insert the data. Here i am hard coding names as name 1,name 2  etc… But you can give a form to user to enter the name and email and a button which triggers insert and you can give the following code on button click by replacing and email values with edittext value entered by the user.

new DBHelper(ExportCsv.this).insert_person(new PersonModel("name 1","email 1"));
new DBHelper(ExportCsv.this).insert_person(new PersonModel("name 2","email 2"));
new DBHelper(ExportCsv.this).insert_person(new PersonModel("name 3","email 3"));

Export sqlite database to csv

First we need to have a read write permission for our application ,For getting the permission just add the following lines to AndroidManifest.xml.

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

Now we need to have a button which triggers the export process. For creating a button just add the following code into your activity’s layout file.

<Button
    android:id="@+id/btn_export"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:text="Export" />

Now you need to have a CSVWriter class which is using to process the sqlite data and convert into csv. All you need to do is just create once java file under your package name and use the below code

CSVWriter.java

import java.io.IOException;
import java.io.PrintWriter;
import java.io.Writer;

/**
* Created by admin on 03-03-2018.
*/

public class CSVWriter {

private PrintWriter pw;
private char separator;
private char escapechar;
private String lineEnd;
private char quotechar;

public static final char DEFAULT_SEPARATOR = ',';
public static final char NO_QUOTE_CHARACTER = '\u0000';
public static final char NO_ESCAPE_CHARACTER = '\u0000';
public static final String DEFAULT_LINE_END = "\n";
public static final char DEFAULT_QUOTE_CHARACTER = '"';
public static final char DEFAULT_ESCAPE_CHARACTER = '"';

public CSVWriter(Writer writer) {
this(writer, DEFAULT_SEPARATOR, DEFAULT_QUOTE_CHARACTER,
DEFAULT_ESCAPE_CHARACTER, DEFAULT_LINE_END);
}

public CSVWriter(Writer writer, char separator, char quotechar, char escapechar, String lineEnd) {
this.pw = new PrintWriter(writer);
this.separator = separator;
this.quotechar = quotechar;
this.escapechar = escapechar;
this.lineEnd = lineEnd;
}

public void writeNext(String[] nextLine) {

if (nextLine == null)
return;

StringBuffer sb = new StringBuffer();
for (int i = 0; i < nextLine.length; i++) {

if (i != 0) {
sb.append(separator);
}

String nextElement = nextLine[i];
if (nextElement == null)
continue;
if (quotechar != NO_QUOTE_CHARACTER)
sb.append(quotechar);
for (int j = 0; j < nextElement.length(); j++) {
char nextChar = nextElement.charAt(j);
if (escapechar != NO_ESCAPE_CHARACTER && nextChar == quotechar) {
sb.append(escapechar).append(nextChar);
} else if (escapechar != NO_ESCAPE_CHARACTER && nextChar == escapechar) {
sb.append(escapechar).append(nextChar);
} else {
sb.append(nextChar);
}
}
if (quotechar != NO_QUOTE_CHARACTER)
sb.append(quotechar);
}

sb.append(lineEnd);
pw.write(sb.toString());

}

public void close() throws IOException {
pw.flush();
pw.close();
}

public void flush() throws IOException {

pw.flush();

}

}

After that you need to implement the following code outside the onCreate function of your activity file. Please note the following points

  • This will be an async task
  • Replace the directory name to the name you want in which the directory will be created in your external storage of the phone
  • You need to have run time permission for read and write external storage if your target version of app is greater than 21. If you are not familiar with how get run time permission in android please refer Android request permission dynamically example
public class ExportDatabaseCSVTask extends AsyncTask<String, Void, Boolean> {

    private final ProgressDialog dialog = new ProgressDialog(MainActivity.this);
    DBHelper dbhelper;
    @Override
    protected void onPreExecute() {
        this.dialog.setMessage("Exporting database...");
        this.dialog.show();
        dbhelper = new DBHelper(MainActivity.this);
    }

    protected Boolean doInBackground(final String... args) {

        File exportDir = new File(Environment.getExternalStorageDirectory(), "/codesss/");
        if (!exportDir.exists()) { exportDir.mkdirs(); }

        File file = new File(exportDir, "person.csv");
        try {
            file.createNewFile();
            CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
            Cursor curCSV = dbhelper.raw();
            csvWrite.writeNext(curCSV.getColumnNames());
            while(curCSV.moveToNext()) {
                String arrStr[]=null;
                String[] mySecondStringArray = new String[curCSV.getColumnNames().length];
                for(int i=0;i<curCSV.getColumnNames().length;i++)
                {
                    mySecondStringArray[i] =curCSV.getString(i);
                }
                csvWrite.writeNext(mySecondStringArray);
            }
            csvWrite.close();
            curCSV.close();
            return true;
        } catch (IOException e) {
            return false;
        }
    }

    protected void onPostExecute(final Boolean success) {
        if (this.dialog.isShowing()) { this.dialog.dismiss(); }
        if (success) {
            Toast.makeText(MainActivity.this, "Export successful!", Toast.LENGTH_SHORT).show();
            ShareGif();
        } else {
            Toast.makeText(MainActivity.this, "Export failed", Toast.LENGTH_SHORT).show();
        }
    }
}

Now start the async task on the onclick of your export button with the following code, which will create a csv file on the defined location with the data from the sqlite table. Please note that this is just a small example which can be customizable according to your requirement. If you want to add more coloums from different table all you need to do is just create a new cursor method for that table in DbHelper file and customize the above code to add that data as well

if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.HONEYCOMB) {

new ExportDatabaseCSVTask().executeOnExecutor(AsyncTask.THREAD_POOL_EXECUTOR);

} else {

new ExportDatabaseCSVTask().execute();
}

Sharing the exported csv file

Now we have the exported csv file in defined location in the code. But it will be very difficult to go there in file manager and search for a particular file. So for making things easier we will have a share button in which they can just share it will email watsap or any social media he want which allows sharing files. What you need to do is to implement the following function and call it inside the button onclick.

private void ShareFile() {
File exportDir = new File(Environment.getExternalStorageDirectory(), "/codesss/");
String fileName = "myfile.csv";
File sharingGifFile = new File(exportDir, fileName);
Intent shareIntent = new Intent(android.content.Intent.ACTION_SEND);
shareIntent.setType("application/csv");
Uri uri = Uri.fromFile(sharingGifFile);
shareIntent.putExtra(Intent.EXTRA_STREAM, uri);
startActivity(Intent.createChooser(shareIntent, "Share image"));
}

About the author

Hi guys, i am the author of codesfor. I am a B.Tech graduate currently working as an App developer. Apart from job i am a blogger and a freelancer.

Add a Comment

Your email address will not be published. Required fields are marked *