Midrange News for the IBM i Community

Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
SPOOL to Excel?
has no ratings.
Published: 14 Jun 2012
Revised: 23 Jan 2013 - 3445 days ago
Last viewed on: 27 Jun 2022 (7291 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SPOOL to Excel? Published by: Bob Cozzi on 14 Jun 2012 view comments(6)

What do people use to convert a legacy SPOOL file (report) to an Excel file for management?

I'm considering just using Web Query which (I assume) can do this magically, but didn't know if there were some trick to getting an existing, old SPOOL file report into Excel with all the columns defined.

Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: clbirk
Premium member *
Comment on: SPOOL to Excel?
Posted: 10 years 15 days 23 hours 30 minutes ago

Typically if there is a report that they want in excel format, we will write out a flat file that either can be imported into excel or a csv file. This avoids all the headers on each page to go through and remove, etc.

There are some pc tools out there that you can take a report and do such but my mind is blank. And the one even will do it from pdfs of a report.


Of course yes we do occasionally go and "copyprt" off a report to a flat file and sometimes will go and remove all the blank lines, headers, etc.



Posted by: DaleB
Premium member *
Reading, PA
Comment on: SPOOL to Excel?
Posted: 10 years 15 days 21 hours 45 minutes ago

There are 3rd party products that do this, though I've never used one of these tools. I believe they involve mapping - you tell it what columns have what fields, and so forth. Presumably they're using the QSP* API's to read the contents directly, or possibly just CPYSPLF and then processing the file contents.

Before doing a CPYSPLF and then reading the resulting file, I'd consider changing the program that produces the report to create a *STMF instead (or in addition), then send the *STMF as an e-mail attachment. CSV is generally good enough for the content, though I've also toyed with open source Java that can create and edit Excel files. Depends on how fancy you need to get.

Posted by: captndjc
Premium member *
Comment on: SPOOL to Excel?
Posted: 10 years 15 days 19 hours 55 minutes ago

Take a look at DRV's Spoolflex. I've used this in the past and it's a slick product to convert reports to excel, pdf, email, or many other options.

Posted by: bobcozzi
Site Admin ****
Comment on: SPOOL to Excel?
Posted: 10 years 15 days 1 hours 33 minutes ago

I've got another client who uses Web Query, so maybe I'll use that opportunity to learn Web Query and just use it with my previous client.

Posted by: JOHND1
Premium member *
Milford, Ma
Comment on: SPOOL to Excel?
Posted: 10 years 11 days 20 hours 16 minutes ago

I also use Spoolflex, very flexable.  You can convert the report to xls, pdf, csv, html, txt, rft, ect. You can send to single receipent, the person who created the report or you can set up groups

Posted by: TFisher
Premium member *
Comment on: SPOOL to Excel?
Posted: 10 years 11 days 6 hours 58 minutes ago

We convert spool files to HTML, PDF, TIFF, or plain text files.  We do not waste time trying to reverse engineer for the purpose of creating a data file that we can work with.  Besides, many of our reports contain overrides that would be lost.  Spool files are treated like an "image file" here and we do not change them...even though we "could" if we really wanted to.


We convert data files (PF) to CSV, HTML tables, or XLS.


However, it sounds more like you are wanting to create an XLS from query results.  We use ASC Sequel here and it allows an XLS to be created.