Android-将多个.CSV文件导入SQLite数据库中的多个表 [英] Android - Importing multiple .CSV Files to multiple tables in SQLite database

问题描述

我在Android设备中有一个包含多个.CSV文件的文件夹。

I have in my Android device a folder with multiple .CSV files.

我想将所有这些文件都导入到我的SQLite数据库中，但是每个文件必须是一个

I want to import all of them to my SQLite Database, but each file must be a different table.

所有.CSV文件都很简单。它们只有一列。

All .CSV file are simple. They have just one column.

示例：

File.CSV

12345

123

00000000

AnotherFile.CSV

XXXXX

ZZZZZZZZZZ

FFFF

这是我的方法，它不起作用。我不明白为什么：

Here is my method and it is not working. I could not understand why:

@TargetApi(Build.VERSION_CODES.M)
public void importaTabelas() {

    //Check the read permission
    if (checkSelfPermission(Manifest.permission.READ_EXTERNAL_STORAGE) == PackageManager.PERMISSION_GRANTED) {

        try {

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

            //Read all file names
            for (File f : importDir.listFiles()) {

                if (f.isFile()) {

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

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

                    SQLiteDatabase db = this.banco.getWritableDatabase();

                    try {

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

                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                    //String for the file location
                    String fn = importDir + "/" + nomeArq + ".csv";

                    //Reads the file
                    FileReader fileReader = new FileReader(fn);

                    BufferedReader buffer = new BufferedReader(fileReader);

                    //ContentValues contentValues = new ContentValues();

                    String line = "";

                    //db.beginTransaction();

                    while ((line = buffer.readLine()) != null) {

                        //String[] colums = line.split("\t");

                        //String[] colums = line.split(";");

                        Toast.makeText(this, line, Toast.LENGTH_SHORT).show();

                        //contentValues.put("codigo", line);

                        //db.insert(nomeArq, null, contentValues);

                        db.execSQL("INSERT INTO " + nomeArq +  " (codigo) VALUES ('" + line + "')");

                    }

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

                }

            }

        } catch (Exception e) {

            Toast.makeText(this, "Catch!", Toast.LENGTH_SHORT).show();
            e.printStackTrace();

        }

    }

    else {

        requestPermissions(new String[]{Manifest.permission.READ_EXTERNAL_STORAGE}, 1);

    }

}

您需要帮助吗我让它起作用？谢谢！

Would you help me to make it works? Thanks!

推荐答案

我将您的代码重构为许多较小的方法。每种方法都只负责一件事情，这是一种好习惯，请尽可能尝试做到这一点。

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.

我只做了一些更改：

• 创建 FileReader 的行现在使用文件（容易出错）


• 通过创建单个插入查询来更改插入方式，从而减少对数据库的访问（例如： INSERT INTO table（codigo）VALUES（'XXXX'），（'ZZZZ'） ，（'FFFF'）; ）


• 更改了Toasts中的文本，以更好地确定错误的出处。

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

主要导入方法：

public void importaTabelas() {

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

            //Read all file names
            for (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(new String[]{Manifest.permission.READ_EXTERNAL_STORAGE}, 1);
    }

}

创建目录

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

导入单个文件

private void importFile(File f) {
    try {
        SQLiteDatabase db = this.banco.getWritableDatabase();

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

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

        createTable(db, nomeArq);

        String insertQuery = 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

private String buildImportQuery(File f, String nomeArq) {
    StringBuilder sb = new StringBuilder();
    try {
        //Reads the file
        FileReader fileReader = new FileReader(f);
        BufferedReader buffer = new BufferedReader(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();

}

创建单个表

private void createTable(SQLiteDatabase db, String tableName) {
    try {
        //Create table with the name of the .csv file
        String 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();
    }
}

这篇关于Android-将多个.CSV文件导入SQLite数据库中的多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！