Excel to DSORG=PS conversion
G'day, I have data given to me in an excel spreadsheet. Is there any way that I can move this data from my PC and MS excel to a sequential file that I can edit with ISPF editor? I've tried a saving as a text file (everything is jammed together on a line). Same thing with a CSV file. I've tried cut and paste column to text file for each column then put them back into order. This was all right for a one timer but I know nothing is ones. Thank you for your assistance. Regards, Herman Stocker The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. The sender's employer is not liable for any loss or damage arising in any way from this message or its attachments. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
Stocker, Herman wrote: G'day, I have data given to me in an excel spreadsheet. Is there any way that I can move this data from my PC and MS excel to a sequential file that I can edit with ISPF editor? I've tried a saving as a text file (everything is jammed together on a line). Same thing with a CSV file. I've tried cut and paste column to text file for each column then put them back into order. This was all right for a one timer but I know nothing is ones. Thank you for your assistance. Regards, Herman Stocker Consider taking your CSV file as input to a sort copy, use the PARSE option of INREC or OUTREC to reformat your data to a flat file with fixed length records. Then you're good to go. (This is DFSORT; not sure if SyncSort has that, but I'm sure we'll hear...) -- Kind regards, -Steve Comstock The Trainer's Friend, Inc. 303-393-8716 http://www.trainersfriend.com z/OS Application development made easier * Our classes include + How things work + Programming examples with realistic applications + Starter / skeleton code + Complete working programs + Useful utilities and subroutines + Tips and techniques == Ask about being added to our opt-in list: == == * Early announcement of new courses == == * Early announcement of new techincal papers == == * Early announcement of new promotions == -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
On Fri, 21 Aug 2009 11:16:02 -0400, Stocker, Herman wrote: I have data given to me in an excel spreadsheet. Is there any way that I can move this data from my PC and MS excel to a sequential file that I can edit with ISPF editor? In order to process some Excel data with a script, I opened it with OOo (we don't use Excel) and saved it as HTML. Workable. Could have made small changes with an editor. I'd hate to add entire rows that way. And I had no requirement to re-import as a spreadsheet. -- gil -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
Thank you Steve. Regards, Herman Stocker -Original Message- From: IBM Mainframe Discussion List [mailto:ibm-m...@bama.ua.edu] On Behalf Of Steve Comstock Sent: Friday, August 21, 2009 11:27 AM To: IBM-MAIN@bama.ua.edu Subject: Re: Excel to DSORG=PS conversion Stocker, Herman wrote: G'day, I have data given to me in an excel spreadsheet. Is there any way that I can move this data from my PC and MS excel to a sequential file that I can edit with ISPF editor? I've tried a saving as a text file (everything is jammed together on a line). Same thing with a CSV file. I've tried cut and paste column to text file for each column then put them back into order. This was all right for a one timer but I know nothing is ones. Thank you for your assistance. Regards, Herman Stocker Consider taking your CSV file as input to a sort copy, use the PARSE option of INREC or OUTREC to reformat your data to a flat file with fixed length records. Then you're good to go. (This is DFSORT; not sure if SyncSort has that, but I'm sure we'll hear...) -- Kind regards, -Steve Comstock The Trainer's Friend, Inc. 303-393-8716 http://www.trainersfriend.com z/OS Application development made easier * Our classes include + How things work + Programming examples with realistic applications + Starter / skeleton code + Complete working programs + Useful utilities and subroutines + Tips and techniques == Ask about being added to our opt-in list: == == * Early announcement of new courses == == * Early announcement of new techincal papers == == * Early announcement of new promotions == -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html - -- The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. The sender's employer is not liable for any loss or damage arising in any way from this message or its attachments. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
The way I do this is to take excel and put it into a table in WORD. Then convert the word doc to a format I want on the MF and then save as a TXT file. Then up load to the minframe. If you have NSF/DSF in place or can use the ISPF WS Client, then you could edit the txt file on your PC using ISPF. Lizette G'day, I have data given to me in an excel spreadsheet. Is there any way that I can move this data from my PC and MS excel to a sequential file that I can edit with ISPF editor? I've tried a saving as a text file (everything is jammed together on a line). Same thing with a CSV file. I've tried cut and paste column to text file for each column then put them back into order. This was all right for a one timer but I know nothing is ones. Thank you for your assistance. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
On 21 Aug 2009 08:39:00 -0700, paulgboul...@aim.com (Paul Gilmartin) wrote: I have data given to me in an excel spreadsheet. Is there any way that I can move this data from my PC and MS excel to a sequential file that I can edit with ISPF editor? In order to process some Excel data with a script, I opened it with OOo (we don't use Excel) and saved it as HTML. Workable. Could have made small changes with an editor. I'd hate to add entire rows that way. And I had no requirement to re-import as a spreadsheet. I have been using delimited files, read in and parsed by programs. But then I discovered that I can save an Excel document as a .PRN document. This is probably what you want to do. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
On Fri, 21 Aug 2009 10:42:51 -0600, howard.bra...@cusys.edu wrote: I have been using delimited files, read in and parsed by programs. But then I discovered that I can save an Excel document as a .PRN document. This is probably what you want to do. Oh, one trouble with this method is that the size can change depending upon data. Fields aren't consistent when that same spreadsheet is changed. But that should be OK for your need. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
Parsing a CSV file in REXX should be very simple. Something along the lines of: /* REXX */ DO FOREVER EXECIO 1 DISKR INPUT IF RC 0 THEN LEAVE PARSE PULL RECORD I=0 DO WHILE RECORD PARSE VAR RECORD VALUE ',' RECORD I=I+1 DATA.I=VALUE END DATA.0=I /* PROCESS DATA */ END EXECIO 0 DISKR INPUT(FINIS -- John -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
I agree with John this have done with SAS csv files ..with no problem at all in Rexx ( the wonder horse ), pretty easy . Scott J Ford From: John McKown joa...@swbell.net To: IBM-MAIN@bama.ua.edu Sent: Friday, August 21, 2009 1:06:48 PM Subject: Re: Excel to DSORG=PS conversion Parsing a CSV file in REXX should be very simple. Something along the lines of: /* REXX */ DO FOREVER EXECIO 1 DISKR INPUT IF RC 0 THEN LEAVE PARSE PULL RECORD I=0 DO WHILE RECORD PARSE VAR RECORD VALUE ',' RECORD I=I+1 DATA.I=VALUE END DATA.0=I /* PROCESS DATA */ END EXECIO 0 DISKR INPUT(FINIS -- John -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
So what happens if your data in a cell contains a comma? Do you have to account for that possibility in REXX? Lizette Parsing a CSV file in REXX should be very simple. Something along the lines of: /* REXX */ DO FOREVER EXECIO 1 DISKR INPUT IF RC 0 THEN LEAVE PARSE PULL RECORD I=0 DO WHILE RECORD PARSE VAR RECORD VALUE ',' RECORD I=I+1 DATA.I=VALUE END DATA.0=I /* PROCESS DATA */ END EXECIO 0 DISKR INPUT(FINIS -- John -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
On Fri, 21 Aug 2009 10:18:15 -0700, Scott Ford scott_j_f...@yahoo.com wrote:
I agree with John this have done with SAS csv files ..with no problem at
all in Rexx ( the wonder horse ), pretty easy .
Scott J Ford
Silly me. I forgot to say it is also simple in awk and Perl as well.
Perl
while() {
@data=split/,/;
# process data in $data[0] and so on
}
--
John
--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO
Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
-Original Message- From: IBM Mainframe Discussion List [mailto:ibm-m...@bama.ua.edu] On Behalf Of Lizette Koehler Sent: Friday, August 21, 2009 12:25 PM To: IBM-MAIN@bama.ua.edu Subject: Re: Excel to DSORG=PS conversion So what happens if your data in a cell contains a comma? Do you have to account for that possibility in REXX? Lizette In the case where I know that there will be a comma in the data, I select another character which is not in the data. Such as a TAB (x'05') or a pipe symbol | . -- 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 -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
Thank you Lizette. I never though of a word table. Regards, Herman Stocker -Original Message- From: IBM Mainframe Discussion List [mailto:ibm-m...@bama.ua.edu] On Behalf Of Lizette Koehler Sent: Friday, August 21, 2009 12:24 PM To: IBM-MAIN@bama.ua.edu Subject: Re: Excel to DSORG=PS conversion The way I do this is to take excel and put it into a table in WORD. Then convert the word doc to a format I want on the MF and then save as a TXT file. Then up load to the minframe. If you have NSF/DSF in place or can use the ISPF WS Client, then you could edit the txt file on your PC using ISPF. Lizette G'day, I have data given to me in an excel spreadsheet. Is there any way that I can move this data from my PC and MS excel to a sequential file that I can edit with ISPF editor? I've tried a saving as a text file (everything is jammed together on a line). Same thing with a CSV file. I've tried cut and paste column to text file for each column then put them back into order. This was all right for a one timer but I know nothing is ones. Thank you for your assistance. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html - -- The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. The sender's employer is not liable for any loss or damage arising in any way from this message or its attachments. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
On Fri, 21 Aug 2009 12:06:48 -0500, John McKown wrote: Parsing a CSV file in REXX should be very simple. Something along the lines of: /* REXX */ DO FOREVER EXECIO 1 DISKR INPUT IF RC 0 THEN LEAVE PARSE PULL RECORD I=0 DO WHILE RECORD PARSE VAR RECORD VALUE ',' RECORD I=I+1 DATA.I=VALUE END DATA.0=I /* PROCESS DATA */ END EXECIO 0 DISKR INPUT(FINIS Some cells I had to deal with contained text with embedded newlines. This broke rows into multiple records. And I used tab instead of comma, because I feared embedded commas. And I didn't know how embedded quotation marks would be escaped. HTML seemed more orderly, or at least I know HTML better. -- gil -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
Re: Excel to DSORG=PS conversion
Must admit I was stunned you didn't offer this as an option first. John Mc wrote: Silly me. I forgot to say it is also simple in awk and Perl as well. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
