Mainframe PS to Excel and mail it.
Moderators: Veera, Moderator Group
Mainframe PS to Excel and mail it.
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..
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
Wipro Technologies
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?
Is there something really uniquely different that you need to do that's not already been done and discussed before?
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..
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
Wipro Technologies
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.
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
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...
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
Wipro Technologies
So, specify .xls as the extension for the file being transferred. . . Actually, a .txt extension will work also.So i need to send the attachment as .xls file...
This is because you have not created the delimited file correctly. You need to interleave a delimiter between each data field.however each row in a dataset appears in a single cell.. so i need to split the data into columns based on delimiters..
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. . .For this i believe ExcelXP tagset could be used...
Have a good one
Huh. . .Can you please provide sample data to refer?
Why can you not simply use your data with delimiters?
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.the way my data will look is
Empno #Name #Age
1 #AAAAA #25
2 #BBBBB #26
Have a good one
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
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.
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.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
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...
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...
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.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 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.The prob with csv file is that you cannot have password protection...
Not a waste to me It does seem though, that you are being expected to unnecessarily jump thru hoops.Apologies if iam wasting your time.. Many Thanks for the support provided till now...
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.
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.Is there any option to parse the data with a delimiter when i am sending the attachment as .xls
Have a good one
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 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
otherwise, if you have microsoft excel at your site,
the thing to do would be write a microsoft excel macro that would
- import your .txt file
- save it as an .xls file
- 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.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page