iSeries programmers are spending an increasing amount of time preparing data for PC users. Often, data is prepared for use in a spreadsheet such as Lotus 1-2-3 or Excel. Ease of creation, quality of output, and security are important factors in this process.

The Client Access data transfer utility has always been a good tool for this task with the caveat that it has to be installed on a user's PC--along with a host of security concerns when users are able to download data. For those using the Integrated File System (IFS), the alternative has been to use the Copy to Import File (CPYTOIMPF) command to convert a database file to a comma- or tab-delimited stream file (i.e., a PC file) on the IFS. This method has two strengths: It's AS/400-based without requiring any special tools on the user's desktop, and it's secure. However, its main weakness is that the command supplies only raw data, so either the programmer must add headings or the user must research what each column is.

In case you're not familiar with it, here's how CPYTOIMPF works. Suppose you have table ORDERS (exported from Microsoft's Northwind.MDB sample database), as shown below:

You want to export this table to a text file that spreadsheet users can open. Joe, a PC user, has his home drive H: mapped to folder homeusersjoe on the IFS. If you want a text file version of the ORDERS table to appear on Joe's H: drive, simply type this:

In this case, CPYTOIMPF will convert the ORDERS table into a text-based, comma-delimited export file called orders.csv in folder /home/users/joe, which a user can easily work with. It would be nice, however, if CPYTOIMPF had an option to read the file's field names or column headings and place them with the data.

Enter the Retrieve Column Headings (RTVCOLHDG) utility. It works in conjunction with the CPYTOIMPF command to add column headings to export files so that users can see what each column contains. The headings will contain the column headings defined by the file's DDS COLHDG keyword or by SQL's LABEL ON COLUMN statement.

The table below shows the list of command parameters for RTVCOLHDG:

Now, in the ORDERS table example, suppose the table was defined with the following column headings:

Here's an example of using RTVCOLHDG to build a stream file containing the column headings:

Use the DSPF command to view the stream file:

Here's the output RTVCOLHDG produces:

The first two lines consist of the heading constants specified on the command. The next three lines show the column headings as defined in the file. Now, the data has to be appended to this stream file using CPYTOIMPF. However, we need to specify MBROPT(*ADD) to append data to the existing stream file rather than replace it.

Formatting the Results in Excel

Figure 1 shows how the orders.csv file appears in Excel.

Figure 1: Using RTVCOLHDG produces these results in Excel. (Click images to enlarge.)


You may have noticed that the columns are nicely spaced and assumed it took a long time to size each column. Actually, there's an easy way to make Excel autofit all of the columns, and it only takes two seconds to show users:

1. Click the box to the left of the A and above the 1 to highlight the entire spreadsheet. In Figure 2, this box has a red dot in it. (Alternatively, you can press Control+A.)

2. Move your cursor to the heading row between the A and the B (circled in yellow in Figure 2). The cursor icon will turn to a crosshair when positioned correctly. Double-click, and the columns will be sized to fit the largest value in each column. (Alternatively, choose Format->Column->AutoFit Selection.)

Figure 2: Highlight the entire spreadsheet to "autofit" column width.

Production Use

Figure 3 contains a sample CL program demonstrating how I've used RTVCOLHDG in production environments. I follow these general steps:

1. The user enters applicable criteria via command prompt or RPG program and passes the information to the CL program.
2. A SELECT statement is run to get the data using either Query Management Queries or a utility such as RUNSQL. I've used the RUNSQL utility just so the SELECT would be visible in the CL program. The results are dumped into QTEMP. A program could be used to extract data as well.
3. If needed, the SQL LABEL ON statement is issued to override any column headings that contain poor descriptions (such as calculated fields in an SQL statement).
4. The RTVCOLHDG command creates the stream file and populates it with heading information.
5. The CPYTOIMPF command appends the data from the temp file to the stream file. The stream file can be FTPed, emailed, or left on the IFS for users to access, depending on your system's capabilities.

/* */
/* CUSTSALES */
/* */
/* Send customer sales history to comma delimited */
/* export file. */
/* */

PGM PARM(&CUSTOMERID)


/* User Passes Customer ID */
DCL &CUSTOMERID *CHAR 5


/* Misc Stuff */
DCL &SYSDATE *CHAR 6
DCL &SYSTIME *CHAR 6
DCL &SYSDATEF *CHAR 10
DCL &SYSTIMEF *CHAR 8
DCL &STMF *CHAR 80 VALUE('/home/msansoterr/Customer_Sales.CSV')

RTVSYSVAL QDATE &SYSDATE
RTVSYSVAL QTIME &SYSTIME

CVTDAT &SYSDATE &SYSDATEF FROMFMT(*JOB) TOFMT(*USA)
CHGVAR &SYSTIMEF ((%SST(&SYSDATEF 1 2) || ':' || +
%SST(&SYSDATEF 3 2) || ':' || +
%SST(&SYSDATEF 5 2)))


/* Delete work file, if it exists */
DLTF QTEMP/TEMPDATA
MONMSG CPF2105


/* Run SQL to retrieve customer's sales history +
This work file processing is usually done by a QM Query +
or program (COBOL/RPG). +
+
NOTE: The CREATE TABLE AS is available with V5R2 */

RUNSQL (' +
CREATE TABLE QTEMP/TEMPDATA AS ( +
SELECT Year(A.ShippedDate) * 100 + +
Month(A.ShippedDate) AS Period, +
B.ProductID, C.ProductName, +
SUM(B.Quantity) As Qty, +
Avg(B.UnitPrice) AS AvgPrice, +
Sum(Round(B.Quantity * B.UnitPrice,2)) AS ExtPrice +
FROM Orders A +
JOIN OrderDetails B On B.OrderID=A.OrderID +
JOIN Products C On C.ProductID=B.ProductID +
WHERE CustomerID=''' || &CUSTOMERID || ''' +
GROUP BY Year(A.ShippedDate) * 100 + +
Month(A.ShippedDate), +
B.ProductID, C.ProductName +
ORDER BY 1 DESC, 2, 3) +
WITH DATA')


/* Place meaningful column labels on the work table */
RUNSQL (' +
LABEL ON COLUMN QTEMP/TEMPDATA ( +
PERIOD IS ''Period'', +
PRODUCTID IS ''Product ID'', +
PRODUCTNAME IS ''Product Name'', +
QTY IS ''Qty'', +
AVGPRICE IS ''Average Price'', +
EXTPRICE IS ''Sales Amount'')')


/* Retrieve column headings into a stream file +
Include a title, customer id and date as well */
RTVCOLHDG FILE(QTEMP/TEMPDATA) +
STMF(&STMF) +
HDGCONST('Customer Sales History' +
' ' +
('Customer ID:'||&CustomerID) +
' ' +
('Run Date: ' || &SysDateF || ' ' || &SysTimeF) +
' ')


/* Append Data to Headings using CPYTOIMPF */
CPYTOIMPF FROMFILE(QTEMP/TEMPDATA) TOSTMF(&STMF) +
MBROPT(*ADD) RCDDLM(*CRLF)


/* Additional processing to e-mail, FTP, etc. the output +
file goes here.... */

ENDPGM

Figure 3: This sample CL program shows how to use RTVCOLHDG in production environments.

Caveats

Watch out for a couple of things when using this technique:

1. Strings with embedded double quotes (") cause problems when the double quote is the string delimiter. You can easily resolve this by replacing a double quote with two double quotes or a substitution character. When using temporary files, this is done most easily with an SQL UPDATE statement using the TRANSLATE function:

2. When a column containing string values has only numeric data, Excel will treat the data as numeric. This is particularly bothersome when dealing with ZIP codes because leading zeros will often be truncated. This problem can usually be solved by having the user format the cells to show leading zeros.

Export Etiquette

It's important that users be able to access iSeries application data quickly and without formatting hassles. The CPYTOIMPF command provides a way to give users delimited data in a stream file format for easy spreadsheet access. The RTVCOLHDG utility provides an appropriate complement by allowing the users to see a definition for what each column holds.