excel file from rpg as400 printer file

5 pts.
Tags:
AS/400 Printer File
Microsoft Excel
I need to take an AS400 Printer file and create an Excel file.

Answer Wiki

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

I doubt that you’ll be happy with the results.
Each row would be a single char cell.
What other options do you have?

Discuss This Question: 7  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
  • MurrayInfoSys
    Junglemary, The previous response is correct. Depending on the heading of the report and other things; the report may all get placed in column 1 or randomly placed in random columns. It ain't pretty. We have solved the problem by creating a second file in QTemp that represents the columns of the report and then coping it to the IFS. This solves two problems, format and audit. Format means that the columns have consistent - read meaningful headings. Audit means that the printed report is the SOURCE for the created Spreadsheet. MIS
    940 pointsBadges:
    report
  • Bigmac46
    1. create the report with no headings or only on page 1 2. open Excel and create the headers needed- or allow it to drop them in based on the spacing selected in next step. 3.in Excel - data import external data import data select the file use the import wizard to set up the columns and where to start the import from. It Is a manual process but you can get an Excel file with the proper format.
    1,000 pointsBadges:
    report
  • philpl1jb
    bigmac46 Did you import into excell directly from the SPOOLFILE? or 1. create a physical file CRTPF MYLIB/MYFILE RCDLEN(132) 2. copy the spool file to the physical file CPYSPLF
    49,590 pointsBadges:
    report
  • Obierun
    This is just a quick and dirty alternative. I have changed the external printer file used for the report by placing a comma after every field that I wanted to be in its own cell. Compile the printer file and the program, and run it to get the new printed report with commas. You need this in a physical file, so I did a CPYSPLF to a physical file. From there I copied that physical file to the IFS (cpytoimpf) with a .CSV extension. My pc is set to open CSV files with Excel. Double click the file and you are there. It is not the prettiest, but after the initial change to the printer file a CL can do the above steps easily.
    60 pointsBadges:
    report
  • philpl1jb
    Great solution, we don't know what the user can/would do. But you have to remove any edit codes that produce comma's 100,000.27 would be processed as one cell of 100 and another of .27
    49,590 pointsBadges:
    report
  • Bigmac46
    This depends on user 's rights/access to sysytem. I create a shortcut for user on desktop that use iSeries Navgator and point to users reports/outq/or in some cases all reports in a specific outq. They can drag and drop reports to the desktop and do whatever they want to do using Excel. Other times we just create a file with no hdrs and allow them to use a run only download to pull into excell using the 'Excel Transfer From' option. Sometime we create the file , download it and open Excel on the PC with the file created from a menu option if the file creation is not to long and the # of records transfered is within a reasonable # so the interactive process is not too long. I t is a long runnning program we send them a message that the file is ready to download and to take Option #?? from MENUAAAAA. These are ways we create Excel files and/or Word documents. Based on responses use different methods/procedures to acomplish the same. What works best for you may be one of the other methods. Have FUN.
    1,000 pointsBadges:
    report
  • TomLiotta
    The simplest way to get a spooled file into a form that Excel can handle is through the Qshell catsplf utility. The basic form would be:
    QSH CMD('catsplf -j 086855/JobUser/JobName SplfName nnn > xmpsplf.txt')
    The job name is given by the -j parameter. In this case, the spooled file is from job "086855/JobUser/JobName". The name of the spooled file in the example is "SplfName". The spooled file number is "nnn" (which needs to be replaced by the actual number.) The example redirects the catsplf output to a streamfile named "xmpsplf.txt" in the current directory. This is the file that Excel would open. CL can build the QSH command string by concatenating all of the values together. The Qshell documentation for catsplf gives general details. There are a couple minor problems. If you create the output streamfile directly with catsplf, the CCSID will be the job CCSID. Excel isn't likely to make much sense of EBCDIC characters. But you can follow the QSH command with something like this:
    CPY OBJ(xmpsplf.txt) TOOBJ(outsplf.txt) TOCCSID(*PCASCII) DTAFMT(*TEXT)
    That will copy the xmpsplf.txt streamfile to a new streamfile named "outsplf.txt", and the new streamfile will have a CCSID that is compatible with Windows. (You might have NetServer configured to do auto-conversion, but it can be better to control it yourself. There are also other methods of controlling the CCSID.) In short, you create a temporary streamfile version of the spooled file with catsplf. Then you create a version that is acceptable to Windows with CPY. The examples all use the current directory of the job. Different users should have different current directories. You can control the job's current directory with the CHGCURDIR command if necessary. Think it through in order to keep different jobs from using the same file names. You can specify the full path if you don't want to rely on current directory. I don't have Excel loaded on this PC, but I do have Open Office. The streamfile imports fine into OO. There might be one more oddity for Excel in that catsplf uses LineFeed (*LF) as its line terminator rather than CarriageReturn/LineFeed (*CRLF). That's because Qshell is a Unix-compliant feature rather than Windows-compliant. If it's a significant problem, then Qshell can also be used to modify the end-of-line terminators -- but that's a different question. This solution might be a temporary one for you. Create a basic ILE CL procedure to use for testing to see how it works. If a really detailed solution becomes necessary (perhaps by using other solutions in this thread), the temporary proc can be gutted and replaced with more sophisticated code. 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