iPhone: Import CSV data into SQLite

Hi,

Does anyone have any experience in converting a CSV file into SQLite format? There must be an easy way.

Cheers,

Stu

Mac Pro, Mac Mini and MacBook running, Mac OS X (10.5.5), iPhone (3G & PAYG), Time Capsule

Posted on Sep 25, 2008 2:19 AM

Reply
8 replies

Sep 25, 2008 6:40 AM in response to Big Stu

Documentation on it is not easy to find, and there's not much.

From the command line interface on your dev system you can use ".import FILE TABLE" where FILE is a path and TABLE is the destination table name. You will also need to use ".separator" to set the delimiter. I used it recently to import some space delimited data, after figuring out that multiple spaces were treated as multiple field delimiters, and not the single "whitespace" I was expecting.

Sep 25, 2008 7:13 AM in response to Big Stu

This is what I did and it worked fine.

1) open the file in Excel and export it as a tab delimited .txt file (make sure there are no headings or anything, just the raw data starting from row 1).
2) create your sql lite database via sqlite3 myDB.sql or whatever
3) create your table with columns that coincide with the original file
4) now type .separator "\t" in sqlite: this tells it to expect a tab delimited file
5) type .import yourFile.txt yourTable

and there you go. as a word of caution beware if your text file has any strange characters and format it accordingly.

Good luck!

Oct 13, 2008 8:15 PM in response to Big Stu

no luck! for some reason when i try to import, i get the dialog, i fill in the new table name, i fill in the delimiter options, but then when i hit create, nothing happens. is there supposed to be something that shows up in the table-viewing type window beneath the delimiter options? i tried an existing table name as well as a new table name.

help!

Feb 13, 2009 7:55 AM in response to Big Stu

I have no problems with the .IMPORT function of sqlite3. However, I do have a problem using the table within iPhone when it contains any non 7-bit Latin chars. For example, word/excel curly quotes, diacritics, degree symbol, etc.

The records containing these chars seem to import -- though it's a bit hard to tell if the chars are correct as they don't display properly in a terminal window running sqlite3. More troubling is that the select calls from within the iPhone application do not parse properly and give me nil strings. I'm essentially using the same code strategy as in SQLiteBooks sample app. Here's the code:

char *str = (char *)sqlite3 column_text(hydratestatement, 0);
self.question = (str) ? [NSString stringWithUTF8String:str] : @"Q";

It seems that self.question ends up being nil (i.e. stringWithUTF8String fails parsing) when str contains a problem character.

Anybody successfully take diacritics all the way from CSV file to iPhone screen via SQLite?

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

iPhone: Import CSV data into SQLite

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.