AS/400 file to Excel with Headers and Footers

35 pts.
Tags:
AS/400
CPYTOIMPF
Email attachments
excel
I need to attach an Excel s/sheet to an email. I currently do that via the CPYTOIMPF as a CSV file but this doesnt give give me the true Excel Header row, which is a requirement. Is there any easy way to do this?
ASKED: July 15, 2010  12:08 PM
UPDATED: July 16, 2010  8:26 PM

Answer Wiki

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

Tom, Thanks for your repsonse– but I need a true Excel Header row, where it shows summary info, where you can select or omit values, sort values, etc. I’ve created column names, but that’s insuffiicent for the user. He needs a complete, true, Excel spreadsheet.

Thanks Sloopy– I’ll take a look and give it a try and let you know. Thanks Again

Discuss This Question: 3  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
  • TomLiotta
    The CPYTOIMPF command is provided to perform DB2 exports. (CPYFRMIMPF does imports.) Since Excel isn't DB2, the command isn't directly compatible. Although the CPYxxxIMPF commands have some flexibility, they do have limits. The IBM product that performs data transfer in Excel format is iSeries Access File Transfer. Choose BIFF8 as the PC file type. When the resulting PC file is opened in Excel, column names will be in row 1. Otherwise, create a procedure to build a streamfile with a single line of comma-separated values consisting of the column names that you want. When you run CPYTOIMPF, include the MBROPT(*ADD) parameter to add your lines after your column headings. Your proc might be written in ILE RPG or any ILE language (including CL if you are at or above V5R4). There are also possibilities such as the Qshell db2 utility, although you might use the db2 utility to perform the entire procedure and skip CPYTOIMPF entirely. Tom
    125,585 pointsBadges:
    report
  • Sloopy
    See these articles - but writing Excel from RPG is quite tricky. There are packages around which you'll have to pay for, but they may be the best way if you will have to do this quite often. http://systeminetwork.com/node/60954 http://www.mcpressonline.com/tips-techniques/microsoft/techtip-excel-flies-higher-with-jexcelapi.html http://www.easy400.net/easy400p/maindown.html Sloopy
    2,195 pointsBadges:
    report
  • TomLiotta
    - but writing Excel from RPG is quite tricky. Not only is that tricky, but then comes another fun part -- e-mailing it. The question started from the perspective of a .CSV file even though it mentioned "an Excel s/sheet" which is a very different kind of file. The latest clarification drops .CSV entirely from consideration. Now, the result needs to be a true Excel spreadsheet file. But that won't e-mail over SMTP without encoding. Either an e-mail product must be bought/installed or routines to generate an encoded attachment must be obtained or written. Then the e-mail becomes possible either through the purchased product or with the QtmmSendMail API (or perhaps a javamail function). Quite a ways to go if it's not already available on the system. Tom
    125,585 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