Android Insert to sqlite database from .csv

Question

I'm in a bit of trouble with a sample program that I am writing.

Here is my code:

public void prePopulateDatabase(SQLiteDatabase db, Context helperContext){
    String fileIn = "";
    String[] file;
    ContentValues row = new ContentValues();
    AssetManager am;
    InputStream is = null;
    am = helperContext.getResources().getAssets();

    try {
        is = am.open("Matrix.csv");
        BufferedReader br = new BufferedReader(new InputStreamReader(is));
        while((fileIn = br.readLine()) != null){
            file = fileIn.split(",");
            row.put(KEYWORD, file[0]);
            row.put(DURATION, file[1]);
            row.put(AMOUNT, file[2]);
            row.put(DESCRIPTION, file[3]);
            row.put(LEVEL_NAME, file[4]);
            row.put(CHOICE_FOR_TWO_1, file[5]);
            row.put(CHOICE_FOR_TWO_2, file[6]);
            row.put(CHOICE_FOR_FIVE_1, file[7]);
            row.put(CHOICE_FOR_FIVE_2, file[8]);
            row.put(CHOICE_FOR_FIVE_3, file[9]);
            row.put(CHOICE_FOR_FIVE_4, file[10]);
            row.put(CHOICE_FOR_FIVE_5, file[11]);
            insertToDBViaContentValues(db, row);
        }//end while
    } catch (Exception e) {
            e.printStackTrace();
    }//end try catch
}//end fxn

public void insertToDBViaContentValues(SQLiteDatabase db, ContentValues row){
    procedure("SiteMatrixMultiLevels: INSERT TO DB > "+row.toString());
        db.insert(TABLE_SITES, null, row);
}

public void validate(SQLiteDatabase db){
    Cursor c = db.rawQuery("select * from "+TABLE_SITES, null);
    if(c != null && c.moveToFirst()){
        while(!c.isAfterLast()){
            data("VALUE: " + c.getString(0) + "," + c.getString(1) + "," + c.getString(2) + "," + c.getString(3) + "," + c.getString(4) + "," + c.getString(5)
                    + "," + c.getString(6) + "," + c.getString(7) + "," + c.getString(8) + "," + c.getString(9) + "," + c.getString(10)
                     + "," + c.getString(11) + "," + c.getString(12));
            c.moveToNext();
        }
    }
}

and I am getting an error of

02-03 20:24:04.537: W/System.err(16437): java.lang.ArrayIndexOutOfBoundsException: length=7; index=7

at

row.put(CHOICE_FOR_FIVE_1, file[7]);

The csv file that I am fetching files from has multiple lines but only follows 2 formats. The first 100 are of this format

pkey,keyword,duration,amount,description,A,B,,,,,

while another 100 follow this format

pkey,keyword,duration,amount,description,,,C,D,E,F,G

I am not sure if im getting the error because the first format type only stops at 7 (since no value for C,D,E,F,G is specified.)

Any help would be appreciated. Thanks!

hi Dhawal thanks for your response. Are you suggesting that if the length is 0, i manually add? I get all my data from the csv using row.put and as much as possible, I want to keep it that way. Without using if statements to determine if a column entry from the csv has no value. — momoja
– momoja, Commented Feb 3, 2014 at 12:41
@user1481694 a rule of thumb is to check if the index exist before indexing into it. — James
– James, Commented Feb 3, 2014 at 12:46
use CSV reader class like this question its easy for you stackoverflow.com/q/9663410/1168654 — Dhaval Parmar
– Dhaval Parmar, Commented Feb 3, 2014 at 12:51
hi all. I'm a bit weirded out by this but, I went to the .csv file. replaced all ",,," with ", , ," (to add a blank space as a value) and it worked. funny thing, I removed all the spaces I added and now I am able to recreate it. (I deleted all my local data first before recompiling). I am going to leave this as open though, because in the future, when I might need to replace my csv, I wouldnt want to do this dumb quick fix again. Thanks to all who answered. — momoja
– momoja, Commented Feb 3, 2014 at 12:57

umair.ali · Accepted Answer · 2014-02-03 13:11:59Z

1

There is no issue with you data. Here is what you need to for in your split() call...

file = fileIn.split(",", -1);

Thats all...

Actually you need to consult the documentation of split(String regex), as it says,

This method works as if by invoking the two-argument split method with the given expression and a limit argument of zero. Trailing empty strings are therefore not included in the resulting array.

Means you need to define second argument (limit) in it otherwise it will consider it as zero '0' and empty entries will be removed. So pass -1 to get all the empty entries as well.

Hope this helps...

edited Feb 3, 2014 at 13:11

answered Feb 3, 2014 at 13:06

umair.ali

2,7142 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tugrul · Accepted Answer · 2014-02-03 13:33:49Z

0

Simple implementation. Try to use StringTokenizer.

private void readAndInsert() throws UnsupportedEncodingException {


ArrayList<SimpleQuestion> questions = new ArrayList<SimpleQuestion>();
AssetManager assetManager = getAssets();
InputStream is = null;

            try {
                is = assetManager.open("questions/question_bank.csv");
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

            BufferedReader reader = null;
            reader = new BufferedReader(new InputStreamReader(is, Charset.forName("UTF-8")));

            String line = "";
            StringTokenizer st = null;
            try {

                while ((line = reader.readLine()) != null) {
                    st = new StringTokenizer(line, ",");
                    SimpleQuestion sQuestion= new SimpleQuestion ();
                                    //your attributes
                    sQuestion.setX(st.nextToken());
                    sQuestion.setY(st.nextToken());
                    sQuestion.setZ(st.nextToken());
                    sQuestion.setW(st.nextToken());
                    questions .add(sQuestion);

                }
            } catch (IOException e) {

                e.printStackTrace();
            }

            yourDB.bulkInsert(questions );

}

answered Feb 3, 2014 at 13:33

Tugrul

1,8084 gold badges27 silver badges40 bronze badges

2 Comments

momoja Over a year ago

hi, thanks for this answer as well. however, i feel that using split over st is the way to go, since i would not want to lengthen the code even more. :)

Tugrul Over a year ago

Thanks, too. Ok go on ;)

Collectives™ on Stack Overflow

Android Insert to sqlite database from .csv

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related