IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

fields truncated in csv file


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Wed May 13, 2015 10:45 pm
Reply with quote

Hi,
I have about 17 fields with comma separated format dataset and I am using IEBGENER to send mail with SMTP. Few Fields has width of maximum 500 that was generated using SAS. In the output CSV excel file only 13 fields are coming. The last 4 fields are missing.May I know why is it happening like that....?

The dataset record length is 1202.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed May 13, 2015 11:06 pm
Reply with quote

Quote:
Few Fields has width of maximum 500


I assume there are more than 1 field with maximum length of 500 and the LRECL of dataset is just over 1200.

Don't you think that the data may be getting truncated?

Please give more detail
.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Wed May 13, 2015 11:16 pm
Reply with quote

RahulG31 wrote:
Quote:
Few Fields has width of maximum 500


I assume there are more than 1 field with maximum length of 500 and the LRECL of dataset is just over 1200.

Don't you think that the data may be getting truncated?

Please give more detail
.



Actually out of the 17 fields one field has 540 and second one have 480, the third one has 120, remaining other fields has 10 and 20 length and all those sum upto 1202 record length. The data is not truncated in the dataset.

The dataset has all the 17 fields in correct format and length.When I use this dataset(csv formatted) and send mail using SMTP. the excl csv attachement has last four fields missing.that is, only 13 fields are coming in excel wheareas the dataet has all the 17 fields in it.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Wed May 13, 2015 11:20 pm
Reply with quote

Can you show a truncated sample of the records on the Mainframe, and also for the same records on the server?
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Wed May 13, 2015 11:34 pm
Reply with quote

Bill Woodger wrote:
Can you show a truncated sample of the records on the Mainframe, and also for the same records on the server?


The data is not truncated on the mainframe.

say for example i have below dataset in mainframe:

A,B1 IN B2 IN B3 IN B4 IN, B4 OUT +,D1 D2 D3 D4 ,E ,F,G,H

when I send the above dataset using SMTP mail,i get the below in EXCEL.CSV

col1 col2 col3 col4
Row1 A B1IN B2 IN B3 IN B4 IN D1 D2 D3 D4 E

here F,G,H is not present in excel but it is there in dataset in mainframe.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed May 13, 2015 11:35 pm
Reply with quote

I am a little puzzled. 540+480+120=1140

So, now you have other 14 fields of 10 and 20 lengths that you are adjusting in just 62 bytes?

Moreover, there will be 16 ',' in the file. So, that makes you to adjust 14 fields in 46 bytes now.

Did you account for the ',' ?

I assume you did everything. So, as Bill says, show us some sample data and the job details
.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Wed May 13, 2015 11:59 pm
Reply with quote

here is the length of each field below:

A - 3
C - 1
B - 8
C - 1
D - 3
C - 1
E - 8
C - 1
F - 540
C - 1
G - 120
C - 1
H - 3
C - 1
I - 4
C - 1
J - 1
C - 1
K - 480
C - 1
L - 4
C - 1
M - 4
C - 1
N - 1
C - 1
O - 4
C - 1
P - 4

In the aboce C means for COMMA.... All other letters corresponds to the field and their length....In this I get all the field values properly in the mainframe but it is sent mail, The excel.csv file has fields only upto K field and fields L,M,N,O,P are not present in the excel sheet.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Thu May 14, 2015 12:15 am
Reply with quote

You said 17 fields but you show only 15.
You said 4 were missing and now you tell 5 are missing.
You said 13 were coming in the excel and now you intend to say only 10 are there.
You said 10 and 20 length and now you show a single digit length.

And you are NOT showing the job yet.

Please show the Job.

.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Thu May 14, 2015 12:19 am
Reply with quote

AND the sum of the above 15 fields + ','s = 1201

So, there can't be more than 15 fields. You better need to clarify.
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Thu May 14, 2015 12:26 am
Reply with quote

And please use Code tags to preserve accurate spacing when pasting.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu May 14, 2015 12:28 am
Reply with quote

RahulG31 wrote:
AND the sum of the above 15 fields + ','s = 1201

So, there can't be more than 15 fields. You better need to clarify.



yes it is fixed....
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Thu May 14, 2015 12:30 am
Reply with quote

'What' is 'Fixed' ?

.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu May 14, 2015 12:34 am
Reply with quote

RahulG31 wrote:
You said 17 fields but you show only 15.
You said 4 were missing and now you tell 5 are missing.
You said 13 were coming in the excel and now you intend to say only 10 are there.
You said 10 and 20 length and now you show a single digit length.

And you are NOT showing the job yet.

Please show the Job.

.


job is just the mail job using IEBGENER. And sorry for telling the number wrongly i dint look at my notes properly. the one which i explained was the right one.As i dont have access to mainframe i couldnt give you the correct info the first time very sorry for that.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu May 14, 2015 12:39 am
Reply with quote

RahulG31 wrote:
'What' is 'Fixed' ?

.



there will be 15 fields and its length is fixed in the data set.
please see the below link which has SAS code that generates the dataset, which is sent as CSV formatted file.

[URL] www.ibmmainframes.com/viewtopic.php?t=64039 [/URL]
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Thu May 14, 2015 1:08 am
Reply with quote

So, it seems that your file is working only till record length 1000. Does it remind me of something? Is there a limit in SMTP for record length (which is 1000)?

Please check if you are facing truncation with the last field in the CSV which should be the 480 byte field.

And also, show your SMTP steps when you have access to Mainframe.

.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8696
Location: Dubuque, Iowa, USA

PostPosted: Thu May 14, 2015 2:01 am
Reply with quote

SMTP has a record limit of 1000 bytes including the CR/LF at the end of the line. This is listed in the RFC for SMTP, hence you are not going to be changing the 1000 limit on a line. So your choices are:
1. truncate your data down to 1000 (actually 998) bytes
2. use MIME to send longer records
3. use some other method besides SMTP to send the data
4. split your data so the lines are shorter with two lines per record
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu May 14, 2015 11:37 am
Reply with quote

So here is my Mail step:

Code:


//SENDMAIL EXEC PGM=IEBGENER,
//             COND=(0,NE)
//SYSIN    DD   DUMMY
//SYSPRINT DD   SYSOUT=*
//SYSUT2   DD   SYSOUT=(B,SMTP)
//SYSUT1   DD   DSN=***.***.CNTRL.EMAIL,DISP=SHR
//         DD   DSN=***.***.****.CNTRLM.RPT,DISP=SHR
//         DD   DSN=***.QUIT.STMT,DISP=SHR



and ***.***.CNTRL.EMAIL contains below :

Code:


HELO ***
MAIL FROM: <***.***@***.COM>
RCPT TO: <***.***@***.COM>
DATA
FROM: <*** TEAM>
TO: < *** TEAM>
SUBJECT: CONTROL-M DOCUMENTATION
MIME-VERSION: 1.0
CONTENT-TYPE: MULTIPART/MIXED; BOUNDARY="=_BOUNDS"

--=_BOUNDS
CONTENT-TYPE: TEXT/PLAIN;CHARSET="US-ASCII"

PLEASE FIND THE ATTACHED CONTROL-M DOCUMENT.
THANKS,

--=_BOUNDS
CONTENT-TYPE: TEXT/PLAIN
CONTENT-DISPOSITION: ATTACHMENT; FILENAME="CNTRL-M DOC.CSV"



***.QUIT.STMT contains

Code:


.
QUIT



***.***.****.CNTRLM.RPT contains the records of 1202 record length.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu May 14, 2015 11:39 am
Reply with quote

Robert Sample wrote:
SMTP has a record limit of 1000 bytes including the CR/LF at the end of the line. This is listed in the RFC for SMTP, hence you are not going to be changing the 1000 limit on a line. So your choices are:
1. truncate your data down to 1000 (actually 998) bytes
2. use MIME to send longer records
3. use some other method besides SMTP to send the data
4. split your data so the lines are shorter with two lines per record



Is there some other method other than SMTP, because I cannot limit the record length and I have used MIME only to send the records I hope, Please see my code I have posted.
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu May 14, 2015 11:41 am
Reply with quote

RahulG31 wrote:
So, it seems that your file is working only till record length 1000. Does it remind me of something? Is there a limit in SMTP for record length (which is 1000)?

Please check if you are facing truncation with the last field in the CSV which should be the 480 byte field.

And also, show your SMTP steps when you have access to Mainframe.

.


I have posted my Mail step codes. Please see it....As Rober sample said I think the records limit is by 1000 so if there is any other any that i could send all the records it would be good as i cannot truncate or drop the records.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu May 14, 2015 12:03 pm
Reply with quote

if You want to send something in a format different from plain text
using the zOS smpt server You will have to do the conversion Yourself
IEBGENER will NOT do it.

xmitip has an assembler program which will convert a dataset to the MIME format
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu May 14, 2015 12:08 pm
Reply with quote

I used SAS in sending the Mail, Now I am getting all the fields in the CSV file....Thanks all for your help....
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Thu May 14, 2015 12:40 pm
Reply with quote

Yes, using SAS mail is the optimal way when you already have SAS installed at your shop. SAS mail handles CSV,HTML,EXE,PDF,DOC and many other file types correctly without much jumping through the hoops.

It is like searching for ghee when you have vennai in your hand :-)
Back to top
View user's profile Send private message
sakrat

Active User


Joined: 05 Feb 2014
Posts: 164
Location: India

PostPosted: Thu May 14, 2015 5:45 pm
Reply with quote

One last doubt can we format the record when sending as csv file like I want the header row to have different background color and in Bold. is it possible to do that.....
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu May 14, 2015 6:15 pm
Reply with quote

If what you are sending the data two has a "markup" language, then yes. Just include the markup codes and the receiver will get the formatted output.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Thu May 14, 2015 6:39 pm
Reply with quote

Quote:
One last doubt can we format the record when sending as csv file like I want the header row to have different background color and in Bold.


It is 'un'possible to have formatting with ODS CSV.
Use something like ODS HTML while creating output file.

Code:
ODS LISTING CLOSE;
ODS HTML FILE='some VB PS file' style=sasweb rs=none;
proc print ...
..
run;
ODS HTML CLOSE;
ODS LISTING;

STYLE=SASWEB or MINIMAL or anything else
STYLE element gives a predefined style. If you need customized styles you may need to use PROC TEMPLATE to edit the styles.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Need help for File Aid JCL to extract... Compuware & Other Tools 23
Search our Forums:

Back to Top