Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Any tool that exports data from Mainframes to Excel sheet?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
pullaiah.cts

New User


Joined: 02 Sep 2010
Posts: 50
Location: Pune

PostPosted: Sun Sep 04, 2011 11:15 pm    Post subject: Any tool that exports data from Mainframes to Excel sheet?
Reply with quote

Hi All,

In our project, I used to run the DB2 queries and prepare the transaction reports as per the given details like Client id,transaction date...etc.
I follow the below steps to create the reports:
1.Run the DB2 queries
2.Execute the COBOL program to seperate each columns with a delimiter.
3.Download the data from Mainframes to a text file
4.Then, export the data to a excel sheet and format it.

But,there is much time wasting by downloading it to text file and again export it to Excel sheet.So,I am looking for way to export the data from mainframe to Excel sheet directly.

Can anyone please let me know the tools that are used to export the data to excel sheets directly? I found a tool called 'Spectrum Mainframe Exporter' which can satisy my requirement but it's licence is too expensive,we cannot bear that much amount.

Any other tool which will satisy my requirement with less amount?

Thanks in advance!!!!!
Back to top
View user's profile Send private message

Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7931
Location: Bellevue, IA

PostPosted: Sun Sep 04, 2011 11:36 pm    Post subject:
Reply with quote

Based upon what I see, the Spectrum Mainframe Exporter is quite cheap -- $4995 per year. Most mainframe software packages run anywhere from $10K to $250K depending upon your machine size and what the software is doing.

You are not very likely to find many products to do what you want for less than $5K. You could always write your own, of course, but that's probably about a 1 to 2-year effort for a 4 to 6 person programming team since the file specifications for Excel format are well over 1000 pages at this point.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10210
Location: italy

PostPosted: Sun Sep 04, 2011 11:40 pm    Post subject: Reply to: Any tool that exports data from Mainframes to Exce
Reply with quote

unless You are doing something weird,
there is no reason to use a cobol program to create a CSV file, the select itself can do it
and EXCEL can open a CSV

if Your organization is tight on budget You will have to bear it
the time saved is just a handful of seconds

search the forums for SQL CSV to arrive here
http://www.ibmmainframes.com/viewtopic.php?t=38552&highlight=sql+csv
or here
http://www.ibmmainframes.com/viewtopic.php?t=55717&highlight=sql+csv
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7251

PostPosted: Mon Sep 05, 2011 1:13 am    Post subject: Reply to: Any tool that exports data from Mainframes to Exce
Reply with quote

enrico has pointed out one method to simplify the mainframe side.

On your csv, include header with business date and trailer with count and hash values.

On the PC, write a macro to allow the user to "import" their new file. The macro looks in the place the file is supposed to be, reads the file, prompts the user as to the correct business date, loads up the CSV, confirms the values on encountering the trailer, reports any problems along the way.

So, extract from DB2 direct to a CSV (including appropriate header and trailer). FTP/other method of transfer. User-option to load new file.

Everything smooth. OK, it wasn't DB2, but this worked 20 years ago, so should still work now.
Back to top
View user's profile Send private message
Stefan

Active User


Joined: 12 Jan 2006
Posts: 110
Location: Germany

PostPosted: Mon Sep 05, 2011 2:53 pm    Post subject:
Reply with quote

You could do it all in the same batch job with no additional cost. Just use the ISPF workstation agent (WSA). Run it on your workstation as a TCP/IP server interacting with ISPF. The workstation agent is an official component of ISPF and can be downloaded from an ISPF installation dataset and requires no additional or separate licence. Although the product has not been enhanced during the last years, IBM still supports the ISPF WSA.
Let batch job do the data collection and formatting. Then add an IKJEFT01 step with invoking a small REXX program via ISPSTART using the connection details of your workstation. The REXX program can use the ISPF services WSCON and WSDISCON to contact your workstation. The ISPF service FILEXFER is designed to transfer your dataset to any directory of your PC network using various options. After copying the data you can use SELECT WSCMD(%comspec% /C START /MAX c:\filename.xls) MODELESS to invoke your spreadsheet processor and format your output asynchronously as desired.
You'll find good documentation in the ISPF manuals and a comprehensive summary on Gilbert St. Flour's website.
Check it out and give it a try.
Back to top
View user's profile Send private message
superk

Moderator Team Head


Joined: 26 Apr 2004
Posts: 4648
Location: Raleigh, NC, USA

PostPosted: Mon Sep 05, 2011 6:50 pm    Post subject: Reply to: Any tool that exports data from Mainframes to Exce
Reply with quote

pullaiah.cts, I guess what I don't understand is why you said that there's a lot of wasted time for this process. Since you and/or your site designed and implemented it, what attempts have you made to either eliminate a step or two, or at least reduce the amount of "time wasted"? Don't you have software designed to accumulate and distribute reports company-wide? Simple spreadsheets are relatively easy to create and distribute in an automated fashion.
Back to top
View user's profile Send private message
pullaiah.cts

New User


Joined: 02 Sep 2010
Posts: 50
Location: Pune

PostPosted: Mon Sep 05, 2011 7:29 pm    Post subject: Reply to: Any tool that exports data from Mainframes to Exce
Reply with quote

Hi all,

Thank you so much for your valuable advices.I just came to know that there is a tool in out company which generates report from mainframe flat file to excel spreadsheet.It is devloped based on VB and it is fit to my requireent.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7251

PostPosted: Mon Sep 05, 2011 9:37 pm    Post subject:
Reply with quote

OK, that's good. It was a year later we got to VB and Access.

The problem was, once the users knew we could extract data for them, they wanted ever more. We still regarded it as "our" data: users aren't generally congnisant of the implications of holding data securely and correctly. Plus, if we ever had to re-run something, we also had to re-supply the data to the user, their spreadsheets became part of our "impact" for failures.

So, we treated all the data we handed-off exactly as we would for our systems handing-off data to another internal system or an external system, except that we had to be the ones "catching" the data on the PC and ensuring its integrity.

For our data, we ended up with a VB system collecting and logging the files (business date, run date, counts, hash counts, source files name, target file name) and providing data for the spreadsheets. OPS would fire-up the collecter in the around 5am and it would periodically check for files until all collected. Individual files could be overriden as not available etc.

Except for system problems, users had their spreadsheets ready to go as they arrived, including business day and totals reconcilliations which we included in all their spreadsheets. Files were backed-up on the PC network.

This is a mainframe forum, so why am I saying this?

Because it is, as far as I'm concerned, still mainframe data in the same way as any report or screen. All those same people (accountants, auditors, etc) are going to come and bounce on you if you don't do everything necessary, because no-one (OK, not many) down in the user/PC world is used to the rigours of looking after the data.

When you release data to anyone (another system, an external system, users) you do it at your peril if you don't do it properly. If a user (worse, accountant, auditor, compliance staff) says "this line on this spreadsheet doesn't exist on the mainframe" how are you going to sort it out if you haven't done it properly? Bearing in mind, that the "line" in question can easily be from many months ago.

I've said my bit now. 'Nuff PC stuff.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Storing huge volume of data, compare ... Pradeep K M All Other Mainframe Topics 3 Mon Jan 16, 2017 5:08 pm
No new posts how to recover an uncataloged VSAM da... archanamuthukrishnan All Other Mainframe Topics 3 Wed Jan 11, 2017 6:18 pm
No new posts HALDB data refresh/copy from producti... vineetanand2007 IMS DB/DC 0 Mon Jan 02, 2017 11:16 am
No new posts Are there any Freeware utilties for i... johnmull IBM Tools 2 Wed Dec 21, 2016 8:29 am
No new posts JES2 JEC: Use UNIX Pipes to Pass Data... Virendra Shambharkar JCL & VSAM 21 Tue Dec 20, 2016 6:55 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us