在安卓中将SQLite数据库导出为csv文件

Question

我正在尝试将SQLite数据作为CSV文件导出到Android SD卡的目录中。

因此，我尝试了下面的方法，但显然它只打印出了这段文本：

数据库的第一个表
日期，物品，金额，货币

在我的DBHelper.java中，我定义了以下函数：

public boolean exportDatabase() {
        DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT, Locale.getDefault());

        /**First of all we check if the external storage of the device is available for writing.
         * Remember that the external storage is not necessarily the sd card. Very often it is
         * the device storage.
         */
        String state = Environment.getExternalStorageState();
        if (!Environment.MEDIA_MOUNTED.equals(state)) {
            return false;
        }
        else {
            //We use the Download directory for saving our .csv file.
            File exportDir = Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS);
            if (!exportDir.exists())
            {
                exportDir.mkdirs();
            }

            File file;
            PrintWriter printWriter = null;
            try
            {
                file = new File(exportDir, "MyCSVFile.csv");
                file.createNewFile();
                printWriter = new PrintWriter(new FileWriter(file));

                /**This is our database connector class that reads the data from the database.
                 * The code of this class is omitted for brevity.
                 */
                SQLiteDatabase db = this.getReadableDatabase(); //open the database for reading

                /**Let's read the first table of the database.
                 * getFirstTable() is a method in our DBCOurDatabaseConnector class which retrieves a Cursor
                 * containing all records of the table (all fields).
                 * The code of this class is omitted for brevity.
                 */
                Cursor curCSV = db.rawQuery("select * from contacts", null);
                //Write the name of the table and the name of the columns (comma separated values) in the .csv file.
                printWriter.println("FIRST TABLE OF THE DATABASE");
                printWriter.println("DATE,ITEM,AMOUNT,CURRENCY");
                while(curCSV.moveToNext())
                {
                    Long date = curCSV.getLong(curCSV.getColumnIndex("date"));
                    String title = curCSV.getString(curCSV.getColumnIndex("title"));
                    Float amount = curCSV.getFloat(curCSV.getColumnIndex("amount"));
                    String description = curCSV.getString(curCSV.getColumnIndex("description"));

                    /**Create the line to write in the .csv file.
                     * We need a String where values are comma separated.
                     * The field date (Long) is formatted in a readable text. The amount field
                     * is converted into String.
                     */
                    String record = df.format(new Date(date)) + "," + title + "," + amount + "," + description;
                    printWriter.println(record); //write the record in the .csv file
                }

                curCSV.close();
                db.close();
            }

            catch(Exception exc) {
                //if there are any exceptions, return false
                return false;
            }
            finally {
                if(printWriter != null) printWriter.close();
            }

            //If there are no errors, return true.
            return true;
        }
    }
}

我的列是：

 public static final String DATABASE_NAME = "MyDBName.db";
 public static final String CONTACTS_TABLE_NAME = "contacts";
 public static final String CONTACTS_COLUMN_ID = "id";
 public static final String CONTACTS_COLUMN_TITLE = "title";
 public static final String CONTACTS_COLUMN_AMOUNT = "amount";
 public static final String CONTACTS_COLUMN_DESC = "description";

如果您需要更多的代码，请告诉我。

- Steve Kamau

---

你想要什么？你有遇到任何错误吗？ - RajSharma

//在.csv文件中写入表名和列名（逗号分隔的值）。如果添加表名，则不再是有效的CSV文件。有效的CSV文件的第一行要么是带有列名的标题行，要么只是第一行数据行。 - Phantômaxx

就你提到的问题而言……似乎表格中没有数据。 - Phantômaxx

@CodeProcessor 不，我没有得到任何错误提示，CSV 文件只包含标题而不是 SQLite 数据本身。 - Steve Kamau

@DerGol...lum 在表中有数据，因为我可以在列表视图中显示数据。 - Steve Kamau

尝试将PrintWriter的AutoFlush参数设置为true。printWriter = new PrintWriter(new FileWriter(file), true); - Phantômaxx

Answer 1

感谢大家的建议，使我得出了这个答案：

private void exportDB() {

        DBHelper dbhelper = new DBHelper(getApplicationContext());
        File exportDir = new File(Environment.getExternalStorageDirectory(), "");
        if (!exportDir.exists())
        {
            exportDir.mkdirs();
        }

        File file = new File(exportDir, "csvname.csv");
        try
        {
            file.createNewFile();
            CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
            SQLiteDatabase db = dbhelper.getReadableDatabase();
            Cursor curCSV = db.rawQuery("SELECT * FROM contacts",null);
            csvWrite.writeNext(curCSV.getColumnNames());
            while(curCSV.moveToNext())
            {
                //Which column you want to exprort
                String arrStr[] ={curCSV.getString(0),curCSV.getString(1), curCSV.getString(2)};
                csvWrite.writeNext(arrStr);
            }
            csvWrite.close();
            curCSV.close();
        }
        catch(Exception sqlEx)
        {
            Log.e("MainActivity", sqlEx.getMessage(), sqlEx);
        }
}

- Steve Kamau

---

不太清楚如何获取getDatabasePath、DBHelper、CSVWriter以及代码中的其他部分。 - zygimantus

getDatabasePath() 方法会找到数据库位置的路径，DBHelper 是我的sqlite处理程序类，它继承了 sqliteOpenHelper，CSVWriter 是CSV功能。 代码可以直接运行，没有太多麻烦。 - Steve Kamau

很遗憾你没有在这里分享那些课程。它们可能对其他人有用。 - leoneboaventura

CSVWriter可以在这里找到：https://github.com/rogerta/secrets-for-android/tree/master/app/src/main/java/au/com/bytecode/opencsv。 - Dranna

@SteveKamau 如果我想要所有的数据怎么办？ - Krishna Murari

Answer 2

如果有人仍然遇到这个问题，我将发布我的解决方案，它比已接受的解决方案略为通用。你应该能够通过复制下面的两个类来导出sqlite数据库中的所有表格。唯一需要的其他更改应与获取应用程序上下文和导入 open csv 相关。

某些部分基本上是从其他stackoverflow线程复制粘贴过来的，但我再也找不到那些线程了。

Sqlite 导出器：

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.opencsv.CSVWriter;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * Can export an sqlite databse into a csv file.
 *
 * The file has on the top dbVersion and on top of each table data the name of the table
 *
 * Inspired by
 * https://dev59.com/U10Z5IYBdhLWcg3whAlv
 * and some other SO threads as well.
 *
 */
public class SqliteExporter {
    private static final String TAG = SqliteExporter.class.getSimpleName();

    public static final String DB_BACKUP_DB_VERSION_KEY = "dbVersion";
    public static final String DB_BACKUP_TABLE_NAME = "table";

    public static String export(SQLiteDatabase db) throws IOException{
        if( !FileUtils.isExternalStorageWritable() ){
            throw new IOException("Cannot write to external storage");
        }
        File backupDir = FileUtils.createDirIfNotExist(FileUtils.getAppDir() + "/backup");
        String fileName = createBackupFileName();
        File backupFile = new File(backupDir, fileName);
        boolean success = backupFile.createNewFile();
        if(!success){
            throw new IOException("Failed to create the backup file");
        }
        List<String> tables = getTablesOnDataBase(db);
        Log.d(TAG, "Started to fill the backup file in " + backupFile.getAbsolutePath());
        long starTime = System.currentTimeMillis();
        writeCsv(backupFile, db, tables);
        long endTime = System.currentTimeMillis();
        Log.d(TAG, "Creating backup took " + (endTime - starTime) + "ms.");

        return backupFile.getAbsolutePath();
    }

    private static String createBackupFileName(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd_HHmm");
        return "db_backup_" + sdf.format(new Date()) + ".csv";
    }

    /**
     * Get all the table names we have in db
     *
     * @param db
     * @return
     */
    public static List<String> getTablesOnDataBase(SQLiteDatabase db){
        Cursor c = null;
        List<String> tables = new ArrayList<>();
        try{
            c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
            if (c.moveToFirst()) {
                while ( !c.isAfterLast() ) {
                    tables.add(c.getString(0));
                    c.moveToNext();
                }
            }
        }
        catch(Exception throwable){
            Log.e(TAG, "Could not get the table names from db", throwable);
        }
        finally{
            if(c!=null)
                c.close();
        }
        return tables;
    }

    private static void writeCsv(File backupFile, SQLiteDatabase db, List<String> tables){
        CSVWriter csvWrite = null;
        Cursor curCSV = null;
        try {
            csvWrite = new CSVWriter(new FileWriter(backupFile));
            writeSingleValue(csvWrite, DB_BACKUP_DB_VERSION_KEY + "=" + db.getVersion());
            for(String table: tables){
                writeSingleValue(csvWrite, DB_BACKUP_TABLE_NAME + "=" + table);
                curCSV = db.rawQuery("SELECT * FROM " + table,null);
                csvWrite.writeNext(curCSV.getColumnNames());
                while(curCSV.moveToNext()) {
                    int columns = curCSV.getColumnCount();
                    String[] columnArr = new String[columns];
                    for( int i = 0; i < columns; i++){
                        columnArr[i] = curCSV.getString(i);
                    }
                    csvWrite.writeNext(columnArr);
                }
            }
        }
        catch(Exception sqlEx) {
            Log.e(TAG, sqlEx.getMessage(), sqlEx);
        }finally {
            if(csvWrite != null){
                try {
                    csvWrite.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if( curCSV != null ){
                curCSV.close();
            }
        }
    }

    private static void writeSingleValue(CSVWriter writer, String value){
        writer.writeNext(new String[]{value});
    }
}

FileUtils：

public class FileUtils {

    public static String getAppDir(){
        return App.getContext().getExternalFilesDir(null) + "/" + App.getContext().getString(R.string.app_name);
    }

    public static File createDirIfNotExist(String path){
        File dir = new File(path);
        if( !dir.exists() ){
            dir.mkdir();
        }
        return dir;
    }

    /* Checks if external storage is available for read and write */
    public static boolean isExternalStorageWritable() {
        String state = Environment.getExternalStorageState();
        return Environment.MEDIA_MOUNTED.equals(state);
    }

    /* Checks if external storage is available to at least read */
    public static boolean isExternalStorageReadable() {
        String state = Environment.getExternalStorageState();
        return Environment.MEDIA_MOUNTED.equals(state) ||
                Environment.MEDIA_MOUNTED_READ_ONLY.equals(state);
    }
}

除这两个类外，您需要向FileUtils传递上下文或者有其他获取上下文的方式。在上面的代码中，App只是一个继承Application以便轻松访问上下文的类。

同时记得在gradle中添加Opencsv，即:

compile group: 'com.opencsv', name: 'opencsv', version: '4.1'

然后只需调用Sqlite导出器类的导出方法。

- Pete

---

如何从 MainActivity 调用此方法？ - user2872856

你可能想从后台线程调用导出方法，因为导出数据库可能需要一些时间。为此，您可以创建一个 IntentService 或其他不会阻塞 UI 线程的东西。 - Pete

我必须进行一个微小的修改。在createDirIfNotExist方法中，我不得不将其从“dir.mkdir()”更改为“dir.mkdirs()”，因为我缺少多个父目录。除此之外，它完美地工作了。 - yarell

它可以工作了。不过我还得把它从“dir.mkdir()”改成“dir.mkdirs()”。谢谢。 - Anky An

无法解析符号“SqliteExporter”，我该怎么办？ - morteza naji

Answer 3

首先，删除此行以获得有效的CSV格式文档。

printWriter.println("FIRST TABLE OF THE DATABASE");

其次，确保您的表中有数据，并通过调试检查查询是否有返回结果。

- Gunhan