Mainframe PS to Excel and mail it.

Ask/Clarify the questions on TSO, CLIST & REXX

Moderators: Veera, Moderator Group

dichandr
Member
Posts: 7
Joined: Sat Nov 12, 2011 11:46 pm

Mainframe PS to Excel and mail it.

Post by dichandr » Sun Nov 13, 2011 3:10 pm

Hi Friends,
I have a requirement to send the data present in a PS to a mail recipient as Excel.
I would be executing a DB2 query and using a Selcopy to convert the SYSPRINT of SPUFI job into a PS which will have columns separated by '#' symbol.
Now i need to conver this content into a excel and mail it.
I checked the forum but getting confused as i am completely new to SAS..

Many Thanks in advance..
Divakar C
Wipro Technologies

MrSpock
Active Member
Posts: 273
Joined: Wed Jun 27, 2007 5:37 pm

Post by MrSpock » Sun Nov 13, 2011 8:09 pm

Well, you're right. There are lots and lots of topics here and elsewhere on this subject, so I think it's pretty much been beaten to death. As far as I know, there is still no mainframe product or tool as of yet that can directly convert text data into Excel .xls code. If you want to use SAS, there are plenty of code examples on their website and in other discussion forums.

Is there something really uniquely different that you need to do that's not already been done and discussed before?

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Sun Nov 13, 2011 8:39 pm

Create the data to be downloaded as a delimited file (the tilde '~' and the tab x'05' both work well). Send the file as an e-mail attachment and the recipient will be able to import this directly into Excel.
Have a good one

dichandr
Member
Posts: 7
Joined: Sat Nov 12, 2011 11:46 pm

Post by dichandr » Sun Nov 13, 2011 9:08 pm

Thanks MrSpock & DikDude...

I tried Excelxp tagset but i don think tat tagset is available for use. How do i check what are all the tagsets available...

the way my data will look is
Empno #Name #Age
1 #AAAAA #25
2 #BBBBB #26

Now i need this to be downloaded to a spreadsheet having values of Empno, Name & Age in diff columns..

It would be very gratefull if someone can guide me to work this out..
Divakar C
Wipro Technologies

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Sun Nov 13, 2011 9:50 pm

You need to re-read and understand my previous post. . .

I made no mention of any "tagset". I have never used one (to my knowledge) and have downloaded thousands of mainframe files to be used on win-based and unix systems. All that is needed is a common delimiter that cannot appear within the actual data.

Suggest you create a tiny file with the sample data you have shown and place a tilde or a tab between the fields with the editor. Then e-mail this delimited file to yourself. Open a blank spreadsheet on the pc and import this data. It will be as you wish.

If there are problems/questions, post back with them.
Have a good one

dichandr
Member
Posts: 7
Joined: Sat Nov 12, 2011 11:46 pm

Post by dichandr » Mon Nov 14, 2011 2:35 am

My requirement is to send the attachment as excel itself.. The results are to be sent to Business stake holders who needs the end result..

So i need to send the attachment as .xls file...

I am able to send the content in mainframe dataset as excel spreadsheet, however each row in a dataset appears in a single cell.. so i need to split the data into columns based on delimiters..

For this i believe ExcelXP tagset could be used... But i could not find any comlplete example...

Please help me with any examples...
Divakar C
Wipro Technologies

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Mon Nov 14, 2011 10:45 am

So i need to send the attachment as .xls file...
So, specify .xls as the extension for the file being transferred. . . Actually, a .txt extension will work also.
however each row in a dataset appears in a single cell.. so i need to split the data into columns based on delimiters..
This is because you have not created the delimited file correctly. You need to interleave a delimiter between each data field.
For this i believe ExcelXP tagset could be used...
I believe you are completely wasting time on this "tagset". . . Don't know how you've become fixated on this, but as i mentioned before - i have NEVER used one and have successfullt sent thousands of delimited files from the mainframe. . .
Have a good one

dichandr
Member
Posts: 7
Joined: Sat Nov 12, 2011 11:46 pm

Post by dichandr » Tue Nov 15, 2011 8:00 pm

Thanks Dikdude...
Can you please provide sample data to refer?

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Wed Nov 16, 2011 12:04 am

Can you please provide sample data to refer?
Huh. . . :?

Why can you not simply use your data with delimiters?
the way my data will look is
Empno #Name #Age
1 #AAAAA #25
2 #BBBBB #26
Copy your sample data into a mainframe pds member or other sequential file. Add delimiters between the fields. Download to the pc as a .txt or .xls file. Open Excel and import this downloaded file.
Have a good one

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Wed Nov 16, 2011 12:59 am

DikDude,

i am afraid that he is asking for code,
simply supply him with your paypal number
and after he has deposited money in your account
do his job for him.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Wed Nov 16, 2011 3:22 am

Hi DBZ,

I'm at a loss. . . An opportunity to generate revenue and i have no idea what code i might provide. . .

Once the delimited file is available, i don't recall using any "code". . .

Bummer - opportunity missed.

d

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Wed Nov 16, 2011 3:55 am

if you are doing a db2 select,
include your delimiter - whatever it is
as an item in the select list of your query.

in addition, though SAS maybe the tool of your choice,
i would rather parse the sysout (make it a temp file - %%....)
and use the temp file as input to sort step.

if you have problems parsing the data with sort,
start a question in the DFSORT forum,
and Frank will help you.

otherwise, if this is a SAS question,
which apparently it is,
i suggest that this thread be abandoned
and a new question posed:
SAS Parsing Data and building records.

this is the tso,clist,rexx forum,
and if you want SAS help, you need to have that in your title.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

dichandr
Member
Posts: 7
Joined: Sat Nov 12, 2011 11:46 pm

Post by dichandr » Wed Nov 16, 2011 4:53 am

Thanks Guys.. Am no way expecting the code...

I understand and believe in "No Pain No Gain"

I am able to download the content as delimited file as .txt and would be able to import that into a excel.. And if i am mailing it as .xls i am facing the prob of one row in a PS appearing in one cell..

Note: I am also able to mail the spreadsheet as .csv file...

However since I am sending this to a Busines stakeholder from whom i cannot expect any of the data import/export to an excel or any activity. The prob with csv file is that you cannot have password protection...

The current steps i have are as below:
Create a dynamic SQL query
Execute the query
format the query output as a delimited file
this fomatted file needs to be sent a mail attachment..

For the last step of above process... Is there any option to parse the data with a delimiter when i am sending the attachment as .xls

Now i have my SAS code as below:
TO: 'xxxxxxxxxxxxx@yyyyyyyyyy.com'
attach:('ABC.DEF' ext='csv')

Apologies if iam wasting your time.. Many Thanks for the support provided till now...

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Wed Nov 16, 2011 9:48 am

However since I am sending this to a Busines stakeholder from whom i cannot expect any of the data import/export to an excel or any activity.
If the "user" cannot be asked to do "anything", you may need to create a real spread sheet and then e-mail this so the user has to do nothng but click on the attachment.

The prob with csv file is that you cannot have password protection...
If the file is being sent directly to the user, what need is there of another password? They already used a password to log into their e-mail.
Apologies if iam wasting your time.. Many Thanks for the support provided till now...
Not a waste to me :) It does seem though, that you are being expected to unnecessarily jump thru hoops.

If money is no object and your organization wants to spen none of your time as well as expecting nothing from the user, buying software that will generate a "real" spreadsheet could be considered. This would have much more functoiknality than a simple delimited file.
Is there any option to parse the data with a delimiter when i am sending the attachment as .xls
Suggest you re-read the last post from DBZ - you could generate the delimiter in the db2 query as a literal between each column named.
Have a good one

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Wed Nov 16, 2011 6:59 pm

parse by the way is 'take apart', not concatenate.

otherwise, if you have microsoft excel at your site,
the thing to do would be write a microsoft excel macro that would
  1. import your .txt file
  2. save it as an .xls file
  3. send the email with the file as attachment
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others