在iPhone上的objective-C中将大文件读入sqlite表 [英] Read large file into sqlite table in objective-C on iPhone

问题描述

我有一个2 MB的文件，不是太大，我想放入一个sqlite数据库，以便我可以搜索它。大约有30K条目是CSV格式，每行有六个字段。我的理解是iPhone上的sqlite可以处理这个大小的数据库。

I have a 2 MB file, not too large, that I'd like to put into an sqlite database so that I can search it. There are about 30K entries that are in CSV format, with six fields per line. My understanding is that sqlite on the iPhone can handle a database of this size.

我采取了一些方法，但它们都慢了> 30秒。我试过了：

I have taken a few approaches but they have all been slow > 30 s. I've tried:

1）使用C代码读取文件并将字段解析为数组。

1) Using C code to read the file and parse the fields into arrays.

2）使用以下Objective-C代码解析文件并将其直接放入sqlite数据库：

2) Using the following Objective-C code to parse the file and put it into directly into the sqlite database:

NSString *file_text = [NSString stringWithContentsOfFile: filePath usedEncoding: NULL error: NULL];

NSArray  *lineArray = [file_text componentsSeparatedByString:@"\n"];

for(int k = 0; k < [lineArray count]; k++){
    NSArray *parts = [[lineArray objectAtIndex:k] componentsSeparatedByString: @","];

    NSString *field0       = [parts objectAtIndex:0];
    NSString *field2       = [parts objectAtIndex:2];
    NSString *field3       = [parts objectAtIndex:3];

NSString *loadSQLi = [[NSString alloc] initWithFormat: @"INSERT INTO TABLE (TABLE, FIELD0, FIELD2, FIELD3) VALUES ('%@', '%@', '%@');",field0, field2, field3];


    if (sqlite3_exec (db_table, [loadSQLi  UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
        sqlite3_close(db_table);
        NSAssert1(0, @"Error loading table: %s", errorMsg);
    }

我错过了什么吗？有谁知道将文件放入数据库的快速方法？

Am I missing something? Does anyone know of a fast way to get the file into a database?

或者是否可以将文件转换为可以直接读入sqlite的sqlite格式？

Or is it possible to translate the file into a sqlite format that can be read directly into sqlite?

或者我应该将文件转换为plist并将其加载到Dictionary中？不幸的是我需要搜索两个字段，我认为一个字典只能有一个键？

Or should I turn the file into a plist and load it into a Dictionary? Unfortunately I need to search on two of the fields, and I think a Dictionary can only have one key?

Louis，感谢您的回复。

Louis, thanks for your response.

我应该提到我只想在应用程序开始时将数据写入数据库，然后我只需要读取数据库，没有写入。

I should have mentioned that I only want to write the data into the database once at the beginning of the app, and then later I only need to do reads of the database, no writes.

您能否告诉我如何在构建过程中直接从CSV文件生成sqlite3数据库？

Can you tell me how to generate the sqlite3 database directly from the CSV file as part of the build process?

推荐答案

好的，我想我已经知道了。从Unix命令提示符开始，以下内容读入CSV文件并写出一个SQL文件：

OK, I think I've got it. Starting from the Unix command prompt, the following reads in the CSV file and writes out a SQL file:

 % sqlite3
 sqlite3> .mode csv
 sqlite3>  create table NEWTABLE (ITEM_ID INTEGER PRIMARY KEY, FIELD0 TEXT, FIELD1 TEXT, FIELD2 TEXT, FIELD3 TEXT, FIELD4 TEXT, FIELD5 TEXT);
 sqlite3> .import csvfile.csv NEWTABLE
 sqlite3> .output csvfile.sql
 sqlite3> .dump NEWTABLE

我仍然没有弄清楚的部分是否有快速阅读方式将sql文件导入sqlite3。我目前的方法是读取sql文件的每一行并在iPhone上执行。是否有单行方法将sql文件读入sqlite3？

The part I still haven't figured out is if there is a quick way to read the sql file into sqlite3. The approach I currently have is to read each line of the sql file and execute on the iPhone. Is there a one line approach to reading the sql file into sqlite3?

这篇关于在iPhone上的objective-C中将大文件读入sqlite表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！