AS/400 file to Excel with Headers and Footers

35 pts.
Email attachments
Microsoft 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?

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: 6  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.
  • 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:
  • 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. Sloopy
    2,195 pointsBadges:
  • 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:
  • kishkar2014
    I want to export data from VB9 with Header footer . How to do it
    20 pointsBadges:
  • azohawk

    The earlier advice is good, but several of them involve the $pend$$ command, which seems hard to get.

    With no access to the $pend$$ command, I have either used a program or a query/400 to populate the headers first (comma delimited) and then added the comma delimited data into a flat file. Then send that file as indicated in the initial question.

    Not saying this ideal/best, but it is cheap and worked for me.

    4,055 pointsBadges:
  • CharlieBrowne
    Another option is to use a MACRO with the excel spreadsheet
    62,385 pointsBadges:

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.

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


Share this item with your network: