Joined: 26 Apr 2004 Posts: 4650 Location: Raleigh, NC, USA
Not really a mainframe-related topic, but ...
I create the data using the Symbolic Link format (.slk) since it allows you to use markup tags for column headers, formats, and the like, and then store the file with the .xls extension. When opened with a double-click, it looks like any other spreadsheet, and when saved, Excel writes it as a fully-formatted .xls document.
Joined: 06 Jun 2008 Posts: 8232 Location: Dubuque, Iowa, USA
Your step 3 is actually 2 steps:
a. move the data from the mainframe to the PC
b. import the data into Excel.
FTP is one way to accomplish 3a, but there are others. The key thing to recognize is that FTP (and most other methods) must connect to a server, and desktop machines are not servers. Therefore you cannot automate a transfer on the mainframe to connect to your desktop and send the file. If this is part of your requirements, you'll need to send the file to a server on your site's network and then get it from that server.
As far as 3b goes, unless your site has spend the money to acquire a conversion package for Excel, your best bet is to use comma-delimited or SLK format as Kevin suggested. Excel opens these files and converts the data while opening it.
Joined: 22 Apr 2006 Posts: 6258 Location: Mumbai, India
Step 3. I want to get this PS file as an output on windows in the form of EXCEL sheet.
I am struggling with this Step 3. Your any help is appreciable.
You don't get the file in "excel format" (.xls) when you transfer teh data from mainframes(zOS) to windows - it's in ".txt" format. You open (import the data) that file with "Microsoft Excel" and to get the different columns from your "text-file" alligned to different columns of "spread-sheet" you need some kind of delimiter, as Kevin has said. possibly, you can create a comma-delimited file and then "tell" excel to use "," as seprater.
We used to do the following things when we need to send report as an excel file. Hope this will help you...
> ftp the dataset into windows as .txt file.
>In Ms Excel select 'Data' menu, select 'Import External Data', select 'Import Data'
>In the Files of type box, click Text Files.
>In the Look in list, locate and double-click the text file you want to import as an external data range.
>To specify how you want to divide the text into columns, follow the instructions in the Text Import Wizard, and then click Finish.
>In the Import Data dialog box, click Properties to set formatting and layout options for the imported data.
>In the Import Data dialog box, do one of the following:
>To return the data to the location you selected, click Existing worksheet, and then click OK.
>To return the data to a new worksheet, click New worksheet, and then click OK. Microsoft Excel adds a new worksheet to your workbook and automatically puts the external data range in the upper-left corner of the new worksheet.