IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Mainframe to windows(excel)


IBM Mainframe Forums -> FAQ & Basics
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
rajesh_m

New User


Joined: 15 Apr 2008
Posts: 39
Location: India

PostPosted: Thu May 15, 2008 6:51 pm
Reply with quote

Can any one tell me how convert a PS file to Excel.This thing should done automatically(Suppose if u called any routine). Is there any possibilty to do this.

Any help appreciatable.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu May 15, 2008 7:06 pm
Reply with quote

rajesh_m wrote:
Can any one tell me how convert a PS file to Excel.This thing should done automatically(Suppose if u called any routine). Is there any possibilty to do this.

Any help appreciatable.


No, Excel files are not mainframe compatable.
You can create a comma or tab separated text file or a HTML file, transfer it to your PC and import it into Excel.

This question has been discussed before.
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia

PostPosted: Thu May 15, 2008 7:37 pm
Reply with quote

A fixed length file with at least one space between fields can also be easily downloaded to a .txt file, then imported via the Excel wizard.
Back to top
View user's profile Send private message
rajesh_m

New User


Joined: 15 Apr 2008
Posts: 39
Location: India

PostPosted: Thu May 15, 2008 8:03 pm
Reply with quote

Phrzby Phil wrote:
A fixed length file with at least one space between fields can also be easily downloaded to a .txt file, then imported via the Excel wizard.


Can we do this through JCL. Please help me out!!!
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia

PostPosted: Thu May 15, 2008 8:10 pm
Reply with quote

Well, as is (all too) often mentioned here, JCL does not create files - it runs programs.

You need to run a program (COBOL, SAS, SORT, FORTRAN, PL/I, JOVIAL, ALGOL, assembler, DYLxxx, etc.) to create the file.

Then most likely you need to download it using your emulator's file transfer option. At least, that's how I do it.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu May 15, 2008 8:20 pm
Reply with quote

since x type fields can contain spaces, most times i have seen some kind of field delimited (with ;) file generated and sent to pc.

using symbols and dfsort, this could be just an exercise of putting the parms together.

a rexx could do this, if you wanted to gothru the trouble of determining field locations and sizes.

a cobol program could easily string the fields with intervening ';'. the string command could easily be generated, especially if the record was defined in a copybook.

then you would write an excell macro to input the file after downloading.
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia

PostPosted: Thu May 15, 2008 8:25 pm
Reply with quote

When I have embedded spaces, I then do have to adjust the wizard's proposed field sizes by adjusting the vertical lines. If this is a recurring task, then my manual solution is not the best, and Dick's macro idea is better.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri May 16, 2008 1:37 am
Reply with quote

Hello,

What has been quite helpful when downloading data to be used in excel is to do 2 STRINGs.

The first STRING (before the record processing begins) creates an output record of field names delimited by tabs (x'05') - this is the same STRING as the second one except that the field names have quotes to make them literals. The second STRING (during record processing) actually creates the tab-delimited data records. This way, when the file is loaded into excel, the first record is the column header of the names.

If the data is to be loaded into Access, SQL Server, or something else, the "header record" can be skipped.

The tab character has been convenient as it is most unlikely to appear in mainframe text data.
Back to top
View user's profile Send private message
rajesh_m

New User


Joined: 15 Apr 2008
Posts: 39
Location: India

PostPosted: Fri May 16, 2008 8:13 pm
Reply with quote

Please provide me some solution.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri May 16, 2008 9:13 pm
Reply with quote

Hello,

Quote:
Please provide me some solution.
You have already been told how to create a delimited file. Have you written and tested some code to do this? If not, do so.

Quote:
Can any one please give me the sample jcl for transfering a PS.
You need to better explain your requirement. One easy thing to do (if your mainframe has ftp) is to "pull" the file from the mainframe to your desktop using the ftp client that is already installed on your desktop.
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1042
Location: Richmond, Virginia

PostPosted: Fri May 16, 2008 9:23 pm
Reply with quote

That's the 2nd time (at least) that op has been told how to xfer to PC.

Three tries for a quarter.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Fri May 16, 2008 10:10 pm
Reply with quote

Many times the terminal emulator screen has a file transfer button, he just has to look at the emulator help to find out how to use it.
Back to top
View user's profile Send private message
anandinmainframe

Active User


Joined: 31 May 2007
Posts: 171
Location: India

PostPosted: Mon May 26, 2008 12:39 pm
Reply with quote

Hi,
Go to ISPF menu go to 6(command) option then at the top select actions->
transfer files->Recieve files from host there give your PS name in host file name.
Give some name for the excel --> in the PC File name .
Select the transfer mode as Text
Now click the Add to list command then give the command recieve
now you will get all the datas whatever you have in the ps will be transfred to Excel then format it by delimiting it.

Hope this helps.
Back to top
View user's profile Send private message
rajesh_m

New User


Joined: 15 Apr 2008
Posts: 39
Location: India

PostPosted: Mon May 26, 2008 3:38 pm
Reply with quote

Anand thank you for your response ..........
But i want to do it from REXX or JCL.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Mon May 26, 2008 6:37 pm
Reply with quote

One more time. . .

Quote:
You have already been told how to create a delimited file. Have you written and tested some code to do this? If not, do so.
Your requirement has 2 parts. The first is to create a ps file with the proper content. You have been told how to do this. Have you done anything to create this file?

Once a "download" file has been created, you can use one of several ways to download the file. If you want to "push" the file (using jcl) to a desktop, you probably cannot do this. Most organizations do not permit desktops to run an ftp service - they nearly all have an ftp client, but not an ftp service.

It is obviously not too important as you've "sat on it" for 10 days waiting for someone to do the work for you. . .

d
Back to top
View user's profile Send private message
rajesh_m

New User


Joined: 15 Apr 2008
Posts: 39
Location: India

PostPosted: Tue May 27, 2008 9:26 am
Reply with quote

Delimited file is created i got that............... Using start 6 we can send the files to local host ... why can't we do that from a JCL
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue May 27, 2008 10:00 am
Reply with quote

Hello Rajesh,

Quote:
Using start 6
What is this on your system (is start 6 on your mainframe or your desktop)?

Quote:
why can't we do that from a JCL
Depends on what you mean by "that". . . If you mean jcl to invoke ftp to send to a desktop, you have already been told why you probably cannot do this. Most desktop systems do not run an ftp service (if you don't understand this, you need to talk with your pc support or network support people).

As there is most likely not a desktop ftp service on your pc, you need to pick another way to solve your problem. One alternative might be to send yourself an e-mail from the mainframe with the delimited file as an attachment. Another is to simply pull the file from the mainframe using the ftp client already available on your pc.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> FAQ & Basics

 


Similar Topics
Topic Forum Replies
No new posts Mainframe openings in Techmahnidra fo... Mainframe Jobs 0
No new posts Mainframe Programmer with CICS Skill... Mainframe Jobs 0
No new posts How to Reformat a file using File Man... All Other Mainframe Topics 14
No new posts Converting fixed length file to excel... IBM Tools 7
No new posts NDM getting stuck - mainframe/JCL All Other Mainframe Topics 13
Search our Forums:

Back to Top