I have a subroutine on Basic called LINE.TO.CSV that accomodates data values having either single or double quotes. It alternates the delimiting quote character when necessary and if both are present, it replaces the single one with an accent. It's usually a text field anyway.
I also have its opposite subroutine called CSV.TO.REC that takes a CSV and returns it as a attributeXvalue from RowsXColumns. It strips out the bounded quotes if they're not data. The standard CONVERT would not work as it would take embedded commas and use 253's. Unfortunately it has to process each row into an attribute and parse each line, pairing the quotes. I also agree that Tabs went out a long time ago. My personal disappointment is opening a tabbed record in Notepad then saving and the tabs reverted to spaces. Thus, I avoid tabs. My 1 cent Mark Johnson ----- Original Message ----- From: "IT-Laure Hansen" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Wednesday, February 13, 2008 12:00 PM Subject: RE: [U2] Excel downloads > Comma delimited fields are always a bit of an issue in this case. The > correct way to program CSVs is to place quotes around the fields, to > account for just this - but then: what if you have quotes in your > fields? Tab is usually a "safer" delimiter for the simple reason that it > is rarely stored as such in data files, but Excel does not handle a > tab-delimited file quite as well as a CSV file. You can of course use > I-descriptors that convert quotes to blanks first, or any other > transformation. > > Thus my original question asking for help and tricks: I know what it > takes to get me to where I want to be, but I am looking for the miracle > tool that will work for all types of data extractions with minimal data > massaging and scripting from me. > > Thanks, > > Laure Hansen, > City of Redwood City > Information Technology > 1017 Middlefield Road > Redwood City, CA 94063 > Tel 650-780-7087 > Cell 650-207-3235 > Fax 650-556-9204 > [EMAIL PROTECTED] > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Dave Davis > Sent: Wednesday, February 13, 2008 7:36 AM > To: [email protected] > Subject: RE: [U2] Excel downloads > > What if the data fields contain quotes or double-quotes themselves? > Does this handle that situation? > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Bartlett > Sent: Wednesday, February 13, 2008 4:00 AM > To: [email protected] > Subject: RE: [U2] Excel downloads > > Laure > > Then it sounds like you could do the option described in these posts > > > > For those who are lazy to write fancy routines to create CSV files, I've > created two dict items, namely COMMA and QUOTE, as follows: > > COMMA > 001: I > 002: "," > 003: > 004: , > 005: 1T > > > QUOTE > 001: I > 002: '"' > 003: > 004: " > 005: 1T > > > To create a CSV file of _ANY_ file data, these dict items either need to > be in the DICT of every file, OR once off in the DICT.DICT (I think - > correct me anyone?) > > > > >> csv imports don't mind excessive spaces... > > LIST DFQ COMMA DFQ.ID COMMA ACCOUNT COMMA DATEOP COMMA 21:47:38 > 07-02-08 > > DFQUOTE.FILE. , QUOTE NO..... , ACCOUNT... , DATEOP.... , > 208-015646-01 , 208-015646-01 , 208-015646 , 04/05/2007 , > 205-011887-AH , 205-011887-AH , 205-011887 , 10/11/2000 , > 202-012637-AH , 202-012637-AH , 202-012637 , 16/02/2005 , > > > > > >> to handle numeric fields simply quote them > > LIST DFQ COMMA QUOTE DFQ.ID QUOTE COMMA QUOTE ACCOUNT QUOTE 21:50:36 > 07-02 > > DFQUOTE.FILE. , ' QUOTE NO..... ' , ' ACCOUNT... > ' > '208-015646-01 , ' 208-015646-01 ' , ' 208-015646 > ' > '205-011887-AH , ' 205-011887-AH ' , ' 205-011887 > ' > '202-012637-AH , ' 202-012637-AH ' , ' 202-012637 > ' > '202-014954-01 , ' 202-014954-01 ' , ' 202-014954 > ' > > > > > And here is where I used it > > 0001: PA > 0002: DISPLAY Saving output as AAQ > 0003: SP-ASSIGN HS > 0004: SETPTR 0,300,,0,0,3,BRIEF,NFMT, BANNER AAQ > 0005: SORT DFQ _ > 0006: WITH INVOICE <> "" _ > 0007: BY ACCOUNT _ > 0008: BY DATEOP _ > 0009: COMMA DFQ.ID _ > 0010: COMMA ACCOUNT _ > 0011: COMMA DATEOP _ > 0012: COMMA _ > 0013: COMMA DFQ.HPHONE _ > 0014: COMMA HPM.HOMEPHONE _ > 0015: COMMA _ > 0016: COMMA DFQ.WPHONE _ > 0017: COMMA HPM.WORKPHONE _ > 0018: COMMA _ > 0019: COMMA DFQ.CELL _ > 0020: COMMA HPM.CELLPHONE _ > 0021: COMMA _ > 0022: COMMA DFQ.SPWORKNO _ > 0023: COMMA HPM.SPWORKPHONE _ > 0024: COMMA _ > 0025: COMMA DFQ.SPCELL _ > 0026: COMMA HPM.SPCELLPHONE _ > 0027: LPTR NOPAGE HDR.SUP ID.SUP > > > > And subsequently modified by Kevin > > For what it's worth, > > I do something similar, but I use TAB. My Dict item looks like: > > 0001: I > > 0002: " " > > 0003: > > 0004: TAB > > 0005: 1L > > 0006: S > > > Line two is actually "^009", but it shows " ". > > Excel handles tab delimited well and I never worry about whether the > field is numeric. > > My output line would be: > > VAL1 TAB VAL2 TAB VAL3 TAB VAL4 > > Works well for me. > > --Kevin > ------- > u2-users mailing list > [email protected] > To unsubscribe please visit http://listserver.u2ug.org/ > ------- > u2-users mailing list > [email protected] > To unsubscribe please visit http://listserver.u2ug.org/ > ------- > u2-users mailing list > [email protected] > To unsubscribe please visit http://listserver.u2ug.org/ ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/
