> John Machin wrote: > >>On 7/08/2009 1:21 PM, aerende wrote: >>> I'm trying to take a CSV file and create a sqlite3 database for the >>> iPhone. >>> The CSV file has 33K entries and is 2 MB. The problem I am having is >>> that >>> only about 1/10 of the database file gets written into the sqlite3 >>> database. >>> >>> I first translated the CSV file into SQL commands using the >>> terminal-based >>> verison of sqlite3: >>> >>> % sqlite3 >>> sqlite> .mode csv >>> sqlite> create table mydatabasetable (ITEM_ID INTEGER PRIMARY KEY, >>> FIELDA TEXT, FIELDB TEXT); >>> sqlite> .import myfile.csv mydatabasetable > >>Were there any error messages from that step? > > Nope. > >> >>If at this stage you do >> >> select count(*) from mydatabasetable; >> >>what is the result? > > I get the full 33K. > >>If it's not the full 33K, which records are being >>left out? >> >> sqlite> .output mydatabasetable.sql >> >>The .output command specifies what file any output will be sent to. It >>doesn't actually generate any output itself. Perhaps you are missing a >>.dump command and a quit command -- it's always a good idea to >>copy/paste actual output into your mail client, rather than re-typing it >>from memory. > > Good point. I executed a .dump command after this. Sorry about that. > > >> >>> Then I tried to create a sqlite3 database from the sql file: >>> >>> % sqlite3 mydatabasetable.sqlite < mydatabasetable.sql >> >>Any errors from this step? What does select count(*) give you? > > Nope, no errors, and I get 33K when I load mydatabasetable.sqlite > back into sqlite3. > >> >>> >>> When I read in mydatabasetable.sqlite into a sqlite3 database, > > I executed % sqlite3 mydatabasetable.sqlite > >> >>What does that mean? A third step? If mydatabasetable.sqlite is not >>already a sqlite3 database, the previous steps have run amok somehow. >> >>> only the >>> first 3400 entries out of 33,000 are in the database even though >>> mydatabasetable.sql has 33,000 unique insert commands. > >>In which database? >> >>> Am I following the correct approach to write out an sqlite database? >> >>Dunno why you are doing it in two (three?) steps; the CSV import should >>be all you need. >> >>> Is >>> there some default database filesize limit that I need to set? Does >>> anyone >>> know why only the first 3400 entries show up in the database? >> >>It would help very much if you said what version of SQLite you are >>running and what platform you are running it on. > > I'm running > > % sqlite3 --version > 3.4.0 > > on a MacPro > > It turns out that the problem went away and I can now read all 33K entries > in the > database file. Strange. For the nth time I blew away the local files > that the iPhone was > reading, and this time it did the trick. Could have been the iPhone local > files were > corrupted. I didn't have any commas or newlines inside of the strings, so > that > wasn't the problem. This problem of only being able to read 1/10 of the > file persisted > over 2 solid days of trying to fix it, where I also blew away the iPhone > local files. > I wish I knew what caused it, but I hope I don't see it again. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >
-- View this message in context: http://www.nabble.com/translating-CSV-file-into-sqlite3-database-for-iPhone--tp24858168p24866404.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
