Replacing KemeTECH ConversionSuite

Replacing KemeTECH ConversionSuite

From 2001-2022 KemeTECH Systems Inc provided handy tools to convert data between your IBM i database and Excel with commands such as CVTDBFXLS. Unfortunately KemeTECH are no longer providing support for these tools and it is no longer possible to obtain new software license keys, which is a significant issue for sites planning to upgrade to new IBM POWER hardware.

Fortunately the CoolSpools Database module includes functionality to convert to and from Excel, and so is a readymade replacement for ConversionSuite. On top of this, CoolSpools provides a wealth of other functionality, such as Spoolfile Report conversion to PDF, conversion to and from XML, and native IBM i email processing.

In place of the ConversionSuite command CVTDBFXLS you can use the CoolSpools command CVTDBFXLSX to generate an xlsx format Excel file. If you did want to generate the older xls file format then command CVTDBFXL allows this, but does not include all of the latest features. The names and order of the command parameters are slightly different in CoolSpools, but it should be a simple task to replicate your current processing.

Below is a review of the differences between the ConversionSuite command CVTDBFXLS and the CoolSpools command CVTDBFXLSX. The first page of parameters when prompting the two commands are similar.

KemeTECH Parameter	CoolSpools Parameter
From file	FROMFILE(library/file *NO member) where the *YES|*NO part of the parameter indicates whether you want to interactively prompt/select field names in the subsequent parameters. CoolSpools also allows you to specify FROMFILE(*SQL) in order to use a SQL SELECT statement selecting data from one or more tables, rather than a single database table, and FROMFILE(*QRYDFN) to use an existing Query/400 query as the source of the data to be converted to Excel.
To file	No direct replacement as *STMF is assumed
From member	This is part of the FROMFILE() parameter above
AS/400 File Text	There is no replacement as this CoolSpools command only converts to an Excel worksheet, not to a database table.
From Coded Character Set Id	DBFCCSID() has a default value of *DBF to automatically identify the CCSID from the database file attributes, so this rarely needs to be specified.
Stream File	TOSTMF(/path/filename)
Multiple Sheet Indicator	CoolSpools allows you to specify the name of the worksheet to be generated within parameter EXCEL() and you can automatically split the output to multiple worksheets based on a change of field value, but you will need to create a “map” using command WRKDBFXL. Maps are typically used when the required Excel format is more complex (e.g. multiple header, detail and total line structures rather than simple columns).
Fixed Headings	You can specify which Excel header rows should be frozen within parameter HEADER()
Number of Fixed Columns	You can specify which Excel columns should be frozen within parameter EXCEL()
Row Formula	To add a total row, parameter RPTSMRY() allows you to specify which columns to summarise and what function to apply (*SUM|*COUNT|*AVG|*MIN|*MAX). You can also specify parameter RPTBRKS() to insert subtotal rows on a change of value in one or more fields.

For the rest of the parameters, the way that CoolSpools selects columns for inclusion in the generated Excel worksheet is slightly different. By default, CoolSpools will include all of the columns in the database file, so you do not need to individually specify the field names unless you only want a subset of columns to be output. If you do want to explicitly specify which database fields to include then you can do this with parameter INCLFLD() specifying multiple fields as INCLFLD(fld1 fld2 fld3). Alternatively, you can specify which fields to exclude with parameter EXCLFLD() and any fields that have not been excluded will be present in the generated spreadsheet.

The header text for all fields is automatically taken from the field name, field text or column heading as specified by the HEADER() parameter. This is specified once for the whole conversion rather than for each individual field, so you do not need to specify a header for each column unless the required text is not available from the database field attributes.

If you do want to override the header text for one or more columns then you can do this using parameter APYSTYLES(). When using this parameter, the column whose heading is to be overridden can be referenced by either the database field name or the Excel column id, as shown:

APYSTYLES((*FLDNAM fld1 *DATA *HEADER ‘Field 1 Header Text’) (*XLCOLID B *DATA *HEADER ‘Field 2 Header Text’))

The values *DATA and *HEADER in the above are the “style” to be applied to this column (font, weight, color, numeric format, etc.) CoolSpools allows you to define and apply custom styles to different rows or columns, including conditional formatting for traffic-light style highlighting of cells.

If you want to include a field in the total summary row then you specify this in the RPTSMRY() parameter. For example, to output the sum of fld3 you would specify RPTSMRY((*FLDNAM fld3 *SUM))

Note that CoolSpools is free to download and install, with all new installations including a 30-day trial license, so why not try it now?