As400 Convert Spool File Excel
Admin Alert: Importing IBM i Spooled Files Into Excel September 10, 2014 Joe Hertvik For a recent audit, I had to export IBM i spooled file data into Excel spreadsheets to send to our auditors. If you have a similar need for spooled file to Excel conversion, here’s my drill for importing spool file data into an Excel 2010 spreadsheet. One Goal, Two Paths You can export spooled file data to an Excel file in two ways: by using a commercial product or by using System i Navigator and the Text Import Wizard from Microsoft Excel 2010. If you have a third-party product that exports spooled files to Excel format, then by all means use it. It will make your life much easier and avoid the manual steps listed here. But if you don’t have a conversion product available, here are three simple steps you can use to import IBM i spooled file data into an Excel 2010 spreadsheet.
On your IBM i partition, create the spooled file you want to export into Excel. Using System i Navigator (OpsNav), extract and save your spooled file data as a text file (.txt). Open your text file in Excel 2010 and use Excel’s Text Import Wizard to import your spooled file text into an Excel spreadsheet.
Here’s my cheat sheet procedure for performing each of these steps. Author’s Note: This article was tested using the System i Navigator program that comes with IBM i Access for Windows version 7, release 1, and Microsoft Excel 2010.
Also note that this technique may work differently when using different versions of IBM i Access for Windows or Microsoft Excel. Step #1: Create the spooled file you want to export into Excel. Choose the spooled file you want to turn into an Excel file. For this example, I’ll use a copy of the Work with Active Jobs (WRKACTJOB) listing for one of my systems. I can output a WRKACTJOB screen by running the following command. WRKACTJOB OUTPUT(.PRINT) If I had a third-party product that allowed me to export a spooled file into Excel format, I would just use that software for the Excel 2010 conversion and leave it at that. But if I don’t have a spooled file→Excel converter, I would usually perform the next two steps to convert my spooled file text into an Excel spreadsheet file.
Step 2: Using System i Navigator (OpsNav), extract and save your spooled file data as a text file (.txt). System i Navigator (affectionately known by its original nickname “OpsNav”) has a quick and easy feature that lets you convert spooled files to.txt files. You can access that feature by doing the following.
Go into OpsNav and open the System→Basic Operations→Printer Output node for your target IBM i partition. You’ll see a screen that looks like this. ( Click graphic to enlarge.) Click on the spooled file that you want to convert into an Excel file and drag and drop that spooled file on to your Windows desktop. This will automatically create a text file that is named after the spooled file you just dragged to the desktop. In this case, I’m dragging the QPDSPAJB output file I created from the WRKACTJOB command to my desktop.
Once the spooled file reaches the desktop, OpsNav and Windows will save the report text (complete with formatting) to a text file with the following naming format: SpooledfilenameSpooledfilenumber.txt Where Spooledfilename is the name of my spooled file and Spooledfilenumber is the spooled file number of my target spooled file. In my example, the text file containing my spooled file data was called QPDSPAJB751753.txt. Step #3: Open your text file in Excel 2010 and use the Excel Text Import Wizard to import your spooled file text into an Excel spreadsheet. Do the following to import your spooled file data into an Excel 2010 spreadsheet.
Go into Microsoft Excel and click on File→Open from the menu bar. Change the file type to look for Text files (.prn,.txt,.csv) and locate the desktop text file you created out of OpsNav. When Excel 2010 opens your text file, it will automatically execute and show you the first screen of the three-step Text Import Wizard. This screen will look like this. ( Click graphic to enlarge.) This screen shows you: 1) the location and name of the file you imported (C:UsersjoehDesktopqpdspajb751753.txt in this case); 2) the starting Excel spreadsheet row that you want to import the spooled file data into (the Start import at Row: input box); and 3) the field type that best describes your data. (The Choose the file type that best describes your data radio buttons.) Select the Fixed width radio button for your file type.
Fixed width refers to file data that is aligned in columns with spaces separating each column or group of data (when I converted my WRKACTJOB spooled file into a text file, OpsNav retained the exact formatting of the original QPDSPAJB file the text file was created from). Fixed Width is the correct choice for an IBM i spooled file text import. Click on the Next button to go to step two of the Text Import Wizard. This screen looks like this: ( Click graphic to enlarge.) Here you can modify where you Excel spreadsheet columns will separate the data and how your text file data is mapped into the spreadsheet cells.
The Text Import Wizard will make its best suggestion as to where the data will be broken into columns as it is placed into your Excel cells. Scroll down to where the columnar data is shown and then move the columns to best accommodate and display your text data. You can also double-click on any of the column break lines to delete a column break. When you’re satisfied with how Excel will import the spooled file data into your spreadsheet, click on the Next button to go to step three of the wizard. The third screen will look like this. ( Click graphic to enlarge.) The third screen allows you to format the data in each of your proposed columns. You can format each column according to the following options.
General–Converts numeric values to numbers and dates to a calendar format. All other column values are treated as text. Text–Treats the column as straight text without any other modification. Date–Treats column items as date items, formatting each date according to format listed in the attached dropdown box. Do not import column (skip) –Leave this column off the exported spreadsheet. Format each column as desired.
Excel uses General as its default value. Click on the Finish button when you’re finished formatting your columns and ready to import the data into your spreadsheet. This will import your data into the spreadsheet according to the parameters you specified in the wizard. Not Quite Finished Yet While the Text Import Wizard does a great job of mapping spooled file data into Excel spreadsheet cells, it may not perfectly fit the data to your spreadsheet. After importing your data, you may have to make the following adjustments to your imported data. You may need to increase the width of your columns to accommodate data that is longer than the column breaks you specified in step two of the text import wizard. If too many values fall outside one of your columns, you may even want to reimport the.txt file data with different column widths.
Headlines or other non-columnar data that was included in the original spool file may be broken up or mapped incorrectly in the Excel spreadsheet. You may either want to delete the cells or rows that include non-columnar data (if appropriate) or edit the spreadsheet so that headline text shows correctly in your spreadsheet. After modifying for these column changes and headline spacing, here’s what my imported WRKACTJOB spooled file looked like after I imported it into Microsoft Excel 2010 using this technique.
( Click graphic to enlarge.) And that’s all there is to importing spooled file data into a Microsoft Excel spreadsheet. Joe Hertvik is an IBM i subject matter expert (SME) and the owner of Hertvik Business Services, a content strategy organization servicing the computer industry. He also runs a data center for two companies outside Chicago, featuring multiple IBM i ERP systems. Joe is a contributing editor for IT Jungle and has written the column since 2002. Check out his blog where he features practical information for tech users.
Sponsored By WORKSRIGHT SOFTWARE Do you need area code information? Do you need ZIP Code information? Do you need ZIP+4 information? Do you need city name information? Do you need county information? Do you need a nearest dealer locator system?
We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you! The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support.
The cost is $495 per year. PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes.
PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal. Just call us and we'll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4. WorksRight Software, Inc.
Phone: 601-856-8337 Fax: 601-856-9432 E-mail: Web site.
Important Note: This document discusses Client Access for Microsoft Windows 95 and Windows NT, Client Access Express, IBM iSeries Access, and IBM System i Access products. These names essentially refer to the same product; however, the functionality and name changed over the last several releases. For the purposes of this document, the terms Client Access, Client Access Express, iSeries Access, and System i Access can be used interchangeably. Where a difference is important, the version of the product is used to identify the differences. Client Access has several ways to transfer operating system spooled files to ASCII text PC files.
Copy the spooled file to the PC using Operations Navigator (available with Client Access V3R2M0 and Client Access Express) or iSeries Navigator (available with iSeries Access for Windows and System i Access for Windows). Convert the operating system spooled file to a database file and then use Client Access Data Transfer or Network Drives to download the file to the PC. Note: The preferred method is to use Operations Navigator or iSeries Navigator to copy the spooled file to the PC as follows.
Open Operations Navigator or iSeries Navigator and expand the desired IBM® System i™ system by clicking on the plus (+) sign. Expand Basic Operations by clicking on the plus (+) sign. Double-click on Printer Output and it will bring up the spooled files associated with the profile you are using to make your connection. Right-click on a spooled file and drag it to your desktop or to a desired directory available in Microsoft® Windows® Explorer. Select Copy here and the file will be copied to the desired location. The file can then be opened in Excel or a preferred application that can open text documents.
The following steps cover how to convert a spooled file on the System i system to a physical file on the System i system so that it can be transferred to the PC using Client Access Data Transfer or Network Drives. Create a physical file to hold the data from the spooled file.
Use the command CRTPF, and select a record length equal to the number of characters in the longest line. For example, if the spooled file has 132 columns of printed data, the physical file should be created with a record length of 132. Note: By default, the CRTPF command limits the size of the physical file to 10000 records with three 1000-record extensions, yielding a limit of 13000 records.
If the spooled file contains more than 13000 records, set the Initial Number of Records to.NOMAX. The physical file can now be transferred to a QDLS shared folder using CPYTOPCD, or to an Integrated File System directory with the CPYTOSTMF or CPYTOIMPF command. The physical file can be transferred directly to a PC drive using the Client Access/400 or iSeries Access data transfer function (see Client Access/400 for Windows 95/NT Data Transfer Function below) or Client Access network drives with the EBCDIC-to-ASCII conversion feature. IBM i Access for Windows Data Transfer Function When a physical file is created with no DDS (as in the example above), it is created as a program-described physical file. One of the traits of program-described physical files is that they are created using the generic catch-all binary CCSID (Coded Character Set Identifier) 65535.
Many Client Access or iSeries Access users have no idea what a CCSID is or why it is significant. The Client Access for Windows 95/NT and iSeries Access data transfer function is based on IBM DB2/400 SQL; therefore, the CCSID is significant. If in doubt, the CCSID may be confirmed using the DSPFD command from an operating system command line. For example, the following command: DSPFD MYLIB/MYFILE produces the following results: On the third screen of information there is a Coded character set identifier. As expected, the CCSID value for this file is 65535. This value is not always on the third page/screen; therefore, you may have to search through the output for it.
As400 Pdf Conversion
As mentioned above, the 65535 CCSID is a binary/catch-all value. The AS/400 National Language Support manual, SC41-4101, offers the following description of this CCSID. Special Note: Character set identifier (CCSID) 65535 is used to show that the associated data should not be processed as coded-graphic-character data. Data associated with CCSID 65535 is to be interpreted as 'actual representation is unknown'. CCSID 65535 is the default for the QCCSID system value. Because Client Access/400 for Windows 95/NT data transfer is DB2/400 SQL-based, it honors this CCSID and treats it as data that cannot be converted. Client Access file transfer functions from previous clients always converted the data by making a best guess about what language to convert the data to.
As400 Convert Spool File Excel Free
This was based on the CCSID of the user's job and the code page in use on the PC. For Client Access for Windows 95/NT, Client Access Express, and iSeries Access for Windows, the user must specify to force the translation between EBCDIC and ASCII when dealing with CCSID 65535 data. How Client Access for Windows 95/NT and iSeries Access data transfer is configured to do this depends upon the version, release, and modification level (VxRxMx) of Client Access being used. ISeries Access V3R1M3 or later: Starting with Client Access V3R1M3, a switch was added to the data transfer GUI. This switch is still available in iSeries Access data transfer. From the File menu in a Data Transfer session, select Properties. The following dialog opens: Check the CCSID 65535 conversion box and the data will be converted from EBCDIC to ASCII when the transfer is run.