Android App: User selects a CSV file through an Intent.ACTION_GET_CONTENT. How can I then import this CSV file into a sqlite database table?

Question

I'm working on building the ability to import data via CSV file into a SQLite database table within an Android app UI.

There's a button with an OnClickListener that allows the user to select the file they want to upload. After the file is selected, they are returned to the "Import Accounts" page that also has an "Import CSV" button at the bottom of the page.

ImportAccountActivity java code for the OnClickListener:

btn_choose_file.setOnClickListener(new Button.OnClickListener(){
            @Override
            public void onClick(View arg0) {

                Intent intent = new Intent(Intent.ACTION_GET_CONTENT);
                intent.setType("*/*");
                startActivityForResult(intent,PICKFILE_RESULT_CODE);

            }});

@Override
    protected void onActivityResult(int requestCode, int resultCode, Intent data) {
        super.onActivityResult(requestCode, resultCode, data);
        switch (requestCode) {
            case PICKFILE_RESULT_CODE:
                if (resultCode == RESULT_OK) {
                    FilePath = data.getData().getPath();
                    Uri returnUri = data.getData();


                    returnCursor = getContentResolver().query(returnUri, null, 
                            null, null, null);



                    /*
                     * Get the column indexes of the data in the Cursor,
                     * move to the first row in the Cursor, get the data,
                     * and display it.
                     */
                    nameIndex = returnCursor.getColumnIndex(OpenableColumns.DISPLAY_NAME);
                    returnCursor.moveToFirst();


                    textFile.setText(returnCursor.getString(nameIndex) + " has been selected!");


                }
                break;
   }}


// Import CSV button OnClickListener
        btn_csv_import.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                // Access Database to be writable
                SQLiteOpenHelper database = new AccountDbHelper(getApplicationContext());
                SQLiteDatabase db = database.getWritableDatabase();


                FileReader file = new FileReader(fileName);
                BufferedReader buffer = new BufferedReader(file);
                String line = "";
                String tableName = AccountLogin.AccountEntry.TABLE_NAME;
                String columns = "_id, account_title, user_name, account_password, account_notes";
                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 want the user to then be able to press the "Import CSV" button and have the data inserted into the SQLite database table. However - I'm having issues with this part, specifically what I need to have inside the btn_csv_import.setOnClickListener. I tried viewing this StackOverflow question, however I'm not quite sure how to get the line fileName correctly for the line that says FileReader file = new FileReader(fileName);

Or - if anyone has a better way to import the CSV file into the SQLite Database, then I'd love some assistance on this, thank you!!

Let me know if there's any other details that I can add to help make this more clear.

You should open an InputStream for the obtained uri. And then use the buffered reader on that input stream. Forget about filepaths. — blackapps, May 16 '20 at 07:17
Transfer `returnUri.toString()` to the next activity. And use it there as the uri as said. — blackapps, May 16 '20 at 07:21

score 0 · Accepted Answer · answered May 27 '20 at 04:00

Going to go ahead and close this question. After some research, I was able to solve the issue and went a slightly different route to import the data via CSV into a specific sqlite database table

Here's what I ended up placing inside the btn_csv_import.setOnClickListener:

// CSV Import button to get data from CSV file into database table
        btn_csv_import.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                // Access Database to be writable
                SQLiteOpenHelper database = new AccountDbHelper(getApplicationContext());
                SQLiteDatabase db = database.getWritableDatabase();


                InputStream inStream = null;
                try {
                    inStream = getContentResolver().openInputStream(returnUri);
                } catch (FileNotFoundException e) {
                    e.printStackTrace();
                    Log.e("ImportAccountActivity", "INPUT STREAM WAS NOT OPENED");
                }
                BufferedReader buffer = new BufferedReader(new InputStreamReader(inStream));
                String line = "";
                db.beginTransaction();
                // Have file skip first row with header columns
                boolean skip = true;
                try {
                    while ((line = buffer.readLine()) != null) {
                        // File skips first row and sets variable to false, so that all other
                        // lines get imported into DB
                        if(skip) {
                            skip = false; // Skip only the first line
                            continue; }



                        String[] colums = line.split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)", -1);
                        if (colums.length != 5) {
                            Log.e("CSVParser", "Skipping Bad CSV Row");
                            Log.e("CSVParser", "Skipping:" + colums[4].trim());
                            Log.e("CSVParser", "Columns length: " + colums.length);
                            continue;
                        }




                        ContentValues cv = new ContentValues();
                        // ADDED replace for parsing tests
                        // cv.put("_id", colums[0].trim());
                        cv.put("account_title", colums[1].trim().replace('`',','));
                        cv.put("user_name", colums[2].trim().replace('`',','));
                        cv.put("account_password", colums[3].trim().replace('`',','));
                        cv.put("account_notes", colums[4].trim().replace('`',','));
                        db.insert(AccountLogin.AccountEntry.TABLE_NAME, null, cv);
                        }
                    } catch (IOException e) {
                        e.printStackTrace();
                        Log.e("ImportAccountActivity", "DATABASE didn't have a chance to parse");
                    }
                    db.setTransactionSuccessful();
                    db.endTransaction();

                    Toast.makeText(ImportAccountActivity.this, R.string.toast_csv_import_sucessful,
                            Toast.LENGTH_LONG).show();

                // Open intent to go back to account_main.xml
                Intent intent = new Intent(ImportAccountActivity.this, AccountActivity.class);
                startActivity(intent);

Hopefully this is helps someone out there!!

Android App: User selects a CSV file through an Intent.ACTION_GET_CONTENT. How can I then import this CSV file into a sqlite database table?

1 Answers1