Import .csv file to Sqlite in Android

Question

I have a .csv file and I want to import this file putting into my database in android.

Example, the .csv file:

| id | name  | dt1 | dt2 | dt3 | 
| 1  | Paul  | 21  | 98  | 91  |
| 2  | John  | 22  | 44  | 11  |
| 3  | George| 21  | 31  | 22  |
| 4  | Ringo | 20  | 10  | 15  | 

To a thing like that:

Cursor cursor = getWritableDatabase().
rawQuery("Insert into table1(_id, name, dt1, dt2, dt3)
values ( 1, Paul, 21, 98, 91)
values ( 2, John, 22, 44, 11)
values ( 3, George, 21,31,22)
values ( 4, Ringo, 20, 10,15);");

So please, how can I do that?

Answer 1

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

Answer 2

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 from public 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));
...