Help needed with sending a mainframe file to users in CSV format

pts.
Tags:
390
Development
IBM DB2
Mainframe
Microsoft Exchange
Hi, My program is creating a delimited flat file on the mainframe. I want to send this file to various users using either IEBGENER or IBMFTP as a CSV file. My problem is that although the file is sent successfully as a .CSV file but when the user opens the CSV file he finds that for some of the numeric strings which are actually text the leading zeroes get truncated for e.g a string of 01234 will display as 1234 only also the date fields are not getting displayed properly. I would like to know what control statements should I specify in my control cards so that the data gets displayed correctly. I am showing below a sample control card which sends out the mail in .CSV format: HELO FMRMVS1 MAIL FROM:<Sender> RCPT TO:<Reciever> DATA DATE: FROM:Job ABCDE TO: CC: SUBJECT: Today's report MIME-Version: 1.0 Content-type: multipart/mixed; boundary="simpleboundary" --simpleboundary Hi, Please find the report attached. --simpleboundary Content-description:REPORT Contentdisposition:attachment;FILENAME="Report.CSV" Any help in this matter will be highly appreciated Thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

Can’t help you on the date, but I have a feeling they numeric suppression of leading zeros is caused by the spreadsheet program that opens the file, not a problem with the file. This assumes of course that it is a spreadsheet program that is openining the file. What does the date field look like when opening the file say with a text editor?

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DKoch67
    I've run into this before, and adding a single quote (the ' character) before the number causes Excel to read the field as text instead of a number. The text field won't drop the leading zero; a numeric field will.
    5 pointsBadges:
    report
  • Jacquie
    LeahDawn is correct. It is Excel that suppresses the zeroes. If your file was in Text format (.txt), instead of .csv, an Import Wizard walks the user through the conversion when they open the file in Excel. They can define the columns with numbers or dates as Text, instead of 'General' which solves this problem. I don't know if it's the same with a csv file, though. We always send .txt files to users.
    0 pointsBadges:
    report
  • DonQuixote
    Thanks to all of you who have taken the time to answer my question. I have added one quote to the text field for e.g. I have changed my program to make the data look like '01234' instead of 01234. This makes the spreadsheet read the data in a correct manner instead of truncating the leading zeroes. The date field though still looks like 00/12/23 although this is not a problem per se but I wanted it to display like 2000/12/23 or 23-December-2000. Thanks once again
    0 pointsBadges:
    report
  • DonQuixote
    Thanks to all of you who have taken the time to answer my question. I have added one quote to the text field for e.g. I have changed my program to make the data look like '01234' instead of 01234. This makes the spreadsheet read the data in a correct manner instead of truncating the leading zeroes. The date field though still looks like 00/12/23 although this is not a problem per se but I wanted it to display like 2000/12/23 or 23-December-2000. Thanks once again
    0 pointsBadges:
    report
  • Cglancy
    Another alternative to your date and general formatting problem would be to create an Excel 'template' formatted with how you want the data to look, especially the date(s). Send the template to your users with instructions to use a clean/empty template to copy your report into so that they all have uniform reports and the data is represented as you intended. It would probably help to explain that this 'extra' step for them is due to how Excel displays the raw data versus your formatted template. Just an idea...
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following