Export And Import Data DB Table To CSV and Vice Versa

Hi Guys,

Today we are learning about the exporting and importing the table data from DB to CSV and CSV to DB table. In that tutorial first we are doing to insert the Person data into DB table by using the ORM open source. Then Exporting the same table data into the CSV file and stored in sd card.

For inserting the text data into sqlite table by using the ORM open source. More detail about the ORM open source in the last tutorial Here.

For exporting the db table we are using the opencsv.jar file. With the help of this jar file we are able to write and read the data of table and write into the CSV file. You can download the opencsv.jare file Here

In the next part we are sending to mail the csv file with attached with the help of android.content.Intent.ACTION_SEND.
So lets start the coding for the same.

activity_main.xml

Person.java

package com.sunil.export;

import java.io.Serializable;

import com.j256.ormlite.field.DatabaseField;

public class Person implements Serializable{

 @DatabaseField(generatedId=true)
 private int id;
 @DatabaseField
 private String firtname;
 @DatabaseField
 private String lastname;
 @DatabaseField
 private String address;
 @DatabaseField
 private String email;
 
 
 public Person(){
  
 }
 
 public Person(String fname, String lname, String address, String email){
  this.firtname=fname;
  this.lastname=lname;
  this.address=address;
  this.email=email;
 }

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getFirtname() {
  return firtname;
 }

 public void setFirtname(String firtname) {
  this.firtname = firtname;
 }

 public String getLastname() {
  return lastname;
 }

 public void setLastname(String lastname) {
  this.lastname = lastname;
 }

 public String getAddress() {
  return address;
 }

 public void setAddress(String address) {
  this.address = address;
 }

 public String getEmail() {
  return email;
 }

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

DatabaseHelper.java

package com.sunil.export;

import java.sql.SQLException;
import java.util.List;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.dao.RuntimeExceptionDao;
import com.j256.ormlite.stmt.UpdateBuilder;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;

public class DatabaseHelper extends OrmLiteSqliteOpenHelper {
 
 private static final String TAG="DatabaseHelper";
 private static final String DATABASE_NAME = "person.db";
 private static final int DATABASE_VERSION = 1;
 private RuntimeExceptionDao personRuntimeDao=null;
 
 public DatabaseHelper(Context context) {
  super(context, DATABASE_NAME, null, DATABASE_VERSION);
  Log.v(TAG, "DatabaseHelper constructor call");
 }
 

   public RuntimeExceptionDao getPersonDataDao() {
  
  Log.v(TAG, "getTimeDataDao call");
  
  if (personRuntimeDao == null) {
   personRuntimeDao = getRuntimeExceptionDao(Person.class);
  }
  return personRuntimeDao;
   }
  public int addPersonData(Person project)
  {
   Log.v(TAG, "addPersonData call");
   RuntimeExceptionDao dao = getPersonDataDao();
   int i = dao.create(project);
   return i;
  }
     
  public List GetDataPerson()
 {
  Log.v(TAG, "GetDataPerson call");
  RuntimeExceptionDao simpleDao = getPersonDataDao();
  List list = simpleDao.queryForAll();
  return list;
 }
  
  public void deleteAllPerson()
 {
  Log.v(TAG, "deleteAllPerson call");
  RuntimeExceptionDao dao = getPersonDataDao();
  List list = dao.queryForAll();
  dao.delete(list);
 }
 
  public UpdateBuilder updatePersonData() throws SQLException
 {
 RuntimeExceptionDao simpleDao = getPersonDataDao();
 UpdateBuilder updateBuilder = simpleDao.updateBuilder();
 return updateBuilder;
 }
 
 
 @Override
 public void close() {
  super.close();
  Log.v(TAG, "close call");
  personRuntimeDao=null;
 }
 
 @Override
 public void onCreate(SQLiteDatabase db, ConnectionSource connectionSource) {
  try {
   Log.v(TAG, "onCreate call");
   TableUtils.createTable(connectionSource, Person.class);
  } catch (SQLException e) {
   Log.e(DatabaseHelper.class.getName(), "Can't create database", e);
   throw new RuntimeException(e);
  }
 }
 
 @Override
 public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion) {
  try {
   Log.v(TAG, "onUpgrade call");
   TableUtils.dropTable(connectionSource, Person.class, true);
   onCreate(db, connectionSource);
  } catch (SQLException e) {
   Log.e(DatabaseHelper.class.getName(), "Can't drop databases", e);
   throw new RuntimeException(e);
  }
 }
}

MainActivity.java

package com.sunil.export;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import android.net.Uri;
import android.os.AsyncTask;
import android.os.Bundle;
import android.os.Environment;
import android.R.integer;
import android.R.string;
import android.app.Activity;
import android.app.ProgressDialog;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
import au.com.bytecode.opencsv.CSVReader;
import au.com.bytecode.opencsv.CSVWriter;

public class MainActivity extends Activity implements OnClickListener{

 private static final String TAG="MainActivity";
 private Button btnexport=null;
 private Button btnimport=null;
 private Button btninsertdata=null;
 private Button btnsendmail=null;
 private EditText txtfirtsname;
 private EditText txtlastname;
 private EditText txtaddress;
 private EditText txtto;
 private EditText txtsubj;
 private EditText txttextmsg;
 private EditText txtemail;
 DatabaseHelper dbhelper=null;
  File file=null;
 
 @Override
 protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_main);
  
  Log.v(TAG, "onCreate called");
  txtfirtsname = (EditText)findViewById(R.id.editText_fname);
  txtlastname = (EditText)findViewById(R.id.editText_lname);
  txtaddress = (EditText)findViewById(R.id.editText_address);
  txtemail = (EditText)findViewById(R.id.editText_email);
  txtto = (EditText) findViewById(R.id.editText_to);
  txtsubj = (EditText) findViewById(R.id.editText_subj);
  txttextmsg = (EditText) findViewById(R.id.editText_text);
  
  btnexport=(Button)findViewById(R.id.button_export);
  btnexport.setOnClickListener(this);
  btninsertdata=(Button)findViewById(R.id.button_Insert);
  btninsertdata.setOnClickListener(this);
  btnsendmail=(Button) findViewById(R.id.button_sendmail);
  btnsendmail.setOnClickListener(this);
  btnimport=(Button)findViewById(R.id.button_import);
  btnimport.setOnClickListener(this);
  
     dbhelper=new DatabaseHelper(getApplicationContext());
 }
 @Override
 public void onClick(View arg0) {
 
  Log.v(TAG, "onClick called");
  if(arg0==btnexport){
   
   ExportDatabaseCSVTask task=new ExportDatabaseCSVTask();
   task.execute();
  }
  
  else if(arg0==btnimport){
   

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

         file = new File(exportDir, "PersonCSV.csv");
     try {
    CSVReader reader = new CSVReader(new FileReader(file));
    String [] nextLine;
    try {
     while ((nextLine = reader.readNext()) != null) {
      
         // nextLine[] is an array of values from the line
        
         String fname=nextLine[0];
         String lname=nextLine[1];
         String address=nextLine[2];
         String email=nextLine[3];
         
         if(fname.equalsIgnoreCase("First Name"))
         {
          
         }
         else
         {
          int value=dbhelper.addPersonData(new Person(fname,lname,address,email));
          if(value==1)
          {
           Toast.makeText(getApplicationContext(), "Data inerted into table", Toast.LENGTH_LONG).show();
          }
         }
         
     }
    } catch (IOException e) {
     e.printStackTrace();
    }
    
   } catch (FileNotFoundException e) {
    e.printStackTrace();
   }
  }
  else if (arg0==btninsertdata) {
   
   String firstname=txtfirtsname.getText().toString().trim();
   String lastname=txtlastname.getText().toString().trim();
   String addresss=txtaddress.getText().toString().trim();
   String email=txtemail.getText().toString().trim();
   
   if(firstname.length() < 1)
   {
    Toast.makeText(getApplicationContext(), "Please Enter First name", Toast.LENGTH_LONG).show();
   }
   else if (lastname.length() < 1) {
    Toast.makeText(getApplicationContext(), "Please Enter Last name", Toast.LENGTH_LONG).show();
   }
   else if (addresss.length() < 1) {
    Toast.makeText(getApplicationContext(), "Please Enter Address", Toast.LENGTH_LONG).show();
   }
   else if (email.length() < 1) {
    Toast.makeText(getApplicationContext(), "Please Enter Email", Toast.LENGTH_LONG).show();
   }
   else {
    
    Person person=new Person(firstname, lastname, addresss, email);
    int status=dbhelper.addPersonData(person);
    if(status==1)
    {
     Toast.makeText(getApplicationContext(), "Data inserted successfully.", Toast.LENGTH_LONG).show();
     txtfirtsname.setText("");
     txtlastname.setText("");
     txtaddress.setText("");
     txtemail.setText("");
    } 
   } 
  }
  
  else if (arg0==btnsendmail) {
   
   String to=txtto.getText().toString().trim();
   String subj=txtsubj.getText().toString().trim();
   String msg=txttextmsg.getText().toString().trim();
   
   if(to.length() < 1)
   {
    Toast.makeText(getApplicationContext(), "Please Enter Recipent Email", Toast.LENGTH_LONG).show();
   }
   else if (subj.length() < 1) {
    Toast.makeText(getApplicationContext(), "Please Enter Subject", Toast.LENGTH_LONG).show();
   }
   else if (msg.length() < 1) {
    Toast.makeText(getApplicationContext(), "Please Enter Message", Toast.LENGTH_LONG).show();
   }
   else {
    
    Intent emailIntent = new Intent(android.content.Intent.ACTION_SEND); 
       emailIntent.setType("image/jpeg");
       emailIntent.putExtra(android.content.Intent.EXTRA_EMAIL, new String[]{to}); 
       emailIntent.putExtra(android.content.Intent.EXTRA_SUBJECT, subj); 
       emailIntent.putExtra(android.content.Intent.EXTRA_TEXT, msg); 
       emailIntent.putExtra(Intent.EXTRA_STREAM, Uri.parse(file.getAbsolutePath()));
       startActivity(Intent.createChooser(emailIntent, "Send mail..."));
   } 
  }
 }
 
 private class ExportDatabaseCSVTask extends AsyncTask{
     private final ProgressDialog dialog = new ProgressDialog(MainActivity.this);
     
     @Override
     protected void onPreExecute() {

         this.dialog.setMessage("Exporting database...");
         this.dialog.show();

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

       File dbFile=getDatabasePath("person.db");
       Log.v(TAG, "Db path is: "+dbFile);  //get the path of db
   
         File exportDir = new File(Environment.getExternalStorageDirectory(), "");
         if (!exportDir.exists()) {
             exportDir.mkdirs();
         }

         file = new File(exportDir, "PersonCSV.csv");
         try {
          
             file.createNewFile();
             CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
            
             //ormlite core method      
             List listdata=dbhelper.GetDataPerson();
             Person person=null;
             
             // this is the Column of the table and same for Header of CSV file
             String arrStr1[] ={"First Name", "Last Name", "Address", "Email"};
                csvWrite.writeNext(arrStr1);
                
             if(listdata.size() > 1)
             {
              for(int index=0; index < listdata.size(); index++)
              {
                person=listdata.get(index);
                String arrStr[] ={person.getFirtname(), person.getLastname(), person.getAddress(), person.getEmail()};
                csvWrite.writeNext(arrStr);
              }
             }
            
             csvWrite.close();
             return true;
         }
         catch (IOException e){
             Log.e("MainActivity", e.getMessage(), e);
             return false;
         }
     }
     
     @Override
     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();
         }
         else {
             Toast.makeText(MainActivity.this, "Export failed!", Toast.LENGTH_SHORT).show();
         }
     }
 }
}

Manifest.xml

You can download the source code from here ExportSqliteToCSV