从 CSV 文件填充 Android 数据库? [英] Populate Android Database From CSV file?

问题描述

是否可以将存储在 res/raw 资源目录中的 csv 文件用于填充 sqlite3 数据库中的表?

Is it possible to take a csv file stored in the res/raw resource directory and use it to populate a table in the sqlite3 database?

我的想法是，如果有一种方法可以将整个文件批量导入到表中，那么这将比遍历文件中的每一行并执行单独的插入语句更清晰、更快......

My thought was that, if there was a way to do a bulk import for the entire file into the table then that would be cleaner and faster than iterating over each line in the file and executing individual insert statements...

我发现有一个 sqlite 导入命令允许这样做:如何将 .sql 或 .csv 文件导入到 SQLite?

I've found that there is a sqlite import command that allows this: How to import load a .sql or .csv file into SQLite?

...但我无法在我的 Android 应用程序中应用这些语句.我的第一个想法是尝试类似以下的东西......但没有运气:

...but I'm having trouble applying those statements in my Android application. My first thought was to try something like the following...but no luck:

db.execSQL("CREATE TABLE " + TABLE_NAME + "(id INTEGER PRIMARY KEY, name TEXT)");
db.execSQL(".mode csv");
db.execSQL(".import res/raw/MyFile.csv " + TABLE_NAME); 

这可能吗?

我应该尝试不同的方法来填充我的数据库吗?

Should I be trying a different approach to populate my database?

更新:我将 Josef 的回复标记为答案(使用事务批量插入)，因为它工作正常并根据我的标题直接回答我的问题(感谢 Josef).但是，我仍在寻找一种使用 import 语句在 Android 应用程序中从 csv 文件批量插入 sqlite3 表的方法.如果您知道如何执行此操作，请回复.

UPDATE: I'm marking Josef's response as the answer (bulk insert using transactions) because it works fine and directly answers my question based on my title (thanks Josef). However, I'm am still looking for a way to do a bulk insert in an Android app from csv file into a sqlite3 table using the import statement. If you know how to do this please respond.

感谢您的回答！

推荐答案

如果您想将静态数据与您的应用程序一起打包，我建议您在开发时准备数据库(使用您喜欢的任何 UI 或 csv-import 命令)并发送assets 文件夹中的 sqlite 文件.然后，当您的应用程序首次运行时，您可以简单地将整个 sqlite 文件复制到设备上.这些 posts 带您了解这个想法，这很可能是设置数据库的最快方法(文件复制速度).

If you want to package static data with your application, I recommend preparing the database at development time (using any UI or csv-import command you like) and shipping the sqlite file inside the assets folder. You can then simply copy the entire sqlite file onto the device when your application is first run. These posts take you through this idea which is most likely the fastest way to setup a database (file copy speed).

如果出于某种原因您确实需要在运行时插入大量数据，我建议您查看使用事务批量插入 以加快速度.

If, for some reason you do need to insert a lot of data at run time, I recommend you look at ways to bulk insert using transactions to speed it up.

这篇关于从 CSV 文件填充 Android 数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！