Android - Importing Multiple .csv Files To Multiple Tables In Sqlite Database

I have in my Android device a folder with multiple .CSV files. I want to import all of them to my SQLite Database, but each file must be a different table. All .CSV file are simple

Solution 1:

I refactored your code into a bunch of smaller methods. Each method is responsible for one thing, which is good practice, try to do that any time you can.

I only did a few changes:

• The line creating the FileReader now uses the file (less error prone)
• Changed the way you insert by creating a single insert query so you have less database accesses (ie like this: INSERT INTO table(codigo) VALUES ('XXXX'), ('ZZZZ'), ('FFFF'); )
• Changed the text in the Toasts to identify better where your error comes from.

Try it out and see if you can find your error better. (I did not try to compile the code so you might have to tweak it a little but should be fine overall)

main import method:

publicvoidimportaTabelas() {

    //Check the read permissionif (checkSelfPermission(Manifest.permission.READ_EXTERNAL_STORAGE) == PackageManager.PERMISSION_GRANTED) {
        try {
            makeDirs();

            //Read all file namesfor (File f : importDir.listFiles()) {
                if (f.isFile()) {
                    importFile(f);
                }
            }
        } catch (IOException e) {
            Toast.makeText(this, "Could not import tables! " + e.getMessage(), Toast.LENGTH_SHORT).show();
            e.printStackTrace();
        }
    } else {
        requestPermissions(newString[]{Manifest.permission.READ_EXTERNAL_STORAGE}, 1);
    }

}

Make the directories

privatevoidmakeDirs() {
    //Check if the folder exists
    File importDir = new File (Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS) + "/ENEL/IMPORTADOS/");
    if (!importDir.exists()) {
        importDir.mkdirs();
    }
}

Import a single file

privatevoidimportFile(File f) {
    try {
        SQLiteDatabasedb=this.banco.getWritableDatabase();

        //Put the files names into variable nomeArqStringnomeArq= f.getName();

        //Take off the file extension .csvif (nomeArq.indexOf(".") > 0)
            nomeArq = nomeArq.substring(0, nomeArq.lastIndexOf("."));

        createTable(db, nomeArq);

        StringinsertQuery= buildImportQuery(f, nomeArq);
        db.execSQL(insertQuery);
    } catch (SQLException e) {
        Toast.makeText(this, "Could not import file. " + e.getMessage(), Toast.LENGTH_SHORT).show();
        e.printStackTrace();
    }
}

Builds the insert query for a specific file

privateStringbuildImportQuery(File f, String nomeArq) {
    StringBuilder sb = newStringBuilder();
    try {
        //Reads the fileFileReader fileReader = newFileReader(f);
        BufferedReader buffer = newBufferedReader(fileReader);
        String line;

        sb.append("INSERT INTO " + nomeArq +  " (codigo) VALUES ");
        boolean addComma = false;
        while ((line = buffer.readLine()) != null) {
            if(line.length() > 0) {
                if(addComma) {
                    sb.append(",");
                }
                sb.append("('" + line.trim() + "')");
                addComma = true;
            }
        }
        sb.append(";");
    } catch (IOException e) {
        Toast.makeText(this, "Could not write query. " + e.getMessage(), Toast.LENGTH_SHORT).show();
        e.printStackTrace();
    }

    return sb.toString();

}

Creates a single table

privatevoidcreateTable(SQLiteDatabase db, String tableName) {
    try {
        //Create table with the name of the .csv fileString criaTab = "CREATE TABLE IF NOT EXISTS " + tableName +  " (id integer PRIMARY KEY AUTOINCREMENT, codigo varchar (50))";
        db.execSQL(criaTab);
        db.close();

    } catch (Exception e) {
        Toast.makeText(this, "Could not create table " + tableName + "." +  e.getMessage(), Toast.LENGTH_SHORT).show();
        e.printStackTrace();
    }
}