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

问题描述

是否可以使用存储在res / raw资源目录中的csv文件，并使用它来填充sqlite3数据库中的表？

我的想法是，如果有一个方法来批量导入整个文件到表中，那将更清洁，更快，迭代文件中的每一行，并执行单独的插入语句...

我发现有一个sqlite import命令允许这样：如何导入将.sql或.csv文件加载到sqlite？

...但我无法在我的Android应用程序中应用这些语句。我的第一个想法是尝试像下面的东西，但没有运气：

  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）; 
  

这是否可能？

尝试不同的方法来填充我的数据库？

更新：我标记Josef的答复作为答案（批量插入使用事务），因为它工作正常，并直接回答我问题基于我的头衔（感谢Josef）。但是，我仍然在寻找一种方法，在Android应用程序从csv文件到一个sqlite3表使用import语句批量插入。

解决方案

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

如果由于某种原因需要在运行时插入大量数据，我建议您查看批量插入使用交易以加快速度。

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...

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

...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); 

Is this possible?

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

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.

Thanks for you answers!

解决方案

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屋！