Re: [U2] Excel downloads
I'll try this method. I do beaucoups of downloads. Thanks - Original Message - From: Symeon Breen [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Wednesday, February 13, 2008 10:42 AM Subject: RE: [U2] Excel downloads Instead of 07748,012345,000123 have =07748,=012345,=000123 ?? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MAJ Programming Sent: 13 February 2008 14:05 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Excel downloads On this topic, I have a request for a solution. In New Jersey, all the zip codes start with '0'. Add product codes like 012345 and order numbers like 000123 etc and you'll see the leading zeros. When creating and exporting a CSV to eventually be used by Excel, those columns lose their leading zeros as Excel thinks they are numeric. I know that the users can format column and choose Custom etc. I've converted the same CSV into a HTML boxed record and the zeros remain as expected. I've tried prepending the values with an astrophe (single quote) to no avail. The true data (as viewed in Notepad) is exactly as I sent it: 07748,012345,000123 So, any tricks in having Excel leave the zeros alone when clicking on the CSV in WIndows Explorer. Meaning, that the person opening the CSV in excel doesn't have to convert anything. Thanks in advance Mark Johnson - Original Message - From: David Murray [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Tuesday, February 12, 2008 4:40 PM Subject: RE: [U2] Excel downloads Laurie, For simple totals, it is possible to add an EXCEL formula into a cell by starting the cell/formula with an '=' sign. This also works with CSV structured files, which EXCEL will load and convert on the fly. Create a virtual/correlative field which will simply output the formula as text and add it to the CSV file e.g. =SUM(An). Cheers, David Murray .learn and do .excel and share -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of IT-Laure Hansen Sent: Tuesday, February 12, 2008 12:10 PM To: u2-users@listserver.u2ug.org Subject: [U2] Excel downloads Hello, We are looking for a simple way to drop aggregate totals type reports out of Universe into Excel. Our users are using AccuTerm and we also have MITS Reports installed. But, neither one does the trick for summarized reports that use the DET-SUP option. Having the detail and forcing the users to do their own grouping/sub-totals in Excel is simply not an acceptable option here. UV 10.2.3 on Win2003 server; AccuTerm 2K2 re. 5.1. We also would be happy to develop a simple (key word...) VB.Net executable that works smoothly regardless of the version of Excel, as our users work on anything from Office 2000 and above. Any suggestions welcome. TIA, 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] mailto:[EMAIL PROTECTED] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Excel downloads
In response to Karen: It's not an import option issue. A CSV is a member of the Excel class of data files and when double clicking in explorer, Excel opens it and sSays Welcome home with no import wizard. My request is to not have any intervention of the user. They can adjust the columns as necessary themselves as CSV's don't convey column widths although an earlier response gave me the =0123 suggestion. I'm a little hesitant of putting a formula in front of the data but on the other hand it would only be for those values that are numeric and start with 0. I would not have to use =Fred as Fred is okay. Thanks - Original Message - From: Bessel, Karen [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Wednesday, February 13, 2008 11:43 AM Subject: RE: [U2] Excel downloads I have had similar problems with Excel in the past. Have you tried importing the column as text? Sometimes that works sometimes it doesn't. Excel is a very peculiar animal indeed. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MAJ Programming Sent: Wednesday, February 13, 2008 8:05 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Excel downloads On this topic, I have a request for a solution. In New Jersey, all the zip codes start with '0'. Add product codes like 012345 and order numbers like 000123 etc and you'll see the leading zeros. When creating and exporting a CSV to eventually be used by Excel, those columns lose their leading zeros as Excel thinks they are numeric. I know that the users can format column and choose Custom etc. I've converted the same CSV into a HTML boxed record and the zeros remain as expected. I've tried prepending the values with an astrophe (single quote) to no avail. The true data (as viewed in Notepad) is exactly as I sent it: 07748,012345,000123 So, any tricks in having Excel leave the zeros alone when clicking on the CSV in WIndows Explorer. Meaning, that the person opening the CSV in excel doesn't have to convert anything. Thanks in advance Mark Johnson - Original Message - From: David Murray [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Tuesday, February 12, 2008 4:40 PM Subject: RE: [U2] Excel downloads Laurie, For simple totals, it is possible to add an EXCEL formula into a cell by starting the cell/formula with an '=' sign. This also works with CSV structured files, which EXCEL will load and convert on the fly. Create a virtual/correlative field which will simply output the formula as text and add it to the CSV file e.g. =SUM(An). Cheers, David Murray .learn and do .excel and share -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of IT-Laure Hansen Sent: Tuesday, February 12, 2008 12:10 PM To: u2-users@listserver.u2ug.org Subject: [U2] Excel downloads Hello, We are looking for a simple way to drop aggregate totals type reports out of Universe into Excel. Our users are using AccuTerm and we also have MITS Reports installed. But, neither one does the trick for summarized reports that use the DET-SUP option. Having the detail and forcing the users to do their own grouping/sub-totals in Excel is simply not an acceptable option here. UV 10.2.3 on Win2003 server; AccuTerm 2K2 re. 5.1. We also would be happy to develop a simple (key word...) VB.Net executable that works smoothly regardless of the version of Excel, as our users work on anything from Office 2000 and above. Any suggestions welcome. TIA, 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] mailto:[EMAIL PROTECTED] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Excel downloads
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: u2-users@listserver.u2ug.org 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: u2-users@listserver.u2ug.org 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: u2-users@listserver.u2ug.org 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
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 u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Excel downloads
Laure, You can also use a PERL interface to create/read excel spreadsheets on the SERVER without the need to have any Microsoft Excel components installs. This is a nice cross platform solution. Of course, you must have Perl installed on the server, but, this is trival in most cases. I developed some Unidata (Basic) code that interfaces with a Perl Script at the old company that I worked for. Email me offline for more details. [sidenote][ad]Currently looking for full time employment in the South Florida, East Coast, area. Contracting work will be considered[ad][sidenote]. Don Verhagen - Original Message - From: IT-Laure Hansen [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Tuesday, February 12, 2008 12:10 PM Subject: [U2] Excel downloads Hello, We are looking for a simple way to drop aggregate totals type reports out of Universe into Excel. Our users are using AccuTerm and we also have MITS Reports installed. But, neither one does the trick for summarized reports that use the DET-SUP option. Having the detail and forcing the users to do their own grouping/sub-totals in Excel is simply not an acceptable option here. UV 10.2.3 on Win2003 server; AccuTerm 2K2 re. 5.1. We also would be happy to develop a simple (key word...) VB.Net executable that works smoothly regardless of the version of Excel, as our users work on anything from Office 2000 and above. Any suggestions welcome. TIA, 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] mailto:[EMAIL PROTECTED] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Excel downloads
On this topic, I have a request for a solution. In New Jersey, all the zip codes start with '0'. Add product codes like 012345 and order numbers like 000123 etc and you'll see the leading zeros. When creating and exporting a CSV to eventually be used by Excel, those columns lose their leading zeros as Excel thinks they are numeric. I know that the users can format column and choose Custom etc. I've converted the same CSV into a HTML boxed record and the zeros remain as expected. I've tried prepending the values with an astrophe (single quote) to no avail. The true data (as viewed in Notepad) is exactly as I sent it: 07748,012345,000123 So, any tricks in having Excel leave the zeros alone when clicking on the CSV in WIndows Explorer. Meaning, that the person opening the CSV in excel doesn't have to convert anything. Thanks in advance Mark Johnson - Original Message - From: David Murray [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Tuesday, February 12, 2008 4:40 PM Subject: RE: [U2] Excel downloads Laurie, For simple totals, it is possible to add an EXCEL formula into a cell by starting the cell/formula with an '=' sign. This also works with CSV structured files, which EXCEL will load and convert on the fly. Create a virtual/correlative field which will simply output the formula as text and add it to the CSV file e.g. =SUM(An). Cheers, David Murray .learn and do .excel and share -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of IT-Laure Hansen Sent: Tuesday, February 12, 2008 12:10 PM To: u2-users@listserver.u2ug.org Subject: [U2] Excel downloads Hello, We are looking for a simple way to drop aggregate totals type reports out of Universe into Excel. Our users are using AccuTerm and we also have MITS Reports installed. But, neither one does the trick for summarized reports that use the DET-SUP option. Having the detail and forcing the users to do their own grouping/sub-totals in Excel is simply not an acceptable option here. UV 10.2.3 on Win2003 server; AccuTerm 2K2 re. 5.1. We also would be happy to develop a simple (key word...) VB.Net executable that works smoothly regardless of the version of Excel, as our users work on anything from Office 2000 and above. Any suggestions welcome. TIA, 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] mailto:[EMAIL PROTECTED] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Excel downloads
Also, believe it or not, this works with HTML as well - even the formulas. This gives one great flexibility as one can do shading, font manipulations, borders, column-widths, etc. What I did is create a subroutine CSV.TO.HTML with options for titles, sub-titles, headings and footings. For the titles and subtitles it merges and centers cells using col-span... Craig McDonald Gensco -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Murray Sent: Tuesday, February 12, 2008 1:40 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] Excel downloads Laurie, For simple totals, it is possible to add an EXCEL formula into a cell by starting the cell/formula with an '=' sign. This also works with CSV structured files, which EXCEL will load and convert on the fly. Create a virtual/correlative field which will simply output the formula as text and add it to the CSV file e.g. =SUM(An). Cheers, David Murray .learn and do .excel and share --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
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: u2-users@listserver.u2ug.org 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 u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Excel downloads
Instead of 07748,012345,000123 have =07748,=012345,=000123?? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MAJ Programming Sent: 13 February 2008 14:05 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Excel downloads On this topic, I have a request for a solution. In New Jersey, all the zip codes start with '0'. Add product codes like 012345 and order numbers like 000123 etc and you'll see the leading zeros. When creating and exporting a CSV to eventually be used by Excel, those columns lose their leading zeros as Excel thinks they are numeric. I know that the users can format column and choose Custom etc. I've converted the same CSV into a HTML boxed record and the zeros remain as expected. I've tried prepending the values with an astrophe (single quote) to no avail. The true data (as viewed in Notepad) is exactly as I sent it: 07748,012345,000123 So, any tricks in having Excel leave the zeros alone when clicking on the CSV in WIndows Explorer. Meaning, that the person opening the CSV in excel doesn't have to convert anything. Thanks in advance Mark Johnson - Original Message - From: David Murray [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Tuesday, February 12, 2008 4:40 PM Subject: RE: [U2] Excel downloads Laurie, For simple totals, it is possible to add an EXCEL formula into a cell by starting the cell/formula with an '=' sign. This also works with CSV structured files, which EXCEL will load and convert on the fly. Create a virtual/correlative field which will simply output the formula as text and add it to the CSV file e.g. =SUM(An). Cheers, David Murray .learn and do .excel and share -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of IT-Laure Hansen Sent: Tuesday, February 12, 2008 12:10 PM To: u2-users@listserver.u2ug.org Subject: [U2] Excel downloads Hello, We are looking for a simple way to drop aggregate totals type reports out of Universe into Excel. Our users are using AccuTerm and we also have MITS Reports installed. But, neither one does the trick for summarized reports that use the DET-SUP option. Having the detail and forcing the users to do their own grouping/sub-totals in Excel is simply not an acceptable option here. UV 10.2.3 on Win2003 server; AccuTerm 2K2 re. 5.1. We also would be happy to develop a simple (key word...) VB.Net executable that works smoothly regardless of the version of Excel, as our users work on anything from Office 2000 and above. Any suggestions welcome. TIA, 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] mailto:[EMAIL PROTECTED] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Excel downloads
Yes, =002536 will stay as 002536 when opening the csv file with Excel instead of importing it. -Original Message- From: MAJ Programming [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 13, 2008 8:05 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Excel downloads On this topic, I have a request for a solution. In New Jersey, all the zip codes start with '0'. Add product codes like 012345 and order numbers like 000123 etc and you'll see the leading zeros. When creating and exporting a CSV to eventually be used by Excel, those columns lose their leading zeros as Excel thinks they are numeric. I know that the users can format column and choose Custom etc. I've converted the same CSV into a HTML boxed record and the zeros remain as expected. I've tried prepending the values with an astrophe (single quote) to no avail. The true data (as viewed in Notepad) is exactly as I sent it: 07748,012345,000123 So, any tricks in having Excel leave the zeros alone when clicking on the CSV in WIndows Explorer. Meaning, that the person opening the CSV in excel doesn't have to convert anything. Thanks in advance Mark Johnson - Original Message - From: David Murray [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Tuesday, February 12, 2008 4:40 PM Subject: RE: [U2] Excel downloads Laurie, For simple totals, it is possible to add an EXCEL formula into a cell by starting the cell/formula with an '=' sign. This also works with CSV structured files, which EXCEL will load and convert on the fly. Create a virtual/correlative field which will simply output the formula as text and add it to the CSV file e.g. =SUM(An). Cheers, David Murray .learn and do .excel and share -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of IT-Laure Hansen Sent: Tuesday, February 12, 2008 12:10 PM To: u2-users@listserver.u2ug.org Subject: [U2] Excel downloads Hello, We are looking for a simple way to drop aggregate totals type reports out of Universe into Excel. Our users are using AccuTerm and we also have MITS Reports installed. But, neither one does the trick for summarized reports that use the DET-SUP option. Having the detail and forcing the users to do their own grouping/sub-totals in Excel is simply not an acceptable option here. UV 10.2.3 on Win2003 server; AccuTerm 2K2 re. 5.1. We also would be happy to develop a simple (key word...) VB.Net executable that works smoothly regardless of the version of Excel, as our users work on anything from Office 2000 and above. Any suggestions welcome. TIA, 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] mailto:[EMAIL PROTECTED] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Excel downloads
I have had similar problems with Excel in the past. Have you tried importing the column as text? Sometimes that works sometimes it doesn't. Excel is a very peculiar animal indeed. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MAJ Programming Sent: Wednesday, February 13, 2008 8:05 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Excel downloads On this topic, I have a request for a solution. In New Jersey, all the zip codes start with '0'. Add product codes like 012345 and order numbers like 000123 etc and you'll see the leading zeros. When creating and exporting a CSV to eventually be used by Excel, those columns lose their leading zeros as Excel thinks they are numeric. I know that the users can format column and choose Custom etc. I've converted the same CSV into a HTML boxed record and the zeros remain as expected. I've tried prepending the values with an astrophe (single quote) to no avail. The true data (as viewed in Notepad) is exactly as I sent it: 07748,012345,000123 So, any tricks in having Excel leave the zeros alone when clicking on the CSV in WIndows Explorer. Meaning, that the person opening the CSV in excel doesn't have to convert anything. Thanks in advance Mark Johnson - Original Message - From: David Murray [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Tuesday, February 12, 2008 4:40 PM Subject: RE: [U2] Excel downloads Laurie, For simple totals, it is possible to add an EXCEL formula into a cell by starting the cell/formula with an '=' sign. This also works with CSV structured files, which EXCEL will load and convert on the fly. Create a virtual/correlative field which will simply output the formula as text and add it to the CSV file e.g. =SUM(An). Cheers, David Murray .learn and do .excel and share -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of IT-Laure Hansen Sent: Tuesday, February 12, 2008 12:10 PM To: u2-users@listserver.u2ug.org Subject: [U2] Excel downloads Hello, We are looking for a simple way to drop aggregate totals type reports out of Universe into Excel. Our users are using AccuTerm and we also have MITS Reports installed. But, neither one does the trick for summarized reports that use the DET-SUP option. Having the detail and forcing the users to do their own grouping/sub-totals in Excel is simply not an acceptable option here. UV 10.2.3 on Win2003 server; AccuTerm 2K2 re. 5.1. We also would be happy to develop a simple (key word...) VB.Net executable that works smoothly regardless of the version of Excel, as our users work on anything from Office 2000 and above. Any suggestions welcome. TIA, 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] mailto:[EMAIL PROTECTED] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Excel downloads
Try this: =CONCATENATE(,01234) For any values with leading zeroes. Marv Marvin R. Fisher Technical Resource Group A Pipeline Group Company 2850 Red Hill Ave. Suite 110 Santa Ana, CA 92705 Tel (949) 296-8380 ext. 620 Fax (949) 756-0029 Pipeline Software Statement: This email message is confidential and may be legally privileged. The contents contained within, including any attached files, are intended solely for the addressee(s). Access by anyone other than the addressee(s) is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action, taken or not taken, in reliance on it, is prohibited and may be unlawful. If you believe that you have received this email message in error, please contact the sender. Any views expressed within are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Pipeline-Software, Inc., Santa Ana, CA. (The contents of this email message have been scanned for the presence of computer viruses.) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MAJ Programming Sent: Wednesday, February 13, 2008 6:05 AM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Excel downloads On this topic, I have a request for a solution. In New Jersey, all the zip codes start with '0'. Add product codes like 012345 and order numbers like 000123 etc and you'll see the leading zeros. When creating and exporting a CSV to eventually be used by Excel, those columns lose their leading zeros as Excel thinks they are numeric. I know that the users can format column and choose Custom etc. I've converted the same CSV into a HTML boxed record and the zeros remain as expected. I've tried prepending the values with an astrophe (single quote) to no avail. The true data (as viewed in Notepad) is exactly as I sent it: 07748,012345,000123 So, any tricks in having Excel leave the zeros alone when clicking on the CSV in WIndows Explorer. Meaning, that the person opening the CSV in excel doesn't have to convert anything. Thanks in advance Mark Johnson - Original Message - From: David Murray [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Tuesday, February 12, 2008 4:40 PM Subject: RE: [U2] Excel downloads Laurie, For simple totals, it is possible to add an EXCEL formula into a cell by starting the cell/formula with an '=' sign. This also works with CSV structured files, which EXCEL will load and convert on the fly. Create a virtual/correlative field which will simply output the formula as text and add it to the CSV file e.g. =SUM(An). Cheers, David Murray .learn and do .excel and share -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of IT-Laure Hansen Sent: Tuesday, February 12, 2008 12:10 PM To: u2-users@listserver.u2ug.org Subject: [U2] Excel downloads Hello, We are looking for a simple way to drop aggregate totals type reports out of Universe into Excel. Our users are using AccuTerm and we also have MITS Reports installed. But, neither one does the trick for summarized reports that use the DET-SUP option. Having the detail and forcing the users to do their own grouping/sub-totals in Excel is simply not an acceptable option here. UV 10.2.3 on Win2003 server; AccuTerm 2K2 re. 5.1. We also would be happy to develop a simple (key word...) VB.Net executable that works smoothly regardless of the version of Excel, as our users work on anything from Office 2000 and above. Any suggestions welcome. TIA, 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] mailto:[EMAIL PROTECTED] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
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: u2-users@listserver.u2ug.org 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: u2-users@listserver.u2ug.org 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 u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Excel downloads
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: u2-users@listserver.u2ug.org 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: u2-users@listserver.u2ug.org 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: u2-users@listserver.u2ug.org 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 u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe
RE: [U2] Excel downloads [ad responding to specific points]
David wrote: Getting info into MS-Excel has always been an issue - careful use of the XML in U2 and MS-Excel may hold the key... Mmmm, worthy of an article... Karen wrote: Have you tried importing the column as text? Sometimes that works sometimes it doesn't. Excel is a very peculiar animal indeed. Folks, this is exactly why I created NebulaXLite. This new product allows developers to create real spreadsheets for Excel, Google, and OpenOffice, with full formatting, and without bothering with XML in Excel or U2 at all. NebulaXLite handles all of the XML complexities, so now getting info into MS-Excel is no longer the issue it has always been. Let's start by pulling CSV off the table. Ask your users what they do with the CSV files you give them, and how much time they spend formatting them afterward. If your users are just using the data to import into other sheets for pivot tables and charts, etc, then stick to the CSV files, they're easy and that's the proper solution for data-only requirements. If not, I invite you to download NebulaXLite and just look at the documentation and samples without even using it. Just know what's possible. NebulaXLite is completely free for non-production use and only $200 for an end-user server. How tough is that? To respond to the questions about zip codes: All you need to do is use Excel formatting with the code 0. You can also treat the data as a string using a single quote. CSV may have a problem with this but NebulaXLite doesn't because you can format numeric data as being text. Excel does provide a friendly warning in the form of a cell comment when it finds a number that is being treated like text. The user can simply select 'Ignore Warning' to eliminate this. The next build of NebulaXLite will have a small enhancement to avoid the warning when the developer does this. I hope that helps to clarify that what used to be difficult is no longer difficult. It pains me to see so many people still going through these issues with CSV when it's no longer necessary, and still talking about how difficult this can all be. Kind regards, Tony Gravagno Nebula Research and Development remove.pleaseNebula-RnD.com/products/xlite.htm remove.pleaseNebula-RnD.com/forum TG@ we.really.don't.like.spamNebula-RnD.com --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Excel downloads
Hmmm, a miracle tool for little or no money too? ;-) I do not know, if this meets your specific requirements, but... Have you looked at DOWNLOAD from http://www.cedarville.edu/departments/compserv/ftp.htm ? It's been around for a long, long time...no need to roll your own, when it's been done all before...originated on Prime INFORMATION, now on U2, complete with source code and supplied as is without any warranty. Documentation is here: ftp://ftp.cedarville.edu/download/download721/download.pdf. Regards, David -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of IT-Laure Hansen Sent: Thursday, February 14, 2008 4:01 AM To: u2-users@listserver.u2ug.org 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: u2-users@listserver.u2ug.org Subject: RE: [U2] Excel downloads What if the data fields contain quotes or double-quotes themselves? Does this handle that situation? --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Excel downloads
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of IT-Laure Hansen Sent: Tuesday, February 12, 2008 9:10 AM To: u2-users@listserver.u2ug.org Subject: [U2] Excel downloads Hello, We are looking for a simple way to drop aggregate totals type reports out of Universe into Excel. We write tab or semicolon delimited sequential files from UV to a shared directory and give the files .xls extensions. Excel treats them just like a standard spreadsheet. If the users want to get fancy with formatting or macros, they create another sheet and link to the xls file written by UV. -John --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Excel downloads
Hi Louie, You are correct, it does - however I am dealing among others with a set of files that contain about 85000 individual records: too much for Excel in the first place, and also, updating the MITS data source takes simply too much time, especially when adding break/sort fields. A simple Universe report using DET-SUP takes a couple of minutes to run. The equivalent using MITS Reports with a data source update to grab the latest data literally takes hours. So, I am looking for a solution that allows me to do the equivalent of a LIST DET-SUP in Universe, and convert the output into true Excel. 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 Louie Bergsagel Sent: Tuesday, February 12, 2008 3:36 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Excel downloads Laure, MITS Reports does export detail-suppressed information to an Excel spreadsheet. Just - click on column heading, then - click Display Totals, then - add a break on the field you want totaled. - click the Suppress Details tab - export to Excel Louie Bergsagel North Coast Electric --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Excel downloads
What about writing a standard BASIC report that would prepare CSV lines for the subtotals (DET-SUPP). Many reports I create offer S=Screen, P=Printer and/or D=Download output choices. Same lines, different destination. I'll re-iterate one of TonyG's suggestions with creating and eventually exporting a CSV. Unless you are using WRITESEQ, having a large CSV 'record' inside a program may encroach on some speed and/or handling problems. Tony's suggestion was to have PRINTER ON and under the Hold and Suppress option, 'print' each row instead of X-1 appending. When you are complete, manage the HOLD record accordingly. I use this method when the resulting CSV (or EDI as well) record may be over 100,000 rows. Otherwise X-1 works acceptably. Actual mileage may differ. My 1 cent Mark Johnson - Original Message - From: IT-Laure Hansen [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Tuesday, February 12, 2008 7:32 PM Subject: RE: [U2] Excel downloads Hi Louie, You are correct, it does - however I am dealing among others with a set of files that contain about 85000 individual records: too much for Excel in the first place, and also, updating the MITS data source takes simply too much time, especially when adding break/sort fields. A simple Universe report using DET-SUP takes a couple of minutes to run. The equivalent using MITS Reports with a data source update to grab the latest data literally takes hours. So, I am looking for a solution that allows me to do the equivalent of a LIST DET-SUP in Universe, and convert the output into true Excel. 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 Louie Bergsagel Sent: Tuesday, February 12, 2008 3:36 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Excel downloads Laure, MITS Reports does export detail-suppressed information to an Excel spreadsheet. Just - click on column heading, then - click Display Totals, then - add a break on the field you want totaled. - click the Suppress Details tab - export to Excel Louie Bergsagel North Coast Electric --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Excel downloads
Laure, How about XML? You can take the output of a LIST/SORT and add TOXML at the end to generate XML. Then print to a HOLD file (HOLD), pick it up and import into MS-Excel. MS-Excel will import XML in a number of forms, including as a sort-able list. It you setup a schema at both ends, then you should be able to get the exact format that you want. Getting info into MS-Excel has always been an issue - careful use of the XML in U2 and MS-Excel may hold the key... Mmmm, worthy of an article... Cheers, David Murray .learn and do .excel and share -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of IT-Laure Hansen Sent: Tuesday, February 12, 2008 7:32 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] Excel downloads Hi Louie, You are correct, it does - however I am dealing among others with a set of files that contain about 85000 individual records: too much for Excel in the first place, and also, updating the MITS data source takes simply too much time, especially when adding break/sort fields. A simple Universe report using DET-SUP takes a couple of minutes to run. The equivalent using MITS Reports with a data source update to grab the latest data literally takes hours. So, I am looking for a solution that allows me to do the equivalent of a LIST DET-SUP in Universe, and convert the output into true Excel. 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 Louie Bergsagel Sent: Tuesday, February 12, 2008 3:36 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Excel downloads Laure, MITS Reports does export detail-suppressed information to an Excel spreadsheet. Just - click on column heading, then - click Display Totals, then - add a break on the field you want totaled. - click the Suppress Details tab - export to Excel Louie Bergsagel North Coast Electric --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Excel downloads
Louie, import the report on universe into excel and either run a macro to stip out the headings or suppress them jak - Original Message - From: IT-Laure Hansen [EMAIL PROTECTED] To: u2-users@listserver.u2ug.org Sent: Wednesday, February 13, 2008 11:32 AM Subject: RE: [U2] Excel downloads Hi Louie, You are correct, it does - however I am dealing among others with a set of files that contain about 85000 individual records: too much for Excel in the first place, and also, updating the MITS data source takes simply too much time, especially when adding break/sort fields. A simple Universe report using DET-SUP takes a couple of minutes to run. The equivalent using MITS Reports with a data source update to grab the latest data literally takes hours. So, I am looking for a solution that allows me to do the equivalent of a LIST DET-SUP in Universe, and convert the output into true Excel. 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 Louie Bergsagel Sent: Tuesday, February 12, 2008 3:36 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Excel downloads Laure, MITS Reports does export detail-suppressed information to an Excel spreadsheet. Just - click on column heading, then - click Display Totals, then - add a break on the field you want totaled. - click the Suppress Details tab - export to Excel Louie Bergsagel North Coast Electric --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Excel downloads [ad]
NebulaXLite was developed to solve exactly the problem that you describe. Please feel free to download and use a free trial: remove.pleaseNebula-RnD.com/products/xlite.htm For Excel back into U2, we have another option coming soon. The difference between this and the AccuTerm option is that (1) you don't need AccuTerm on the client or anywhere else, it can be used by anyone including your trading partners, and (2) it uses web services rather than telnet so it can be used anywhere, over the internet, from home, etc. For more info, see the link to NebulaXChange from the NebulaXLite download page. Tony Gravagno Nebula Research and Development TG@ remove.pleaseNebula-RnD.com Laure wrote: We are looking for a simple way to drop aggregate totals type reports out of Universe into Excel. Our users are using AccuTerm and we also have MITS Reports installed. But, neither one does the trick for summarized reports that use the DET-SUP option. Having the detail and forcing the users to do their own grouping/sub-totals in Excel is simply not an acceptable option here. UV 10.2.3 on Win2003 server; AccuTerm 2K2 re. 5.1. We also would be happy to develop a simple (key word...) VB.Net executable that works smoothly regardless of the version of Excel, as our users work on anything from Office 2000 and above. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Excel downloads
I use Accuterm FTD to create excel files and then run scripts via Accuterm (PRINT ESC : STX : 'P' : SCRIPT : CR :) to create pivot tables for some of my users. Bruce Bruce M Neylon Health Care Management Group IT-Laure Hansen [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/12/2008 12:10 PM Please respond to u2-users@listserver.u2ug.org To u2-users@listserver.u2ug.org cc Subject [U2] Excel downloads Hello, We are looking for a simple way to drop aggregate totals type reports out of Universe into Excel. Our users are using AccuTerm and we also have MITS Reports installed. But, neither one does the trick for summarized reports that use the DET-SUP option. Having the detail and forcing the users to do their own grouping/sub-totals in Excel is simply not an acceptable option here. UV 10.2.3 on Win2003 server; AccuTerm 2K2 re. 5.1. We also would be happy to develop a simple (key word...) VB.Net executable that works smoothly regardless of the version of Excel, as our users work on anything from Office 2000 and above. Any suggestions welcome. TIA, 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] mailto:[EMAIL PROTECTED] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Excel downloads
If this is a one-off type of job*, sometimes the simplest thing to do is capture the screen output as a .txt file, then import to excel as fixed length fields. Quick and dirty? Yes, but his would work pretty well for your detail-suppress requirement. I use BREAK-ON fieldname 'L' to remove the extra blank lines between rows. *Actually, I have used this for regular, repeating jobs, by scripting accuterm to capture the screen output as a specific filename in a specific directory, then printing instructions on the screen telling the user how to import into excel. /Scott Ballinger Pareto Corporation Edmonds WA USA 206 713 6006 /Scott Ballinger On Feb 12, 2008 4:32 PM, IT-Laure Hansen [EMAIL PROTECTED] wrote: Hi Louie, You are correct, it does - however I am dealing among others with a set of files that contain about 85000 individual records: too much for Excel in the first place, and also, updating the MITS data source takes simply too much time, especially when adding break/sort fields. A simple Universe report using DET-SUP takes a couple of minutes to run. The equivalent using MITS Reports with a data source update to grab the latest data literally takes hours. So, I am looking for a solution that allows me to do the equivalent of a LIST DET-SUP in Universe, and convert the output into true Excel. 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] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Excel downloads
Laurie, For simple totals, it is possible to add an EXCEL formula into a cell by starting the cell/formula with an '=' sign. This also works with CSV structured files, which EXCEL will load and convert on the fly. Create a virtual/correlative field which will simply output the formula as text and add it to the CSV file e.g. =SUM(An). Cheers, David Murray .learn and do .excel and share -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of IT-Laure Hansen Sent: Tuesday, February 12, 2008 12:10 PM To: u2-users@listserver.u2ug.org Subject: [U2] Excel downloads Hello, We are looking for a simple way to drop aggregate totals type reports out of Universe into Excel. Our users are using AccuTerm and we also have MITS Reports installed. But, neither one does the trick for summarized reports that use the DET-SUP option. Having the detail and forcing the users to do their own grouping/sub-totals in Excel is simply not an acceptable option here. UV 10.2.3 on Win2003 server; AccuTerm 2K2 re. 5.1. We also would be happy to develop a simple (key word...) VB.Net executable that works smoothly regardless of the version of Excel, as our users work on anything from Office 2000 and above. Any suggestions welcome. TIA, 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] mailto:[EMAIL PROTECTED] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Excel downloads
Thank you, John. 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 John Hester Sent: Tuesday, February 12, 2008 11:42 AM To: u2-users@listserver.u2ug.org Subject: RE: [U2] Excel downloads -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of IT-Laure Hansen Sent: Tuesday, February 12, 2008 9:10 AM To: u2-users@listserver.u2ug.org Subject: [U2] Excel downloads Hello, We are looking for a simple way to drop aggregate totals type reports out of Universe into Excel. We write tab or semicolon delimited sequential files from UV to a shared directory and give the files .xls extensions. Excel treats them just like a standard spreadsheet. If the users want to get fancy with formatting or macros, they create another sheet and link to the xls file written by UV. -John --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Excel downloads
Hi Laure, I used a simple technique for this a while back. Create a program and EXECUTE the retrieve statement and CAPTURE the output. If you add Char(9) to a dict and place this between each field excel will then split it in to columns. EG SORT FILE BY FIELD1 BREAK.ON FIELD1 TAB TOTAL 1 TAB TOTAL 2 DET.SUP Then simply email the output array to the user. If you add html to the array then you can even format the text so it looks better when excel opens it, but this can slow things down as you need to insert table/tr/td into the array. Re andy Legal Disclaimer: The information contained in this message may be privileged and confidential. It is intended to be read only by the individual or entity to whom it is addressed or by their designee. If the reader of this message is not the intended recipient, you are on notice that any distribution of this message, in any form, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete or destroy any copy of this message --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
