Multiple users with varying abilities need to download IBM iSeries Spooled files for analysis to Excel daily.
We would like the general population who generate spooled files to be able to export into XLS please.
Currently the options for download from printer output to Excel are 2 part:
1. Export the data from IBMi to .txt and/or .pdf.
***** We would like to be offered export to XLS please?
2. Then they strategically open Excel, open the .txt and position below the title and column header and map the fixed length data into cells within Excel. Which requires skill patience and most importantly TIME.
This additional human work to convert to xls/csv is recurring, inefficient and exacerbating users that have enjoyed direct Excel access to IBM I spooled files. It would be fabulous if analyzing our IBM reports were the easiest thing we do daily and we had time to use our brains to analyze instead of retrieve data.
1. We want to right click on our spooled file and download to XLS in addition to .txt and .pdf.
2. Can APIs be made available to enable transform of spooled files from IBMi to Excel , Lotus 1-2-3 or CSV?
a. Please dump the report column heading and the underlying data into individual spreadsheet cells.
3. If we have to write maps; can Data Transfers be saved describing the data map from spooled file Report Column Data into the spreadsheet columns for re-use please?
Note:
From my perspective offering this option in ACS Printer Output would be best because it seems most available to the general/basic user. Also, mot all users are granted access to Navigator for I.
Alternately
A. I understand that there may be news that we can get a spooled file in a database file for use with ODBC connection. Perhaps there is common development that would benefit ACS Printer output.
B. Revive the old cwbtfxla.xll Excel Add-in and/or rewrite it for Java to work within ACS.
https://www.ibm.com/support/pages/adding-data-transfer-add-microsoft-excel Can the Navigator for i 'export/export all as CSV'.
C. In Navigator - Include the spooled file content for import to Excel in addition to (or instead of producing the printer output 'list')?
One current workaround is to 'export as ascii text' and import (copy/paste the text) into the spreadsheet. Some of the reports are massive and select all copy and paste is just too big.
See this link; an example of how easy we envision transforming/converting IBM is spooled files to CSV or Excel. This is a 3rd party product from Quadrant Software spool Explorer Youtube Hyperlink that looks like IBM functionality:
https://www.bing.com/videos/search?q=youtube+ibm+iseries+Operating+System+Spooled+File+to+Excel&docid=608043644528758679&mid=D2AF7A6016904164B0ECD2AF7A6016904164B0EC&view=detail&FORM=VIRE
Has IBM provided APIs to enabling the functionality in use by Quadrant Software?
Use Case: Use case:
10s of thousands of financial reports are produced daily on IBM I for financial, distribution, etc industries by direct users and/or scheduled jobs. Day End reporting for financial institutions can generate daily 1,000 reports per institution.
Every day for every report needed in a spreadsheet thousands of users:
1. Save the text file locally.
2. Open a New Excel SS a. Click on Open
b. Navigate to the desired .txt/.csv
expand the file name to include .prn .txt .csv and click OPEN
c. Step through the Import Wizard using Fixed Width. i.
Steps 1 – 3 to map the report detail into excel cells. (this is very tedious) where the user clicks to designate the begin and end of each column.
I have worked in many industries and the case is the same for education, distribution, real estate and finance. Data analysis is performed with Excel or CSV by nearly all of us. Please help to make the general population more efficient and/or make us smarter.
.Due to the effort required, and the availability of 3rd party solutions, we are closing this RFE.
There is little to no chance that we would be able to provide an .XLS, or even a CVS transform. An XML transform is under consideration, but is not yet on our "short list".
I am not sure that a transform is the right approach to this challenge. I am intrigued by your idea to leverage the iACS OCDB connection support to export DB files to Excel. If you defined a db file with fields that aligned the text columns in the spooled files, would CPYSPLF to this db file then put the text data in the appropriate records and fields for export to a Excel file? It would require the spooled file to be *SCS, and the columns to be separated with spaces, rather than inline move or tab controls, for it to have a chance. It is worth further discussion with our team. (Some of these folks are fiendishly clever.)
Another approach would be to write a User Print Driver that is specified on a printer device description. This driver could take the buffer of data passed to it by our writer, call the HostPrintTransform API with the QWPDEFAULT wscst object to convert it to ASCII, then replace each string of spaces in the buffer with a comma and write it to an IFS file. The resulting cvs file could then be imported to Excel. The wscst object could be modified to output markers when an relative or absolute inline and baseline moves are encountered. This would allow the driver to support *AFPDS and additional *SCS files, as well as the simple *SCS files. We provide an example user print driver in the User tools/example library.
The Quantum Software you referenced looks to provide exactly the solution you seek.
Due to processing by IBM, this request was reassigned to have the following updated attributes:
Brand - Servers and Systems Software
Product family - Power Systems
Product - IBM i
Component - Print
Operating system - IBM i
Source - Other
For recording keeping, the previous attributes were:
Brand - Digital Services
Product family - developerWorks
Product - RFE Community
Component - Create RFE
Operating system - IBM i
Source - Other