Extracting Data to Excel From Your IBM i Server Using CoolSpools
CoolSpools for IBM i Developers
If your company has a development team then they can build simple CL programs to call the CoolSpools command CVTDBFXLSX and populate Excel spreadsheets with data, either directly from your IBM i database files by specifying FROMFILE(filename) or by using a SQL SELECT statement with parameters FROMFILE(*SQL) and SQL(‘SQL SELECT Statement‘). Once the CL program has been compiled your developers can add it to a menu for interactive execution or to a job scheduler for automated processing.
CoolSpools for End Users
Easy Conversion of IBM i data to Excel: Even if you do not have a development team, CoolSpools will allow your end users to easily convert IBM i data to Excel. All that is needed is a basic understanding of your application’s database. If you know the name of a database table, then you can quickly convert it to Excel, for example if your system includes a product table named ST_PROD then a user can run the command CVTDBFXLSX FROMFILE(ST_PROD) to convert the entire table to an Excel spreadsheet named ST_PROD.xlsx in their home directory on the server IFS. If they want to select specific columns or rows then that can be done by adding a couple of extra parameters. For example, if they only want to populate Excel columns with the product code and name, and to only include rows with a product type of 1 then the required command becomes CVTDBFXLSX FROMFILE(ST_PROD) TOSTMF(‘/extracts/products_type_1.xlsx’)INCLFLD((PDCODE) (PDNAME)) QRYSLT(‘PDTYPE *EQ 1’). Note that in this example we have also used the TOSTMF() parameter to specify the path and name of the file to be created on the server IFS.
Using CoolSpools with Query/400
If you need to select data from multiple database tables, but are not comfortable with the intricacies of SQL SELECT statements, then your users can combine CoolSpools with IBM Query/400 to achieve this. Query/400 is a native query tool for the IBM i platform that is accessed using command WRKQRY and is well known to most IBM i system users. If you use the CVTDBFXLSX parameters FROMFILE(*QRYDFN) and QRYDFN(queryname) then you can convert the output of any QUERY/400 Query to Excel. This could be a Query that you create specifically for the extract, or any of the Queries that already exist on your server. Try running command WRKOBJ OBJ(*ALLUSR/*ALL) OBJTYPE(*QRYDFN) to see how many Queries your users have already created. For example, if you have a query named STOCKRPT that lists stock levels by warehouse, then you can use the following command to convert the output of that query to Excel: CVTDBFXLSX FROMFILE(*QRYDFN) TOSTMF(‘/extracts/warehouse_stock.xlsx’) QRYDFN(STOCKRPT *NO *DETAIL). Note that the additional optional parameters *NO and *DETAIL indicate that a selection window allowing entry of selection criteria should not be displayed, and that the Excel spreadsheet should only include detail rows, even if the Query has break levels and summaries defined.
Complex Excel Structuring and Formatting
Easy Conversion of IBM i data to Excel: If all you need are simple extracts of data from your IBM i server then the commands described above should be all that you need, but once you are proficient in using the CoolSpools command CVTDBFXLSX, you can try using additional parameters to control the structuring and formatting of the cells within the Excel spreadsheet. These options are described within the CoolSpools User Guides.
Using CoolSpools with Excel Charts
While CoolSpools does not provide the ability to generate Excel Charts, it does allow you to replace a worksheet within an existing Excel workbook. This means that you can create an Excel workbook consisting of two worksheets, one containing data and a second containing one or more charts based on the data in the first worksheet. If you then run CVTDBFXLSX FROMFILE(filename) TOSTMF(‘extract_with_charts.xlsx’) STMFOPT(*RPLXLSSHT) RPLXLSSHT(data) then this will replace the content of the worksheet named “data” but will leave the other worksheet “charts” untouched.
Download CoolSpools FREE for 30 days
Take a free test drive of the entire CoolSpools Suite, including converter, database, email and admin FREE for 30 days.
