Reading CSV then Insert Data to SQLITE

Question

I have a CSV file located in my assets folder. I need my program to get the CSV file and then insert it's data to a table in SQLite.

Here's my current code

FileReader file;
                        file = new FileReader("com.xylol.o_cha/assets/questions/question_bank.csv");

                         BufferedReader buffer = new BufferedReader(file);

                         String line = "";
                         String tableName ="O_CHA_QUESTIONS";
                         String columns = "QUESTION_ID, CATEGORY, ANIME TITLE, QUESTION_DESC, CHOICE1, CHOICE2, CHOICE3, CHOICE4, CORRECT_ANSWER, ANSWERED";
                         String str1 = "INSERT INTO " + tableName + " (" + columns + ") values(";
                         String str2 = ");";

                         database.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);
                             database.execSQL(sb.toString());
                         }
                         database.setTransactionSuccessful();
                         database.endTransaction();

Here's the problem

09-23 05:47:29.436: W/System.err(7020): java.io.FileNotFoundException: /com.xylol.o_cha/assets/questions/question_bank.csv (No such file or directory)
09-23 05:47:29.465: W/System.err(7020):     at org.apache.harmony.luni.platform.OSFileSystem.open(Native Method)
09-23 05:47:29.465: W/System.err(7020):     at dalvik.system.BlockGuard$WrappedFileSystem.open(BlockGuard.java:239)
09-23 05:47:29.473: W/System.err(7020):     at java.io.FileInputStream.<init>(FileInputStream.java:88)
09-23 05:47:29.473: W/System.err(7020):     at java.io.FileInputStream.<init>(FileInputStream.java:122)
09-23 05:47:29.473: W/System.err(7020):     at java.io.FileReader.<init>(FileReader.java:66)
09-23 05:47:29.483: W/System.err(7020):     at com.xylol.o_cha.Splash_Screen$1.run(Splash_Screen.java:73)
09-23 05:47:29.483: W/System.err(7020):     at android.os.Handler.handleCallback(Handler.java:587)
09-23 05:47:29.493: W/System.err(7020):     at android.os.Handler.dispatchMessage(Handler.java:92)
09-23 05:47:29.493: W/System.err(7020):     at android.os.Looper.loop(Looper.java:132)
09-23 05:47:29.493: W/System.err(7020):     at android.app.ActivityThread.main(ActivityThread.java:4025)
09-23 05:47:29.493: W/System.err(7020):     at java.lang.reflect.Method.invokeNative(Native Method)
09-23 05:47:29.493: W/System.err(7020):     at java.lang.reflect.Method.invoke(Method.java:491)
09-23 05:47:29.493: W/System.err(7020):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:841)
09-23 05:47:29.503: W/System.err(7020):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:599)
09-23 05:47:29.526: W/System.err(7020):     at dalvik.system.NativeStart.main(Native Method)

Answer 1

To reader csv, CSVReader is better to use than the FileReader. find latest jar for open csv this link

how to access file from asset?

CSVReader reader = new CSVReader(new InputStreamReader(getAssets().open("questions/question_bank.csv")));

try like this use CSV reader so you can have string array for a individual row

reader = new CSVReader(new FileReader(Environment.getExternalStorageDirectory()+"/folder/CSV/"+filename+".csv"));
                    try 
                    {
                        List<String[]> myEntries = reader.readAll();
                        Log.d("size","size of LIST Quote======>"+myEntries.size());
                        if(myEntries.size()>0)
                        {
                            for(int i=0;i<myEntries.size();i++)
                            {
                                String arr[]=myEntries.get(i);
                                list11=new ArrayList<String>();
                                for(int k=0;k<arr.length;k++)
                                {
                                    list11.add(arr[k]);
                                    //Log.d("log is", "data size is==>"+arr[k]);
                                }
                                    dba.insetdata_New_Qoute(list11, "tblNewQute");  
                            }
                        }
                    }
                    catch (Exception e) 
                    {
                        e.printStackTrace();
                    }

Answer 2

Firstly; What is the problem in your code ? You need SQLiteHelper class and transaction methods to create an flex code design.

Some advices;

• Create an object for holding one row data into the csv. ( Ex: SimpleQuestion . It provides to you manage the data easily.)
• Read file row by row and assign to object. Add the object to list. (Ex: List<SimpleQuestion>)
• Bulk insert the list to the database table.

Code:

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

}