Spool File Report to Excel/Data File

765 pts.
Tags:
AS/400
AS/400 Spool Files
excel
Spool files
I need to take a report from my Spool File and get the data into Excel. I try it the iSeries way with the PrinterOutput but it's dirty. no headers. is there another way to dump it into a temp file whil in the AS400 and then export that out to Excel and have a cleaner report that is in cells?

Answer Wiki

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

Links removed – not considered of any practicle use..
Phil

Discuss This Question: 9  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
    I try it the iSeries way with the PrinterOutput but it's dirty. You could send it to the Windows spooler through a remote *OUTQ or through an iSeries Access printer session. Then you could use the Windows way of getting a spooled file into Excel. After all, Excel is a Windows program, not an iSeries program. Once it's in the Windows spooler, it's available to the Windows APIs just like any other Windows spooled file. Maybe you'll find that to be easier and cleaner. Tom
    125,585 pointsBadges:
    report
  • Jaymz69
    not sure on how to do that. Can you point me in a direction or any good resource links... Thnaks
    765 pointsBadges:
    report
  • TomLiotta
    I don't know of anyone who has successfully done it. But if you think the AS/400 methods are 'dirty', you can look here for basics on detecting when spooled files are available in Windows -- Monitoring a print queue from Visual Basic.Net; and here for some info on what you'll find if you actually access Windows spooled data -- SPL - Microsoft® Windows Spool File Format. You might also want to research some of the printing changes mentioned in this Wikipedia article -- Technical features new to Windows Vista (Printing). Keep in mind that it's all proprietary to Microsoft and that there are WinNT, Win2K, WinXP, WinVista, Win7, not to mention ME, CE, 98... as well as service packs. Elements changed in all of them. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Here are a few ideas. For small and infrequent occurances - Use cut and paste. There are 3rd party software packages on the AS/400 or PC that allow you to map the data from the spool file to columns in Excel. Changes can be made to the application programs to create a physical file or to directly create excel spreadsheets. Data in db2/400 tables can be extracted, downloaded, or accessed by excel. Phil
    49,600 pointsBadges:
    report
  • TomLiotta
    @Philpl1jb: Only a couple of those links had anything relevant to this question -- neither was actually helpful since the OP already tried them before asking this question. Another link was for this question itself. One of the others gave essentially the same answer as I already gave here. The remaining links were about various products rather than about how to do it. For this question, the OP knows how to do it. But the method is somehow thought to be "dirty". (I don't know what that means, but it doesn't seem to be a good thing. I think it has something to do with "...no headers." ...Whatever that means.) I figure that anybody who can think of the AS/400 methods as "dirty" must know about how other platforms do it. Judgments can't be made when comparisons can't be made at the same time. That's why I figured that if the AS/400 way wasn't appreciated, then it must be because the OP knows that Windows does it better. So, I suggested converting to a Windows spooled file. That seemed more reasonable since Excel was the ultimate target. Of course, if the OP prefers Linux, then the AS/400 spooled file could always be routed to a Linux printer queue and Linux functions could be used to create an Excel file. Or whatever the preferred platform is, route the spooled file to it and do the work there. The OP must know some "clean" way to do it. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    This looks so easy. Spool files look alot like spreadsheets. A request of this type comes in every 5 or 6 months.from someone who doesn't realize the complety of the problem. It will be no better on the Client side, it's totally dependent of the users skills, knowledge, and tools.. Dividing the report into formated columns getting dates and numbers entered correctly, capturing headings, dealing with report totals are not trivial. Shurly, someone has written a good discussion on this topic. Phil
    49,600 pointsBadges:
    report
  • TomLiotta
    This looks so easy. I agree -- except for the “…no headers.” part. I have no clue what that means. That is, I could interpret it as referring to the first row in Excel not being a "header" row; but I can't guess why such a row would be expected from a spooled file. Or maybe something happened to the "header" lines in the spooled file. Maybe some part of the process used by the OP deleted all of the page and column heading lines. But that would be the fault of whomever wrote/created the procedure or due to using the wrong procedure. Or maybe it's something else entirely. I've never been clear on why printed reports are expected to be useful in Excel in the first place. Sure, it can be easy enough to do; but there is no way to guarantee the validity of the data. There's no way to know that what ends up in Excel actually represents the data that exists. IMO, a simple drag/drop of the spooled file to the PC followed with a text import into Excel is as good as it gets. If something more than that is needed, then the reporting procedure should simply write detail line data to a database file at the same time it prints detail lines. It would help a lot if we knew what the actual problem was. Tom
    125,585 pointsBadges:
    report
  • Jaymz69
    OK Thanks for all your input
    765 pointsBadges:
    report
  • YuVa47
    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