Re: [sqlite] translating CSV file into sqlite3 database for iPhone?
On 8/08/2009 2:02 AM, Simon Slavin wrote: > On 7 Aug 2009, at 4:21am, aerende wrote: > >>sqlite> .import myfile.csv mydatabasetable >>sqlite> .output mydatabasetable.sql > > When you look at the .sql file in a text editor, does it make sense ? > Does it look like legal SQL ? Does it have all the INSERT commands in ? > > I would probably try it differently: open the .csv file in a > spreadsheet program, and use calculations to convert each line into an > INSERT command. Then save that column of commands as a text file and > add the CREATE TABLE and other commands to it. Good idea, but not a novel one; creating INSERT statements using Excel is rather prevalent in rapid-response "support" environments and provides many work opportunities for data remediaters. Example: a database where many rows were thrown up by this query: select account_num, price, qty, amount from a_table where price * qty != amount; Further investigation showed that a high proportion met one of the following criteria: (1) price = account_num (2) qty = account_num (3) amount = account_num (4) price * qty = account_num -- after allowing for rounding. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] translating CSV file into sqlite3 database for iPhone?
On 7 Aug 2009, at 4:21am, aerende wrote: >sqlite> .import myfile.csv mydatabasetable >sqlite> .output mydatabasetable.sql When you look at the .sql file in a text editor, does it make sense ? Does it look like legal SQL ? Does it have all the INSERT commands in ? I would probably try it differently: open the .csv file in a spreadsheet program, and use calculations to convert each line into an INSERT command. Then save that column of commands as a text file and add the CREATE TABLE and other commands to it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] translating CSV file into sqlite3 database for iPhone?
> This is why I generally advocate TAB delimited files over CSV How does .mode tabs cope with quoted strings with tabs or newlines in them? RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com HARRIS CORPORATION | RF Communications Division assuredcommunications(tm) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] translating CSV file into sqlite3 database for iPhone?
> 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
Re: [sqlite] translating CSV file into sqlite3 database for iPhone?
Have you tried my SQLiteManager app? http://www.sqlabs.com/sqlitemanager.php It can easily convert/import your CSV file into an sqlite3 database. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Aug 7, 2009, at 4:57 PM, Adam DeVita wrote: > This is why I generally advocate TAB delimited files over CSV > > Restaurant , Menu Item, Price > Tom, Dick "The MAN", and Harry's Bar & Grill , Specials /new stuff! > Mikey's > Burger "Delishiousness ' , $5 > > If you only have to upload your data once, you should be able to use a > spreadsheet program to convert to TAB delimited rather than going > through > the work of writing your own parser. > > > > On Fri, Aug 7, 2009 at 10:43 AM, Wilson, Ron P < > ronald.wil...@tycoelectronics.com> 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. >> >> The .import csv method is imperfect; if you have quoted strings in >> your csv >> that have commas or newlines in them, the import will do surprising >> things. >> I had to write my own code to do imports with quoted strings. >> >> RW >> >> Ron Wilson, Engineering Project Lead >> (o) 434.455.6453, (m) 434.851.1612, www.harris.com >> >> HARRIS CORPORATION | RF Communications Division >> assuredcommunications(tm) >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > VerifEye Technologies Inc. > 905-948-0015x245 > 7100 Warden Ave, Unit 3 > Markham ON, L3R 8B5 > Canada > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] translating CSV file into sqlite3 database for iPhone?
This is why I generally advocate TAB delimited files over CSV Restaurant , Menu Item, Price Tom, Dick "The MAN", and Harry's Bar & Grill , Specials /new stuff! Mikey's Burger "Delishiousness ' , $5 If you only have to upload your data once, you should be able to use a spreadsheet program to convert to TAB delimited rather than going through the work of writing your own parser. On Fri, Aug 7, 2009 at 10:43 AM, Wilson, Ron P < ronald.wil...@tycoelectronics.com> 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. > > The .import csv method is imperfect; if you have quoted strings in your csv > that have commas or newlines in them, the import will do surprising things. > I had to write my own code to do imports with quoted strings. > > RW > > Ron Wilson, Engineering Project Lead > (o) 434.455.6453, (m) 434.851.1612, www.harris.com > > HARRIS CORPORATION | RF Communications Division > assuredcommunications(tm) > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] translating CSV file into sqlite3 database for iPhone?
> 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. The .import csv method is imperfect; if you have quoted strings in your csv that have commas or newlines in them, the import will do surprising things. I had to write my own code to do imports with quoted strings. RW Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com HARRIS CORPORATION | RF Communications Division assuredcommunications(tm) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] translating CSV file into sqlite3 database for iPhone?
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? If at this stage you do select count(*) from mydatabasetable; what is the result? 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. > 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? > > When I read in mydatabasetable.sqlite into a sqlite3 database, 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] translating CSV file into sqlite3 database for iPhone?
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 sqlite> .output mydatabasetable.sql Then I tried to create a sqlite3 database from the sql file: % sqlite3 mydatabasetable.sqlite < mydatabasetable.sql When I read in mydatabasetable.sqlite into a sqlite3 database, only the first 3400 entries out of 33,000 are in the database even though mydatabasetable.sql has 33,000 unique insert commands. Am I following the correct approach to write out an sqlite database? 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? -- View this message in context: http://www.nabble.com/translating-CSV-file-into-sqlite3-database-for-iPhone--tp24858168p24858168.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
