How to insert CSV file into SQLite database on android directly

Question

I am downloading a csv from server.And i have to insert its data into sqlite database table (a single table ).I got the file file from server and save it on sdcard . Here is my code of reading Csv file

public synchronized void insertAllDataToDB(String path) {
    FileReader file = null;
    try {
        file = new FileReader(path);
    }  catch (IOException e) {
        e.printStackTrace();
    }
    BufferedReader buffer = new BufferedReader(file);
    String line = "";
    sqLite.beginTransaction();
    try {
        while((line = buffer.readLine()) != null) {
            String[] str = line.split(",");
            ContentValues values=new ContentValues(4);
            if(str.length!=8){
                continue;
            }
            values.put(col1,str[0]);
            values.put(col2,str[1]);
            values.put(col3,str[2]);
            values.put(col4,str[3]);
            values.put(col5,str[4]);
            values.put(col6,str[5]);
            values.put(col7,str[6]);
            sqLite.insert(TABLE_NAME,null, values);
            Log.e("Inserted",values.toString());
        }
    } catch (SQLException | IOException e) {
        e.printStackTrace();
        Log.e("Exception",e.getLocalizedMessage());
    }
    finally{

    }
    sqLite.setTransactionSuccessful();
    sqLite.endTransaction();
}

It work perfectly but the problem is my Csv file data has comma as itself in its column data and when i am splitting it from comma it also split the data .. Can anyone tell me how to get rid of that problem .Or anyone have another way to achieve this . thanks in advance .. i am stuck

Is the column data with the comma encapsulated with quotation marks? — Biber, Jul 31 '15 at 06:03
I think in this post you will find the answer: http://stackoverflow.com/questions/1189416/c-regular-expressions-how-to-parse-comma-separated-values-where-some-values — Biber, Jul 31 '15 at 06:05
You should enclose the while loop in a try...catch...finally block. This is the correct use of transactions: finalize only on success, rollback on fail and finally close it. Otherwise, it's perfectly useless. Therefore, move this `sqLite.setTransactionSuccessful();` just after the loop and this `sqLite.endTransaction();` inside the `finally{}` block. For the comma problem, you can use a different separator, such as **TAB** or **|** to split your columns. — Phantômaxx, Jul 31 '15 at 07:00

score 2 · Answer 1 · answered Jul 31 '15 at 06:03

This is how I do it for loading the states of the US into my app:

Scanner scanner = new Scanner(mContext.getResources().openRawResource(R.raw.states_csv));

db.beginTransaction();
String line;
while(scanner.hasNextLine() && ((line = scanner.nextLine()) != null)) {
    String[] values = line.split(",");
    if(values.length != 4)
        continue;

    for(int i = 0; i < values.length; i++)
        values[i] = values[i].replace("\"", "");

    ContentValues contentValues = new ContentValues();
    contentValues.put(States.NAME, values[2]);
    contentValues.put(States.CODE, values[1]);
    contentValues.put(States.COUNTRY_CODE, values[3]);

    db.insert(States.TABLE_NAME, null, contentValues);
}

db.setTransactionSuccessful();
db.endTransaction();

I place the .csv file into the res/raw folder and it works just fine.

You should enclose the while loop in a `try...catch...finally` block. This is the correct use of transactions: finalize only on success, rollback on fail and finally close it. Otherwise, it's perfectly useless. — Phantômaxx, Jul 31 '15 at 06:58
Thanks buddy i did it .. but you are using the same method like i did as per my question . I want to a parser library that will do automatically for me . anyway thanks for your answer — ADM, Aug 07 '15 at 12:57

How to insert CSV file into SQLite database on android directly

1 Answers1