iPhone: Import CSV data into SQLite

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.

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!

SQLite database browser has an import function:

http://sqlitebrowser.sourceforge.net/

Hmm...that looks cool; does it do anything to protect against messy characters (like when Microsoft word autoslants apostrophes)

I'm not sure. It's pretty simple. You can set the delimiter and the text quote character, but not much else.

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!

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?