Manually converting an IBM i report into an Excel spreadsheet is messy. The process is time consuming, a waste of technical & financial resources and can produce unreliable data.
In my opinion, it’s reckless and financially dangerous to manually convert existing IBM i reports into Excel XLSX files for analysis, especially when there are good third-party report automation tools (paperless reporting) that can accurately do the job for you.
Want proof? Here are the three deadly errors most companies encounter when manually converting IBM i spooled file data to Excel spreadsheets, and how good paperless reporting software avoids these errors.
Error #1: Jumbling Report Data in a Spreadsheet
Multi-line data is the bane of successful spooled file conversion. A typical ERP system report will produce line item detail that doesn’t translate well into spreadsheet format.
Here’s where the magic doesn’t happen.
Many ERP reports work on a level break basis. For example, a report will tabulate and print all data (i.e. order totals) for a key parameter such as customer number.
These reports generally print all the line item detail for orders under a customer number (a Level 2 break). Order detail is printed on one or more lines, without the customer number printed on each line. When the report gets to the next customer number (a L1 break), it totals all the detail for the previous customer and then starts printing order detail for the new customer numbers. The result is a report that looks like this:
Line item detail for order #1, line 1
Order totals for Customer 10567
|Customer number: 10568
Line item detail for order #3, line 1
Order totals for Customer 10568
This makes for lousy manual IBM i report-to-Excel spreadsheet conversion. Each line will be converted into its own spreadsheet row without a unifying customer number for sorting or comparison. A programmer or financial resource usually has to cut and paste the downloaded data to insure the right order line details wind up with the right customer…a process that’s prone to wasting time and corrupting data, especially if your resource makes a mistake.
Good paperless reporting programs like my SpoolFlex and Reportflex software, convert jumbled data by combining individual line data into a single Excel spreadsheet row (that can be sorted) for each L1 customer and L2 order number pair (or other paired data). No need to have a dedicated resource cut and paste this mess. The software does it for you, saving resource time, money, and significantly reducing corruption errors.
Error #2: Formatting Errors
There’s no guarantee that manually converting an IBM I spooled file to spreadsheet format will produce readable or accurate output.
Here are the most common errors that can occur when manually downloading IBM i report data to an Excel spreadsheet.
- Negative numbers can be unreadable because the spreadsheet may not recognize the way the number was formatted. The minus sign may be in the wrong position, or the number might be bracketed or otherwise marked in a way the spreadsheet doesn’t recognize.
- Text data may break in the wrong position, separating names, descriptions, etc. into two different Excel cells.
- Numeric data can break in the wrong position or a digit can be cut off, changing numeric values and causing erroneous data to be downloaded to the spreadsheet.
In these cases, you have to rely on someone manually reviewing the converted Excel spreadsheet and correcting these problems when they occur. Worse, some errors may be overlooked, and corrupted data could slip into your analysis.
Report automation software solves this issue by defining where a report column begins or ends during a conversion, insuring that the data in your IBM i spooled file report is the same data that’s downloaded to your Excel spreadsheet.
Error #3: Additional Analysis
Once report data is downloaded to Excel, a financial analyst may perform additional calculations looking for data that exceeds specific limits, consuming additional time and effort.
Spooled file conversion software solves this issue by performing additional calculations and highlighting downloaded data that meets your selection criteria. It can also compare data in two rows to be downloaded. If any data points exceed the analysis parameters, those cells can be highlighted with colors to catch an analyst’s eye. Rather than massaging exported data further inside Excel to look for pain points or outliers, spooled file conversion software can perform additional analysis and highlight significant data for the reviewer’s attention before it gets downloaded.
Standardized, Reliable, and Efficient
These common mistakes can be avoided by using good paperless reporting programs, guaranteeing a standardized and easy IBM i spooled file report-to-Excel file conversion method, producing reliable output, and saving conversion time. Most packages provide other valuable features to prep spooled file reports for conversion to Excel, including creating report tabs, reformatting column headings, finding and replacing values, and converting numbers into percentages.
If you’re ready to leave behind the world of manual IBM i report-to-Excel downloads, contact us at DRV Tech for help in automating your conversion process. And don’t forget to check the helpful articles I’ve listed below that are related to this post.