On Mon, 13 Sep 2010, Mithun Nair wrote: > When i try importing it into a SQLite table, i get some errors like > "expected 2 columns of data but found 1". Later i found that importing a > comma separated file into SQlite wont work because a comma is considered > as a delimiter by the the SQLite engine even within a "". Is this true? My > csv file source is a webservice, so i have to try adjusting with the comma > delimiter. They are not gonna change it for me :). Should i write some > simple string parser of my own? Or is there any better, optimal method?
Mithun, I've gone through this a couple of times (because I did not save the solution from the first time), most recently this past weekend. Here's what you need to do to clean up your .csv file for import into SQLite. Use the text editor of your choice (emacs, vim, joe, whatever). First, you are correct that commas embedded in text strings are seen as column delimiters. Therefore, convert all commas to the default SQLite separator |. In my data I run this series: - replace all "," with "|" - replace all ,,, with ||| - repeat above for all NULL columns in your source file with varying numbers of commans. Second, replace all " (double qoutes) used to delimit text attributes with ' (single quotes). That's also a global search and replace. I've not had a problem with apostrophes within a quoted text string as long as the column separators were the vertical bars. HTH, Rich _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
