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.
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: Spooled_file_nameSpooled_file_number.txt Where Spooled_file_name is the name of my spooled file and Spooled_file_number 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.
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.
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. Email Joe for a free quote for any upcoming projects. 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 Admin Alert column since 2002. Check out his blog where he features practical information for tech users at joehertvik.com.
|
