Re: Converting Text to XLS
I would give them an Excel template (.XLT) with all the necessary headings formats in and ask them (nicely) to import the CSV data into it rather than just opening it with Excel. The template could also hold any fancy formulae etc. they want to use on the data, and would not involve much work. Regards, Peter Borton From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: 20 April 2010 18:27 To: IBMVM@LISTSERV.UARK.EDU Subject: Converting Text to XLS Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh
Recall: Converting Text to XLS
Peter Borton would like to recall the message, Converting Text to XLS.
Re: Converting Text to XLS
I would give them an Excel template (.XLT) with all the necessary headings formats in and ask them (nicely) to import the CSV data into it rather than just opening it with Excel. The template could also hold any fancy formulae etc. they want to use on the data. Regards, Peter Borton From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: 20 April 2010 18:27 To: IBMVM@LISTSERV.UARK.EDU Subject: Converting Text to XLS Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh
Re: Converting Text to XLS
Exactly, with a distributed macro (for instance) you can pull in the CSV file adjust the columns, center headings, change fonts, etc. Frank M. Ramaekers Jr. From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 4:27 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS I want them to have a consistent view when it is received. I don't give a damn what they do with it afterward. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Frank M. Ramaekers Sent: Tuesday, April 20, 2010 12:28 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS ...and you don't want to have to futz with Excel macros or scripts? Frank M. Ramaekers Jr. From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 1:19 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS Right now, I am creating a csv file using tab (x'05', which is also the default field separator in Pipelines) characters as a separator. Since I do not have control of the width of cells, I need to be able to selectively specify whether to align right or left and top or bottom in the cells of a column. I suppose I could use a multiple pass process where I get the max size of a cell in each column and create fixed size cells and align things myself. Then, there are such things as table and column headings and anything else needed to make it print friendly. Even then, the output would still be a text file. not xls. The short of it is, I am trying to make it so that the recipient does not have to futz with formats or headings when the file is opened by Excel. I have seen some really strange formats when looking at files that people (in ISO, but I will give them the benefit of the doubt) have messed up. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Wandschneider, Scott Sent: Tuesday, April 20, 2010 10:40 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS I create several text files on CMS where I use the or character as a delimiter and surround special text with . Thank you, Scott From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 12:27 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Converting Text to XLS Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh Confidentiality Note: This e-mail, including any attachment to it, may contain material that is confidential, proprietary, privileged and/or Protected Health Information, within the meaning of the regulations under the Health Insurance Portability Accountability Act as amended. If it is not clear that you are the intended recipient, you are hereby notified that you have received this transmittal in error, and any review, dissemination, distribution or copying of this e-mail, including any attachment to it, is strictly prohibited. If you have received this e-mail in error, please immediately return it to the sender and delete it from your system. Thank you. _ This message contains information which is privileged and confidential and is solely for the use of the intended recipient. If you are not the intended recipient, be aware that any review, disclosure, copying, distribution, or use of the contents of this message is strictly prohibited. If you have received this in error, please destroy it immediately and notify us at privacy...@ailife.com. _ This message contains information which is privileged and confidential and is solely for the use of the intended recipient. If you are not the intended recipient, be aware that any review, disclosure, copying
Re: Converting Text to XLS
Richard, I think the easiest thing to do now is simply upgrade your users... On 04/21/2010 10:33 AM, Schuh, Richard wrote: I often send CSV files with a .txt extension. I use a TAB X'05' as a delimiter which is the default for for .txt files. That is what I have been providing for them. It makes no difference whether the file is banana.txt or banana.csv, it will be opened properly by Excel. Only one thing for the user to do: Select all, click Format - Column - Autofit selection. I get no complaint from user about having to do this format. That is something that the people who receive my files cannot seem to remember and cannot find in the instructions that were provided when they were actually transferring the files themselves, so I get complaints. If I do not find a way to handle that in all of the suggestions and code provided because of this thread, I will simply have to add the instructions to the e-mail when I send it. /Fran Hensler at Slippery Rock University of Pennsylvania USA for 46 years mailto:f...@zvm.sru.edu http://zvm.sru.edu/~fjh +1.724.738.2153 Yes, Virginia, there is a Slippery Rock -- Regards, Richard Schuh -- Dave Jones V/Soft www.vsoft-software.com Houston, TX 281.578.7544
Re: Converting Text to XLS
That is what I have been providing for them. It makes no difference whether the file is banana.txt or banana.csv, it will be opened properly by Excel. As much as I dislike dealing with XML in general, the XML suggestion is probably the best one so far. That would let you generate both data and formatting in one pass, and it should Just Work for J Random Luser. Create the sheet the way you want it to look once in Excel, and then Save, open with a text editor, and chop out the data bits. Also would work beautifully for OpenOffice; the structure should be pretty similar. I wish someone would port Xerces to CMS. Then we'd have a standard XML library to work with, and writing a pipe stage to generate stuff would be easy. -- db
Re: Converting Text to XLS
I think you might have a solution there. A canned email or even an attachment with instructions. /Fran On Wed, 21 Apr 2010 08:33:54 -0700 Schuh, Richard said: the files themselves, so I get complaints. If I do not find a way to handle that in all of the suggestions and code provided because of this thread, I will simply have to add the instructions to the e-mail when I send it.
Re: Converting Text to XLS
Richard, just yesterday I finished working on a REXX program that generat es CSV, HTML, or XML for a simple spreadsheet. If you're interested, I can ask my employer whether I can give it to you. You'd have to cannibalize it, since it does not read a text file; it generates its own data by reading application-specific files. It has a layer of calls (open, start table, start row, td, etc.) and below that ar e the abstraction layers for the different formats. The XML can handle centering, column widths, and some cell highlighting (bold red, in this case), but you could change it if you're up on XML, or by doing what I di d: I don't know XML, by the way. I did the whole thing by starting with a spreadsheet in Excel, saving it as XML, and taking it apart.
Re: Converting Text to XLS
Need I remind you that they are in the InfoSec group. :-) Regards, Richard Schuh -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Dave Jones Sent: Wednesday, April 21, 2010 8:42 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS Richard, I think the easiest thing to do now is simply upgrade your users... On 04/21/2010 10:33 AM, Schuh, Richard wrote: I often send CSV files with a .txt extension. I use a TAB X'05' as a delimiter which is the default for for .txt files. That is what I have been providing for them. It makes no difference whether the file is banana.txt or banana.csv, it will be opened properly by Excel. Only one thing for the user to do: Select all, click Format - Column - Autofit selection. I get no complaint from user about having to do this format. That is something that the people who receive my files cannot seem to remember and cannot find in the instructions that were provided when they were actually transferring the files themselves, so I get complaints. If I do not find a way to handle that in all of the suggestions and code provided because of this thread, I will simply have to add the instructions to the e-mail when I send it. /Fran Hensler at Slippery Rock University of Pennsylvania USA for 46 years mailto:f...@zvm.sru.edu http://zvm.sru.edu/~fjh +1.724.738.2153 Yes, Virginia, there is a Slippery Rock -- Regards, Richard Schuh -- Dave Jones V/Soft www.vsoft-software.com Houston, TX 281.578.7544
Re: Converting Text to XLS
I am not up on XML, but may have to get there ... assuming that there is enough time in the day. (A big assumption considering the current work load.) Regards, Richard Schuh -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Alan Winson Sent: Wednesday, April 21, 2010 8:59 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS Richard, just yesterday I finished working on a REXX program that generat= es CSV, HTML, or XML for a simple spreadsheet. If you're interested, I can = ask my employer whether I can give it to you. You'd have to cannibalize it, since it does not read a text file; it generates its own data by reading application-specific files. It has a layer of calls (open, start table, start row, td, etc.) and below that ar= e the abstraction layers for the different formats. The XML can handle centering, column widths, and some cell highlighting (bold red, in this case), but you could change it if you're up on XML, or by doing what I di= d: I don't know XML, by the way. I did the whole thing by starting with a spreadsheet in Excel, saving it as XML, and taking it apart.
Converting Text to XLS
Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh
Re: Converting Text to XLS
I create several text files on CMS where I use the or character as a delimiter and surround special text with . Thank you, Scott From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 12:27 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Converting Text to XLS Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh Confidentiality Note: This e-mail, including any attachment to it, may contain material that is confidential, proprietary, privileged and/or Protected Health Information, within the meaning of the regulations under the Health Insurance Portability Accountability Act as amended. If it is not clear that you are the intended recipient, you are hereby notified that you have received this transmittal in error, and any review, dissemination, distribution or copying of this e-mail, including any attachment to it, is strictly prohibited. If you have received this e-mail in error, please immediately return it to the sender and delete it from your system. Thank you.
Re: Converting Text to XLS
Another possibility is the symbolic link format file (SYLK). .
Re: Converting Text to XLS
Right now, I am creating a csv file using tab (x'05', which is also the default field separator in Pipelines) characters as a separator. Since I do not have control of the width of cells, I need to be able to selectively specify whether to align right or left and top or bottom in the cells of a column. I suppose I could use a multiple pass process where I get the max size of a cell in each column and create fixed size cells and align things myself. Then, there are such things as table and column headings and anything else needed to make it print friendly. Even then, the output would still be a text file. not xls. The short of it is, I am trying to make it so that the recipient does not have to futz with formats or headings when the file is opened by Excel. I have seen some really strange formats when looking at files that people (in ISO, but I will give them the benefit of the doubt) have messed up. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Wandschneider, Scott Sent: Tuesday, April 20, 2010 10:40 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS I create several text files on CMS where I use the or character as a delimiter and surround special text with . Thank you, Scott From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 12:27 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Converting Text to XLS Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh Confidentiality Note: This e-mail, including any attachment to it, may contain material that is confidential, proprietary, privileged and/or Protected Health Information, within the meaning of the regulations under the Health Insurance Portability Accountability Act as amended. If it is not clear that you are the intended recipient, you are hereby notified that you have received this transmittal in error, and any review, dissemination, distribution or copying of this e-mail, including any attachment to it, is strictly prohibited. If you have received this e-mail in error, please immediately return it to the sender and delete it from your system. Thank you.
Re: Converting Text to XLS
Are there programs to do that conversion in CMS? I am not familiar with the SYLK format. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Stace Cameron Sent: Tuesday, April 20, 2010 10:57 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS Another possibility is the symbolic link format file (SYLK). .
Re: Converting Text to XLS
You would have to ask the ISO folks. That is what they specified. I suspect they want to play with it using secret macros. Regards, Richard Schuh -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Ron Schmiedge Sent: Tuesday, April 20, 2010 11:27 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS Richard, I've a dumb question to ask - why does the CMS file have to be opened by a spreadsheet program? On Tue, Apr 20, 2010 at 12:19 PM, Schuh, Richard rsc...@visa.com wrote: Right now, I am creating a csv file using tab (x'05', which is also the default field separator in Pipelines) characters as a separator. Since I do not have control of the width of cells, I need to be able to selectively specify whether to align right or left and top or bottom in the cells of a column. I suppose I could use a multiple pass process where I get the max size of a cell in each column and create fixed size cells and align things myself. Then, there are such things as table and column headings and anything else needed to make it print friendly. Even then, the output would still be a text file. not xls. The short of it is, I am trying to make it so that the recipient does not have to futz with formats or headings when the file is opened by Excel. I have seen some really strange formats when looking at files that people (in ISO, but I will give them the benefit of the doubt) have messed up. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Wandschneider, Scott Sent: Tuesday, April 20, 2010 10:40 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS I create several text files on CMS where I use the or character as a delimiter and surround special text with . Thank you, Scott From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 12:27 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Converting Text to XLS Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh
Re: Converting Text to XLS
I certainly do not have Java on CMS :-) I do have Rexx, Pipelines and HLASM. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of McKown, John Sent: Tuesday, April 20, 2010 11:31 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS What languages do you have available? If you have a current Java, then I'd suggest looking at Apache:POI http://poi.apache.org/ John McKown Systems Engineer IV IT Administrative Services Group HealthMarkets(r) 9151 Boulevard 26 * N. Richland Hills * TX 76010 (817) 255-3225 phone * (817)-961-6183 cell john.mck...@healthmarkets.com * www.HealthMarkets.com Confidentiality Notice: This e-mail message may contain confidential or proprietary information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. HealthMarkets(r) is the brand name for products underwritten and issued by the insurance subsidiaries of HealthMarkets, Inc. -The Chesapeake Life Insurance Company(r), Mid-West National Life Insurance Company of TennesseeSM and The MEGA Life and Health Insurance Company.SM From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 1:19 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS Right now, I am creating a csv file using tab (x'05', which is also the default field separator in Pipelines) characters as a separator. Since I do not have control of the width of cells, I need to be able to selectively specify whether to align right or left and top or bottom in the cells of a column. I suppose I could use a multiple pass process where I get the max size of a cell in each column and create fixed size cells and align things myself. Then, there are such things as table and column headings and anything else needed to make it print friendly. Even then, the output would still be a text file. not xls. The short of it is, I am trying to make it so that the recipient does not have to futz with formats or headings when the file is opened by Excel. I have seen some really strange formats when looking at files that people (in ISO, but I will give them the benefit of the doubt) have messed up. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Wandschneider, Scott Sent: Tuesday, April 20, 2010 10:40 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS I create several text files on CMS where I use the or character as a delimiter and surround special text with . Thank you, Scott From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 12:27 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Converting Text to XLS Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh Confidentiality Note: This e-mail, including any attachment to it, may contain material that is confidential, proprietary, privileged and/or Protected Health Information, within the meaning of the regulations under the Health Insurance Portability Accountability Act as amended. If it is not clear that you are the intended recipient, you are hereby notified that you have received this transmittal in error, and any review, dissemination, distribution or copying of this e-mail, including any attachment to it, is strictly prohibited. If you have received this e-mail in error, please immediately return it to the sender and delete it from your system. Thank you.
Re: Converting Text to XLS
Well if you have money to spend on this, Focus from Information Builders runs on CMS and can save output as HTML, HTMTABLE, PDF, PS, ALPHA, WP, DIF, EXCEL, EXL97, EXL2K, LOTUS, SYLK, TAB, TABT, COMMA, COM, COMT, DB2, FUSION, SQL, SQLORA, SQLDBC formats. I have tried a couple of these, and they do work. Peter -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: April 20, 2010 14:22 To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS Are there programs to do that conversion in CMS? I am not familiar with the SYLK format. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Stace Cameron Sent: Tuesday, April 20, 2010 10:57 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS Another possibility is the symbolic link format file (SYLK). . The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review retransmission dissemination or other use of or taking any action in reliance upon this information by persons or entities other than the intended recipient or delegate is strictly prohibited. If you received this in error please contact the sender and delete the material from any computer. The integrity and security of this message cannot be guaranteed on the Internet. The sender accepts no liability for the content of this e-mail or for the consequences of any actions taken on the basis of information provided. The recipient should check this e-mail and any attachments for the presence of viruses. The sender accepts no liability for any damage caused by any virus transmitted by this e-mail. This disclaimer is property of the TTC and must not be altered or circumvented in any manner.
Re: Converting Text to XLS
You could use OORexx and its support for OLE to automatically fix the inconsistencies. Les Schuh, Richard wrote: Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh
Re: Converting Text to XLS
...and you don't want to have to futz with Excel macros or scripts? Frank M. Ramaekers Jr. From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 1:19 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS Right now, I am creating a csv file using tab (x'05', which is also the default field separator in Pipelines) characters as a separator. Since I do not have control of the width of cells, I need to be able to selectively specify whether to align right or left and top or bottom in the cells of a column. I suppose I could use a multiple pass process where I get the max size of a cell in each column and create fixed size cells and align things myself. Then, there are such things as table and column headings and anything else needed to make it print friendly. Even then, the output would still be a text file. not xls. The short of it is, I am trying to make it so that the recipient does not have to futz with formats or headings when the file is opened by Excel. I have seen some really strange formats when looking at files that people (in ISO, but I will give them the benefit of the doubt) have messed up. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Wandschneider, Scott Sent: Tuesday, April 20, 2010 10:40 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS I create several text files on CMS where I use the or character as a delimiter and surround special text with . Thank you, Scott From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 12:27 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Converting Text to XLS Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh Confidentiality Note: This e-mail, including any attachment to it, may contain material that is confidential, proprietary, privileged and/or Protected Health Information, within the meaning of the regulations under the Health Insurance Portability Accountability Act as amended. If it is not clear that you are the intended recipient, you are hereby notified that you have received this transmittal in error, and any review, dissemination, distribution or copying of this e-mail, including any attachment to it, is strictly prohibited. If you have received this e-mail in error, please immediately return it to the sender and delete it from your system. Thank you. _ This message contains information which is privileged and confidential and is solely for the use of the intended recipient. If you are not the intended recipient, be aware that any review, disclosure, copying, distribution, or use of the contents of this message is strictly prohibited. If you have received this in error, please destroy it immediately and notify us at privacy...@ailife.com.
Re: Converting Text to XLS
I want them to have a consistent view when it is received. I don't give a damn what they do with it afterward. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Frank M. Ramaekers Sent: Tuesday, April 20, 2010 12:28 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS ...and you don't want to have to futz with Excel macros or scripts? Frank M. Ramaekers Jr. From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 1:19 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS Right now, I am creating a csv file using tab (x'05', which is also the default field separator in Pipelines) characters as a separator. Since I do not have control of the width of cells, I need to be able to selectively specify whether to align right or left and top or bottom in the cells of a column. I suppose I could use a multiple pass process where I get the max size of a cell in each column and create fixed size cells and align things myself. Then, there are such things as table and column headings and anything else needed to make it print friendly. Even then, the output would still be a text file. not xls. The short of it is, I am trying to make it so that the recipient does not have to futz with formats or headings when the file is opened by Excel. I have seen some really strange formats when looking at files that people (in ISO, but I will give them the benefit of the doubt) have messed up. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Wandschneider, Scott Sent: Tuesday, April 20, 2010 10:40 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS I create several text files on CMS where I use the or character as a delimiter and surround special text with . Thank you, Scott From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 12:27 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Converting Text to XLS Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh Confidentiality Note: This e-mail, including any attachment to it, may contain material that is confidential, proprietary, privileged and/or Protected Health Information, within the meaning of the regulations under the Health Insurance Portability Accountability Act as amended. If it is not clear that you are the intended recipient, you are hereby notified that you have received this transmittal in error, and any review, dissemination, distribution or copying of this e-mail, including any attachment to it, is strictly prohibited. If you have received this e-mail in error, please immediately return it to the sender and delete it from your system. Thank you. _ This message contains information which is privileged and confidential and is solely for the use of the intended recipient. If you are not the intended recipient, be aware that any review, disclosure, copying, distribution, or use of the contents of this message is strictly prohibited. If you have received this in error, please destroy it immediately and notify us at privacy...@ailife.com.
Re: Converting Text to XLS
On Tue, 20 Apr 2010 10:27:09 -0700 Schuh, Richard said: Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. I have a QD Rexx exec that was work I did to create an XML file for use in Excel. It opens just fine in Excel 2007 higher, and I think in 2003 with the the 2007 compatibility patch. This is not a clean program, no doc and some options are coded as variables rather than options, so they were real work in progress. There are 2 versions, a less developed turn a fixed length file into XML, and a version you pass a DB2 select statement, and it turns the resulting output into an XML file. eg DB2XML SELECT * from mytable will turn the table mytable into an XML file. The DB2 version is much more flushed out, but probably has Marist specific things in it. They both are very much early works in progress. If you are interested, I can send you either or both of them. Regards, Richard Schuh /ahw
Re: Converting Text to XLS
I've done some work with text to csv for z/os and never found a way to get the csv to open in a decent view in excel as a csv is the lowest common format. I did find that you can create an html table that can be opened in excel - take a look at www.lbdsoftware.com for the txt2html file - that code is in rexx but I've not taken the time to make it work under z/vm (feel free). It has the ability to do what you want to a degree. Good luck Lionel Lionel B. Dyck z/Linux Specialist IBM Corporation Global Technology Services - Kaiser Account Work: 925-926-5332 Cell: 925-348-0237 E-Mail: ld...@us.ibm.com AIM: lbdyck | Yahoo IM: lbdyck | GTalk: lbdyck From: Schuh, Richard rsc...@visa.com To: IBMVM@LISTSERV.UARK.EDU Date: 04/20/2010 02:28 PM Subject:Re: Converting Text to XLS Sent by:The IBM z/VM Operating System IBMVM@LISTSERV.UARK.EDU I want them to have a consistent view when it is received. I don't give a damn what they do with it afterward. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Frank M. Ramaekers Sent: Tuesday, April 20, 2010 12:28 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS …and you don’t want to have to “futz” with Excel macros or scripts? Frank M. Ramaekers Jr. From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 1:19 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS Right now, I am creating a csv file using tab (x'05', which is also the default field separator in Pipelines) characters as a separator. Since I do not have control of the width of cells, I need to be able to selectively specify whether to align right or left and top or bottom in the cells of a column. I suppose I could use a multiple pass process where I get the max size of a cell in each column and create fixed size cells and align things myself. Then, there are such things as table and column headings and anything else needed to make it print friendly. Even then, the output would still be a text file. not xls. The short of it is, I am trying to make it so that the recipient does not have to futz with formats or headings when the file is opened by Excel. I have seen some really strange formats when looking at files that people (in ISO, but I will give them the benefit of the doubt) have messed up. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Wandschneider, Scott Sent: Tuesday, April 20, 2010 10:40 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS I create several text files on CMS where I use the or character as a delimiter and surround special text with “. Thank you, Scott From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 12:27 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Converting Text to XLS Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh Confidentiality Note: This e-mail, including any attachment to it, may contain material that is confidential, proprietary, privileged and/or Protected Health Information, within the meaning of the regulations under the Health Insurance Portability Accountability Act as amended. If it is not clear that you are the intended recipient, you are hereby notified that you have received this transmittal in error, and any review, dissemination, distribution or copying of this e-mail, including any attachment to it, is strictly
Re: Converting Text to XLS
Thanks to all who have been responding. I should have enough the keep me busy, er, busier. I will post when I have the solution or if I don't find one among the many suggestions. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Lionel Dyck Sent: Tuesday, April 20, 2010 2:38 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS I've done some work with text to csv for z/os and never found a way to get the csv to open in a decent view in excel as a csv is the lowest common format. I did find that you can create an html table that can be opened in excel - take a look at www.lbdsoftware.com for the txt2html file - that code is in rexx but I've not taken the time to make it work under z/vm (feel free). It has the ability to do what you want to a degree. Good luck Lionel Lionel B. Dyck z/Linux Specialist IBM Corporation Global Technology Services - Kaiser Account Work: 925-926-5332 Cell: 925-348-0237 E-Mail: ld...@us.ibm.com AIM: lbdyck | Yahoo IM: lbdyck | GTalk: lbdyck http://www.ibm.com/ibm/values/ [cid:1__=07BBFD98DFE510FF8f9e8a93df938@us.ibm.com]Schuh, Richard ---04/20/2010 02:28:40 PM---I want them to have a consistent view when it is received. I don't give a damn what they do with it From: Schuh, Richard rsc...@visa.com To: IBMVM@LISTSERV.UARK.EDU Date: 04/20/2010 02:28 PM Subject: Re: Converting Text to XLS Sent by: The IBM z/VM Operating System IBMVM@LISTSERV.UARK.EDU I want them to have a consistent view when it is received. I don't give a damn what they do with it afterward. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Frank M. Ramaekers Sent: Tuesday, April 20, 2010 12:28 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS ...and you don't want to have to futz with Excel macros or scripts? Frank M. Ramaekers Jr. From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 1:19 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS Right now, I am creating a csv file using tab (x'05', which is also the default field separator in Pipelines) characters as a separator. Since I do not have control of the width of cells, I need to be able to selectively specify whether to align right or left and top or bottom in the cells of a column. I suppose I could use a multiple pass process where I get the max size of a cell in each column and create fixed size cells and align things myself. Then, there are such things as table and column headings and anything else needed to make it print friendly. Even then, the output would still be a text file. not xls. The short of it is, I am trying to make it so that the recipient does not have to futz with formats or headings when the file is opened by Excel. I have seen some really strange formats when looking at files that people (in ISO, but I will give them the benefit of the doubt) have messed up. Regards, Richard Schuh From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Wandschneider, Scott Sent: Tuesday, April 20, 2010 10:40 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS I create several text files on CMS where I use the or character as a delimiter and surround special text with . Thank you, Scott From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 12:27 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Converting Text to XLS Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh Confidentiality Note: This e-mail, including any attachment to it, may contain material that is confidential, proprietary, privileged and/or Protected Health Information, within the meaning of the regulations under the Health Insurance Portability Accountability Act as amended. If it is not clear that you are the intended recipient, you are hereby notified that you have received this transmittal in error, and any review, dissemination, distribution or copying of this e-mail, including any attachment to it, is strictly prohibited. If you have received this e-mail in error, please immediately return it to the sender and delete it from your system. Thank you. _ This message contains information which is privileged and confidential and is solely for the use of the intended
Re: Converting Text to XLS
Why Excel then? What about HTML, Postscript or PDF? Or to be really devious send them a REXX Script that runs in Regina, creates a spread sheet and then loads data in using OLE/COM/Active X. Kind of like using Windows as a batch processor Dave G4UGM Illegitimi Non Carborundum -Original Message- From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: 20 April 2010 22:27 To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS I want them to have a consistent view when it is received. I don't give a damn what they do with it afterward. Regards, Richard Schuh _ From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Frank M. Ramaekers Sent: Tuesday, April 20, 2010 12:28 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS .and you don't want to have to futz with Excel macros or scripts? Frank M. Ramaekers Jr. _ From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 1:19 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS Right now, I am creating a csv file using tab (x'05', which is also the default field separator in Pipelines) characters as a separator. Since I do not have control of the width of cells, I need to be able to selectively specify whether to align right or left and top or bottom in the cells of a column. I suppose I could use a multiple pass process where I get the max size of a cell in each column and create fixed size cells and align things myself. Then, there are such things as table and column headings and anything else needed to make it print friendly. Even then, the output would still be a text file. not xls. The short of it is, I am trying to make it so that the recipient does not have to futz with formats or headings when the file is opened by Excel. I have seen some really strange formats when looking at files that people (in ISO, but I will give them the benefit of the doubt) have messed up. Regards, Richard Schuh _ From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Wandschneider, Scott Sent: Tuesday, April 20, 2010 10:40 AM To: IBMVM@LISTSERV.UARK.EDU Subject: Re: Converting Text to XLS I create several text files on CMS where I use the or character as a delimiter and surround special text with . Thank you, Scott From: The IBM z/VM Operating System [mailto:ib...@listserv.uark.edu] On Behalf Of Schuh, Richard Sent: Tuesday, April 20, 2010 12:27 PM To: IBMVM@LISTSERV.UARK.EDU Subject: Converting Text to XLS Does anyone know of a program to create an XLS file from a text file on CMS? The file can be sent as a CSV file attached to an e-mail; however, opening the CSV file with Excel or other spreadsheet programs exposes a few formatting problems/inconsistencies that must be fixed by manual manipulation. Regards, Richard Schuh Confidentiality Note: This e-mail, including any attachment to it, may contain material that is confidential, proprietary, privileged and/or Protected Health Information, within the meaning of the regulations under the Health Insurance Portability Accountability Act as amended. If it is not clear that you are the intended recipient, you are hereby notified that you have received this transmittal in error, and any review, dissemination, distribution or copying of this e-mail, including any attachment to it, is strictly prohibited. If you have received this e-mail in error, please immediately return it to the sender and delete it from your system. Thank you. _ This message contains information which is privileged and confidential and is solely for the use of the intended recipient. If you are not the intended recipient, be aware that any review, disclosure, copying, distribution, or use of the contents of this message is strictly prohibited. If you have received this in error, please destroy it immediately and notify us at privacy...@ailife.com.
Re: Converting Text to XLS
Richard - I often send CSV files with a .txt extension. I use a TAB X'05' as a delimiter which is the default for for .txt files. The files are sent to user's network drive. They start Excel, OPEN the file and Excel recognizes the TAB delimiters and automatically pulls the file into the spreadsheet. Only one thing for the user to do: Select all, click Format - Column - Autofit selection. I get no complaint from user about having to do this format. /Fran Hensler at Slippery Rock University of Pennsylvania USA for 46 years mailto:f...@zvm.sru.edu http://zvm.sru.edu/~fjh +1.724.738.2153 Yes, Virginia, there is a Slippery Rock --
