If you have quotes in the data you prefix them with a quote - for example, the field value:
In your "free" time Becomes "In your ""free"" time" The easiest way to see what the CSV should look like is to type some data in an excel spreadsheet and output it to a CSV file. Our current approach is to create a CSV file, a headings-only Excel spreadsheet with the columns typed correctly, and macros to import the CSV file (which uses a .txt extension instead of .csv in our case), auto-size the columns, and anything else. Would be interested in hearing about any different/better approaches. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of IT-Laure Hansen Sent: Wednesday, February 13, 2008 12:01 PM To: [email protected] 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/
