I need assistance with putting an output spool file from MPE/iX 7.5 into Excel or other readable format. The file is generated by Query, then processed by Editor, then sent to the printer. Instead of printing it, I want to put it into a readable format.
I do not have QEdit or any smart tools on MPE, so my approach thus far has been to move the file to a PC before doing anything. However, that carries with it the initialization sequence for the printer to which the job is spooled. The job is set up to print on a PCL 5 laser, which means it has hundreds of lines of control before the data starts.
Tom Moore replies
I would put commas in between my columns (in the query, or using Editor). I FCOPY from the file to a new file with NOCCTL to get rid of carriage control byte. You could also remove the PCL 5 lines by subset in the FCOPY command. Depending on the data, I would use EDIT3000 to change all " ," to "," and all ", ","," to compress the file, removing the spaces before and after the commas inserted above, then save the file for download to the PC.
I would also consider using ODBC to directly extract from the IMAGE database, rather than Query and all the subsequent steps. The HP free ODBC driver would do the job very well.
Birket Foster of MB Foster notes
Not only did we make that free ODBCLink/SE as HP's lab resource from 1998 to 2006, but we have continued to develop the ability to work with data in all kinds of file formats. We do supply 32- and 64-bit versions for ODBC to the HP 3000.
UDALink-MPE was designed for the HP 3000. We provide data in several different formats including XLS for Excel, XML, CSV etc. We can have a discussion about what you are trying to do with data; perhaps UDACentral is the right product for your challenge and we can organize a demonstration for you.
There seem to be at least three steps to what you are trying to do.
- Remove the headers, footers and perhaps page numbers from the report.
- Remove the ff or CNTL characters from the text file.
- Import a space-delimited file to Excel.
There are any number of different scripting tools that can do this including various Unix tools. Here's a reference to an Excel solution that might get you started. In fact, if it were my problem to solve, I would likely do it all with Excel scripting.
John Hohn replies
- Output to a delimited file (tabs, pipes, etc).
- Download to your laptop or PC or wherever Excel is running
- Start macro recording in Excel
- Import/format the delimited file, save as .xls
- Turn recording off, save macro
Set the Excel file to auto-execute the macro every time the Excel file it's opened, i.e., re-input/format the delimited file. Then you can, for example, schedule delivery of a new version of this delimited file whenever you'd like, to your server. When people open it they would automatically get the formatted version of the new data.
Connie Sellitto of Hillary Software suggests
Hillary Software has a product, byREQUEST, which does just this.
It has the ability to suppress headings on pages after the first, and define the type of data in the columns (text, numeric, dates in various formats). It can remove blank pages and leading and trailing blank lines. It can even call an Excel macro to make the headings a different font, background color, etc — anything you'd want to do with a macro. In addition to Excel, byREQUEST can create a PDF file, Word, csv or Text.