Re: [sqlite] replacing all newlines in a field
On 2/11/08, BareFeet <[EMAIL PROTECTED]> wrote: > Hi Puneet, > > > I have a db in which the data in some fields in some tables has > > newlines (line breaks). All is fine until I want to export this into > > a CSV or tab-delimited format in which case the line breaks mess up > > the > > row-by-row records. > > How does it mess it up? CSV facilitates newlines in the data by > wrapping in quotes any cells containing them. I just did a quick text > using: > > .mode csv > select MyField from MyTable; > > where data in myField contains newlines etc, and SQLite seemed to > output correctly, wrapping those data elements in double quotes. > > into what other program do you want to import the resulting CSV? All > software that properly handles CSV importing should handle the output > from SQLite correctly (according to by brief test). I don't know what I was smoking. You are very correct. Ok. This problem solved. I don't have to scramble these eggs. Onto the next one in a new thread. > > > I am thinking, maybe I should not allow storing line breaks in the > > database > > I think that's extreme and starts scrambling the egg. > > Tom > BareFeet > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] replacing all newlines in a field
Hi Puneet, > I have a db in which the data in some fields in some tables has > newlines (line breaks). All is fine until I want to export this into > a CSV or tab-delimited format in which case the line breaks mess up > the > row-by-row records. How does it mess it up? CSV facilitates newlines in the data by wrapping in quotes any cells containing them. I just did a quick text using: .mode csv select MyField from MyTable; where data in myField contains newlines etc, and SQLite seemed to output correctly, wrapping those data elements in double quotes. into what other program do you want to import the resulting CSV? All software that properly handles CSV importing should handle the output from SQLite correctly (according to by brief test). > I am thinking, maybe I should not allow storing line breaks in the > database I think that's extreme and starts scrambling the egg. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] replacing all newlines in a field
Hi Puneet, Question: Is there a way I can search and replace all the line breaks? x'hh' should work, where hh are hex digits. So if your file contained hex 0D type line breaks: UPDATE t SET essay_without_newlines = Replace(essay, x'0D', '~'); This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
