|
View previous topic :: View next topic
|
| Author |
Message |
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland
|
|
|
|
The three datasets need to have the same DCB info. Try creating the MIME control cards in a dataset with LRECL=141 and put the . & QUIT into another LRECL=141 dataset.
// DD * datasets will have LRECL=80 which is conflicting with the .csv dataset DCB.
Garry. |
|
| Back to top |
|
 |
sakrat
Active User
Joined: 05 Feb 2014
Posts: 164
Location: India
|
|
|
|
| Garry Carroll wrote: |
The three datasets need to have the same DCB info. Try creating the MIME control cards in a dataset with LRECL=141 and put the . & QUIT into another LRECL=141 dataset.
// DD * datasets will have LRECL=80 which is conflicting with the .csv dataset DCB.
Garry. |
The code which i have posted above has three datasets with same LRECL 141....
All the three has RECFM = FB, LRECL=141 and BLKSIZE=27918 |
|
| Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland
|
|
|
|
Have you read
www-01.ibm.com/support/docview.wss?uid=swg21512178 ?
and have you got the "boundary" identifier in all required places. In this example, you'll see it before the text
| Code: |
Content-Type: text/plain;
|
and before and after the data to be in the attachment
| Code: |
Content-Type: application;
Content-Disposition: attachment; filename=cert.cer;
Content-Transfer-Encoding: 8bit; |
Garry. |
|
| Back to top |
|
|
sakrat
Active User
Joined: 05 Feb 2014
Posts: 164
Location: India
|
|
|
|
| Garry Carroll wrote: |
Have you read
www-01.ibm.com/support/docview.wss?uid=swg21512178 ?
and have you got the "boundary" identifier in all required places. In this example, you'll see it before the text
| Code: |
Content-Type: text/plain;
|
and before and after the data to be in the attachment
| Code: |
Content-Type: application;
Content-Disposition: attachment; filename=cert.cer;
Content-Transfer-Encoding: 8bit; |
Garry. |
I modified the code giving boundary as specified in the link above:
| Code: |
HELO ***
MAIL FROM: <***.***@***.COM>
RCPT TO: <***.***@***.COM>
DATA
FROM: <*** TEAM>
TO: < DEV TEAM>
SUBJECT: CONTROL-M DOCUMENTATION
MIME-VERSION: 1.0
CONTENT-TYPE: MULTIPART/MIXED;
BOUNDARY="---- =_BOUNDS"
THIS MESSAGE IS IN MIME FORMAT.
---- =_BOUNDS
CONTENT-TYPE: TEXT/PLAIN;
PLEASE FIND THE ATTACHED CONTROL-M DOCUMENT.
NOTICE:
THIS EMAIL IS CONFIDENTIAL
---- =_BOUNDS
CONTENT-TYPE: TEXT/PLAIN;
CONTENT-DISPOSITION: ATTACHMENT;
FILENAME="CNTRL-M DOCUMENT.CSV"
---- =_BOUNDS
|
JCL :
| Code: |
//SENDMAIL EXEC PGM=IEBGENER,
// COND=(0,NE)
//SYSIN DD DUMMY
//SYSPRINT DD SYSOUT=*
//SYSUT2 DD SYSOUT=(B,SMTP)
//SYSUT1 DD DSN=***.TEST.CNTRL.EMAIL,DISP=SHR
// DD DSN=***.****.FINAL.CNTRLM.DOC,DISP=SHR
// DD DSN=***.QUIT.STMT,DISP=SHR
|
and I receive a mail with no attachments but in the BODY OF THE MAIL I receive everything like below :
| Code: |
BOUNDARY="---- =_BOUNDS"
THIS MESSAGE IS IN MIME FORMAT.
---- =_BOUNDS
CONTENT-TYPE: TEXT/PLAIN;
PLEASE FIND THE ATTACHED CONTROL-M DOCUMENT.
NOTICE:
THIS EMAIL IS CONFIDENTIAL
---- =_BOUNDS
CONTENT-TYPE: TEXT/PLAIN;
CONTENT-DISPOSITION: ATTACHMENT;
FILENAME="CNTRL-M DOCUMENT.CSV"
---- =_BOUNDS
NEW,PREJOBS,Q075SBEJ,Q075CB ,CONCATENATE B12960 AND SBF, D6W1
00000100
QUIT |
De-bolded |
|
| Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland
|
|
|
|
Move the last
to AFTER the
| Code: |
| // DD DSN=***.****.FINAL.CNTRLM.DOC,DISP=SHR |
.
You last =_BOUNDS is delimiting the
| Code: |
---- =_BOUNDS
CONTENT-TYPE: TEXT/PLAIN; |
Garry |
|
| Back to top |
|
|
sakrat
Active User
Joined: 05 Feb 2014
Posts: 164
Location: India
|
|
|
|
| Garry Carroll wrote: |
Move the last
to AFTER the
| Code: |
| // DD DSN=***.****.FINAL.CNTRLM.DOC,DISP=SHR |
.
You last =_BOUNDS is delimiting the
| Code: |
---- =_BOUNDS
CONTENT-TYPE: TEXT/PLAIN; |
Garry |
I tried it, its still the same way i get in mail....
| Code: |
BOUNDARY="---- =_BOUNDS"
THIS MESSAGE IS IN MIME FORMAT.
---- =_BOUNDS
CONTENT-TYPE: TEXT/PLAIN;
PLEASE FIND THE ATTACHED CONTROL-M DOCUMENT.
NOTICE:
THIS EMAIL IS CONFIDENTIAL
---- =_BOUNDS
CONTENT-TYPE: TEXT/PLAIN;
CONTENT-DISPOSITION: ATTACHMENT;
FILENAME="CNTRL-M DOCUMENT.CSV"
NEW,JOBNAME,JOBNAME1,TABNAME,TABLE DESCRIPTION,D6W1
---- =_BOUNDS
|
I moved the last _BOUNDS to the JCL where . & QUIT is placed. even then output in the mail is same with one difference QUIT is not coming now.... |
|
| Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland
|
|
|
|
You've specified the boundary with four underscores.
| Code: |
| BOUNDARY="---- =_BOUNDS" |
You need to use boundary statements correctly. The statements that implement the boundaries must begin with two underscores, so change to:
| Code: |
| BOUNDARY="=_BOUNDS" |
and then, for the boundary statements :
Garry. |
|
| Back to top |
|
|
sakrat
Active User
Joined: 05 Feb 2014
Posts: 164
Location: India
|
|
|
|
| Garry Carroll wrote: |
You've specified the boundary with four underscores.
| Code: |
| BOUNDARY="---- =_BOUNDS" |
You need to use boundary statements correctly. The statements that implement the boundaries must begin with two underscores, so change to:
| Code: |
| BOUNDARY="=_BOUNDS" |
and then, for the boundary statements :
Garry. |
Yes garry i found the issue and the code is working fine with attachment containing records in it.....  Now the issue what i am facing is i have records in dataset as below :
| Code: |
NEW,JOBNAME1,JOBNAME,TABNAME,TAB DESCRIPTION,WORKING DAYS
JOB1 CNT OF DESC
JOB2 |
JOB1 and JOB2 present in the same column where JOBNAME is there. and when i send this attachment i get JOB1 and CNT OF DESC in first column of row 2 and JOB2 in first column of row 3 in excel.... can i not get it in same colum where JOBNAME is present in the first row. (JOBNAME is present in row1 of column 3), so i want JOB2 and JOB3 also be present in row2 and row3 of column 3. is that possible...?
once i get this done i will be completing all my requirements....would be very much thankful to you if any help is got for this....
Some Code'ing |
|
| Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland
|
|
|
|
Depends on how you are building the records.
You could build with BUILD=(C',,,',x,y,..... ) where x and y are the position and length pf "JOB1 CNT of DESC", respectively. This puts 3 commas at the start of the record. Excel will then align the fields accordingly.
Garry. |
|
| Back to top |
|
|
sakrat
Active User
Joined: 05 Feb 2014
Posts: 164
Location: India
|
|
|
|
| Garry Carroll wrote: |
Depends on how you are building the records.
You could build with BUILD=(C',,,',x,y,..... ) where x and y are the position and length pf "JOB1 CNT of DESC", respectively. This puts 3 commas at the start of the record. Excel will then align the fields accordingly.
Garry. |
Yes that is what I did but I want JOB1 and Job2 to be under JOBNAME in one cell of that first row. |
|
| Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland
|
|
|
|
Did you modify your sort step as I suggested on Friday last in the DFSORT forum ?
ibmmainframes.com/viewtopic.php?p=329275#329275
You need to get the information from both related records into a single record and build your output .csv record from that. From your description, what you need is a record that looks something like :
,,,JOB1 JOB2 ,CNT OF DESC
Garry. |
|
| Back to top |
|
|
sakrat
Active User
Joined: 05 Feb 2014
Posts: 164
Location: India
|
|
|
|
| Garry Carroll wrote: |
Did you modify your sort step as I suggested on Friday last in the DFSORT forum ?
ibmmainframes.com/viewtopic.php?p=329275#329275
You need to get the information from both related records into a single record and build your output .csv record from that. From your description, what you need is a record that looks something like :
,,,JOB1 JOB2 ,CNT OF DESC
Garry. |
Please find the attached Excel sheet through which i can clearly explain my expected output and requirement. Sorry I couldnt paste in the text as it will be difficult to understand. |
|
| Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland
|
|
|
|
From what you've attached, I can see that you have multiple records in the .csv file for what you want in each row of the Excel spreadsheet. It would also seem that you have a variable number of records for each row (3 record for row 1 and 2 records for row 2 in the example).
If you have variable number of records, you would probably be best to create the .csv file in a program - as I said last Friday:
| Garry Carroll wrote: |
Is there a fixed number of rows for each key (e.g. 1,8 = AAAAAAAA )? If not, you'd probably be better processing the report in a program rather than in DFSORT.
There may be a way with sort - as well as starting each GROUP at a non-blank key, is there any way of determining the END of a group (e.g. 16,6,CH,EQ,C' ') ?
Garry. |
What you need to achieve before your e-mail step is a .csv file with a single record per excel row.
Once you've done that, getting the vertical alignment within cells is Excel formatting - which is not my area of expertise.
Garry. |
|
| Back to top |
|
|
sakrat
Active User
Joined: 05 Feb 2014
Posts: 164
Location: India
|
|
|
|
| Garry Carroll wrote: |
From what you've attached, I can see that you have multiple records in the .csv file for what you want in each row of the Excel spreadsheet. It would also seem that you have a variable number of records for each row (3 record for row 1 and 2 records for row 2 in the example).
If you have variable number of records, you would probably be best to create the .csv file in a program - as I said last Friday:
| Garry Carroll wrote: |
Is there a fixed number of rows for each key (e.g. 1,8 = AAAAAAAA )? If not, you'd probably be better processing the report in a program rather than in DFSORT.
There may be a way with sort - as well as starting each GROUP at a non-blank key, is there any way of determining the END of a group (e.g. 16,6,CH,EQ,C' ') ?
Garry. |
What you need to achieve before your e-mail step is a .csv file with a single record per excel row.
Once you've done that, getting the vertical alignment within cells is Excel formatting - which is not my area of expertise.
Garry. |
Sorry my mistake i misinterpreted the rows as field length without looking at it properly. So now if i write a COBOL program to create the .CSV file, howdoes the logic comes in....? I have two input file and one output .csv file has to be created. so by comparing the input file2 key value with the input file1 key value and if it matched move the necessary fields to the output .csv file with comma separated. But what to do with the next (eg: 2 records) of that key value how can i move it to the .csv file.....?
can you give some idea about it....? |
|
| Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland
|
|
|
|
The logic is something you should be able to work out for yourself. You already know, from the work you did with DFSORT, that the group of records in file 2 ends when bytes 1-8 are non-blank.
Provided the "keys" in both files are in ascending sequence, it should be a fairly simple piece of code to produce the .csv output. If they're not in sequence, use DFSORT to put them in sequence.
Garry. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| If you want to do it with DFSORT, please ask a new question there. You can provide links to the two relevant questions, but both are already very "long", making them more difficult to be useful to others. |
|
| Back to top |
|
|
sakrat
Active User
Joined: 05 Feb 2014
Posts: 164
Location: India
|
|
|
|
| Garry Carroll wrote: |
The logic is something you should be able to work out for yourself. You already know, from the work you did with DFSORT, that the group of records in file 2 ends when bytes 1-8 are non-blank.
Provided the "keys" in both files are in ascending sequence, it should be a fairly simple piece of code to produce the .csv output. If they're not in sequence, use DFSORT to put them in sequence.
Garry. |
If its the same way that has been done using DFSORT and join keys that would be used for COBOL as well, the output .CSV will also be obtained in the same way as now right....that is what i was asking you how would be the output .CSV file look like if using COBOL so that i can achieve what i require in excel... As you said logic of the cobol i will only have to think that would be good...but i want to know how the layout of the output file would be...? |
|
| Back to top |
|
|
sakrat
Active User
Joined: 05 Feb 2014
Posts: 164
Location: India
|
|
|
|
| Bill Woodger wrote: |
| If you want to do it with DFSORT, please ask a new question there. You can provide links to the two relevant questions, but both are already very "long", making them more difficult to be useful to others. |
yeah ok.... |
|
| Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1193
Location: Dublin, Ireland
|
|
|
|
You already know the layout of the row you want - how you get the fields from the various records into the correct positions in the .csv file is something you have to decide. I'm not going to write the code for you - this is a help forum not a do-it-for-me forum and I reckon I've given you plenty of help with this task so far.
Garry. |
|
| Back to top |
|
|
sakrat
Active User
Joined: 05 Feb 2014
Posts: 164
Location: India
|
|
|
|
| Garry Carroll wrote: |
You already know the layout of the row you want - how you get the fields from the various records into the correct positions in the .csv file is something you have to decide. I'm not going to write the code for you - this is a help forum not a do-it-for-me forum and I reckon I've given you plenty of help with this task so far.
Garry. |
I didn't ask you in that way, you didn't understand what I was asking you any way I will try how to get a proper csv file from code that will work.... Thanks a lot for helping me so far.... |
|
| Back to top |
|
|
David Maher
New User
Joined: 17 Aug 2015
Posts: 3
Location: Australia
|
|
|
|
My comments from another blog ...........
"OK, here's the deal.
There is a slight variation between Lotus Notes and OUTLOOK when emailing a CSV File as an attachment.
Using Lotus Notes you would have the SMTP Header information concatenated ahead of a .CSV Data file, and this works fine.
With OUTLOOK there needs to be a blank line inserted between the SMTP Header and the CSV Data for OUTLOOK to recognize the data.
If there is not a blank line OUTLOOK will attach an EMPTY Excel Spreadsheet." |
|
| Back to top |
|
|
|
|
