Import .csv file to Sqlite in Android

Try following code,

FileReader file = new FileReader(fileName);BufferedReader buffer = new BufferedReader(file);String line = "";String tableName ="TABLE_NAME";String columns = "_id, name, dt1, dt2, dt3";String str1 = "INSERT INTO " + tableName + " (" + columns + ") values(";String str2 = ");";db.beginTransaction();while ((line = buffer.readLine()) != null) {    StringBuilder sb = new StringBuilder(str1);    String[] str = line.split(",");    sb.append("'" + str[0] + "',");    sb.append(str[1] + "',");    sb.append(str[2] + "',");    sb.append(str[3] + "'");    sb.append(str[4] + "'");    sb.append(str2);    db.execSQL(sb.toString());}db.setTransactionSuccessful();db.endTransaction();

I suggest using ContentValues to insert records. It is easier, will escape quotes, and is less verbose.

Like this, (it will also skip bad lines):

 ... BufferedReader buffer = new BufferedReader(new InputStreamReader(inStream)); String line = ""; db.beginTransaction();        try {            while ((line = buffer.readLine()) != null) {                String[] colums = line.split(",");                if (colums.length != 4) {                    Log.d("CSVParser", "Skipping Bad CSV Row");                    continue;                }                ContentValues cv = new ContentValues(3);                cv.put(dbCol0, colums[0].trim());                cv.put(dbCol1, colums[1].trim());                cv.put(dbCol2, colums[2].trim());                cv.put(dbCol3, colums[3].trim());                cv.put(dbCol4, colums[4].trim());                db.insert(TABLE, null, cv);            }        } catch (IOException e) {            e.printStackTrace();        }   db.setTransactionSuccessful();   db.endTransaction();

dbCol0 etc are the column-names of the database, e.g. _id, name, dt1, dt2, dt3 .

db is a database you get frompublic SQLiteDatabase getReadableDatabase () in a SQLiteOpenHelper

How to read the CSV file

Copy file.csv into the assets folder, and read it like this:

String mCSVfile = "file.csv";AssetManager manager = context.getAssets();InputStream inStream = null;try {     inStream = manager.open(mCSVfile);    } catch (IOException e) {     e.printStackTrace();    } BufferedReader buffer = new BufferedReader(new InputStreamReader(inStream));...

I have added some classes using these classes you can easily import and export your file or db

for these operation you must have to import these two jar file into your project

(1) opencsv-1.7.jar http://www.java2s.com/Code/Jar/o/Downloadopencsv17jar.htm
(2) poi-3.8-20120326.jarhttp://code.google.com/p/mipgsmca12-108-126--online-library/downloads/detail?name=poi-3.8-20120326.jar&can=2&q=

and than import all these four classes, and use based on requirement ...

ExportFileModule `

public class ExportDatabaseToCSV extends AsyncTask<Void, Boolean, Boolean>{Context context;ProgressDialog dialog;public ExportDatabaseToCSV(Context context){    this.context=context;   }@Overrideprotected void onPreExecute() {    dialog=new ProgressDialog(context);    dialog.setTitle("Exporting SecureIt Data to CSV file");    dialog.setMessage("Please wait...");    dialog.setCancelable(false);    dialog.setIcon(android.R.drawable.ic_dialog_info);    dialog.show();}@Overrideprotected Boolean doInBackground(Void... params) {    CredentialDb db = new CredentialDb(context);//here CredentialDb is my database. you can create your db object.       File exportDir = new File(Environment.getExternalStorageDirectory(), "");               if (!exportDir.exists())           {              exportDir.mkdirs();          }      File file = new File(exportDir, "SecureItExportedFile.csv");      try       {          file.createNewFile();                          CSVWriter csvWrite = new CSVWriter(new FileWriter(file));          SQLiteDatabase sql_db = db.getReadableDatabase();//returning sql          Cursor curCSV = sql_db.rawQuery("SELECT * FROM "+CredentialDb.TABLE_NAME,null);          csvWrite.writeNext(curCSV.getColumnNames());          while(curCSV.moveToNext())              {                 //Which column you want to export you can add over here...                  String arrStr[] ={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();          return true;      }      catch(Exception sqlEx)      {          Log.e("Error:", sqlEx.getMessage(), sqlEx);      }      return false;}   @Overrideprotected void onPostExecute(Boolean result) {    if (dialog.isShowing())    {        dialog.dismiss();    }    if(result)        Toast.makeText(context, "SqLite Data has been Exported!", Toast.LENGTH_LONG).show();    else        Toast.makeText(context, "SqLite Data has not Exported", Toast.LENGTH_LONG).show();  }}

ImportModule

import java.io.File;import android.app.Activity;import android.content.Context;import android.os.Environment;public class MyCSVFileReader {public static void openDialogToReadCSV(final Activity activity,final Context context){    File mPath = new File(Environment.getExternalStorageDirectory() + "//DIR//");    FileDialog fileDialog = new FileDialog(activity, mPath);    fileDialog.setFileEndsWith(".txt");    fileDialog.addFileListener(new FileDialog.FileSelectedListener() {        @Override        public void fileSelected(File file) {            new ImportCVSToSQLiteDataBase(context,activity,file).execute(); //execute asyncTask to import data into database from selected file.                    }    });    fileDialog.showDialog();}}

import java.io.File;import java.io.FilenameFilter;import java.util.ArrayList;import java.util.List;import android.app.Activity;import android.app.AlertDialog;import android.app.Dialog;import android.content.DialogInterface;import android.content.DialogInterface.OnClickListener;import android.os.Environment;import android.util.Log;import com.export.importmodule.ListenerList.FireHandler;public class FileDialog {    private static final String PARENT_DIR = "..";    private final String TAG = getClass().getName();    private String[] fileList;    private File currentPath;public interface FileSelectedListener {    void fileSelected(File file);}public interface DirectorySelectedListener {    void directorySelected(File directory);}private ListenerList<FileSelectedListener> fileListenerList = new ListenerList<FileDialog.FileSelectedListener>();private ListenerList<DirectorySelectedListener> dirListenerList = new ListenerList<FileDialog.DirectorySelectedListener>();private final Activity activity;private boolean selectDirectoryOption;private String fileEndsWith;    /** * @param activity  * @param initialPath */public FileDialog(Activity activity, File path) {    this.activity = activity;    if (!path.exists()) path = Environment.getExternalStorageDirectory();    loadFileList(path);}/** * @return file dialog */public Dialog createFileDialog() {    Dialog dialog = null;    AlertDialog.Builder builder = new AlertDialog.Builder(activity);    builder.setTitle(currentPath.getPath());    if (selectDirectoryOption) {        builder.setPositiveButton("Select directory", new OnClickListener() {            public void onClick(DialogInterface dialog, int which) {                Log.d(TAG, currentPath.getPath());                fireDirectorySelectedEvent(currentPath);            }        });    }    builder.setItems(fileList, new DialogInterface.OnClickListener() {        public void onClick(DialogInterface dialog, int which) {            String fileChosen = fileList[which];            File chosenFile = getChosenFile(fileChosen);            if (chosenFile.isDirectory()) {                loadFileList(chosenFile);                dialog.cancel();                dialog.dismiss();                showDialog();            } else fireFileSelectedEvent(chosenFile);        }    });    dialog = builder.show();    return dialog;}public void addFileListener(FileSelectedListener listener) {    fileListenerList.add(listener);}public void removeFileListener(FileSelectedListener listener) {    fileListenerList.remove(listener);}public void setSelectDirectoryOption(boolean selectDirectoryOption) {    this.selectDirectoryOption = selectDirectoryOption;}public void addDirectoryListener(DirectorySelectedListener listener) {    dirListenerList.add(listener);}public void removeDirectoryListener(DirectorySelectedListener listener) {    dirListenerList.remove(listener);}/** * Show file dialog */public void showDialog() {    createFileDialog().show();}private void fireFileSelectedEvent(final File file) {    fileListenerList.fireEvent(new FireHandler<FileDialog.FileSelectedListener>() {        public void fireEvent(FileSelectedListener listener) {            listener.fileSelected(file);        }    });}private void fireDirectorySelectedEvent(final File directory) {    dirListenerList.fireEvent(new FireHandler<FileDialog.DirectorySelectedListener>() {        public void fireEvent(DirectorySelectedListener listener) {            listener.directorySelected(directory);        }    });}private void loadFileList(File path) {    this.currentPath = path;    List<String> r = new ArrayList<String>();    if (path.exists()) {        if (path.getParentFile() != null) r.add(PARENT_DIR);        FilenameFilter filter = new FilenameFilter() {            public boolean accept(File dir, String filename) {                File sel = new File(dir, filename);                if (!sel.canRead()) return false;                if (selectDirectoryOption) return sel.isDirectory();                else {                    boolean endsWith = fileEndsWith != null ? filename.toLowerCase().endsWith(fileEndsWith) : true;                    return endsWith || sel.isDirectory();                }            }        };        String[] fileList1 = path.list(filter);        for (String file : fileList1) {            r.add(file);        }    }    fileList = (String[]) r.toArray(new String[]{});}private File getChosenFile(String fileChosen) {    if (fileChosen.equals(PARENT_DIR)) return currentPath.getParentFile();    else return new File(currentPath, fileChosen);   }//--------------------------------------------------------------    public void setFileEndsWith(String fileEndsWith) {        this.fileEndsWith = fileEndsWith != null ? fileEndsWith.toLowerCase() : fileEndsWith;        }     }class ListenerList<L> {private List<L> listenerList = new ArrayList<L>();public interface FireHandler<L> {    void fireEvent(L listener);}public void add(L listener) {    listenerList.add(listener);}public void fireEvent(FireHandler<L> fireHandler) {    List<L> copy = new ArrayList<L>(listenerList);    for (L l : copy) {        fireHandler.fireEvent(l);    }}public void remove(L listener) {    listenerList.remove(listener);}public List<L> getListenerList() {    return listenerList;}}

import java.io.File;import java.io.FileReader;import android.app.Activity;import android.app.ProgressDialog;import android.content.Context;import android.os.AsyncTask;import android.util.Log;import android.widget.Toast;import au.com.bytecode.opencsv.CSVReader;public class ImportCVSToSQLiteDataBase extends AsyncTask<String, String, String> {    Activity activity;    Context context;    File file=null;    private ProgressDialog dialog;    public ImportCVSToSQLiteDataBase(Context context, Activity activity,File file) {        this.context=context;        this.activity=activity;        this.file=file;    }    @Override    protected void onPreExecute()    {        dialog=new ProgressDialog(context);        dialog.setTitle("Importing Data into SecureIt DataBase");        dialog.setMessage("Please wait...");        dialog.setCancelable(false);        dialog.setIcon(android.R.drawable.ic_dialog_info);        dialog.show();    }@Overrideprotected String doInBackground(String... params) {            String data="";            Log.d(getClass().getName(), file.toString());           try{                 CSVReader reader = new CSVReader(new FileReader(file));                   String [] nextLine;                  //here I am just displaying the CSV file contents, and you can store your file content into db from while loop...                    while ((nextLine = reader.readNext()) != null) {                        // nextLine[] is an array of values from the line                        String accId=nextLine[0];                        String acc_name=nextLine[1];                        data=data+"AccId:"+accId  +"  Account_name:"+acc_name+"\n";                      }                   return data;            } catch (Exception e) {                Log.e("Error", "Error for importing file");            }        return data="";  }protected void onPostExecute(String data)  {    if (dialog.isShowing())    {        dialog.dismiss();    }    if (data.length()!=0)    {        Toast.makeText(context, "File is built Successfully!"+"\n"+data, Toast.LENGTH_LONG).show();    }else{            Toast.makeText(context, "File fail to build", Toast.LENGTH_SHORT).show();         }   }}

Steps to use ...
(1) For Export SqLiteDb:
     new ExportDatabaseToCSV(YourActivityClass.this).execute();
(2) For Import SqLiteDb from your selected file:
    MyCSVFileReader.openDialogToReadCSV(this, YourActivityClass.this);

(3) And Don't forget to add these permission in AndroidManifest.xml

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