Fast Unload: How to unload comma-delimited data to a spreadsheet (MS Excel, MS Access, etc...) utilizing CA Fast Unload?

Products

Fast Unload for DB2 for z/OS Fast Load for DB2 for z/OS

Issue/Introduction

How do I unload comma-delimited data from a DB2 table, transfer to my PC and load it into a spreadsheet package like Excel with Fast Unload for DB2 for z/OS (PFU)?

The ability to transfer table data from a DB2 table to the non mainframe environment allows that data to be utilised by applications such as spreadsheets, databases and executive information systems. The non mainframe environment can "value add" this data by further presenting it or further processing it.

Environment

Release: R20
Component: PFU

Resolution

Fast Unload has a data OUTPUT FORMAT called COMMA-DELIMITED which is able to produce data records able to be imported into programs such as spreadsheet programs. Once the table is unloaded from DB2 to a mainframe sequential dataset it can be transferred to the non mainframe environment with an appropriate file transfer program as a text file.

Most mainframe emulator programs have such file transfer functions. Spreadsheet programs accept "comma delimited" as a valid format for input data which is then read into the correct columns in the spreadsheet as a proper table. The file type on the PC is .CSV Microsoft Excel defines this file type as "Microsoft Office Excel Comma Separated Values File".

A comma delimited record simply means that the record is written with a delimiter or "separator" between data columns. A user can choose various characters to be a delimiter. The actual field may also be written with field delimiters around the data itself. Field delimiters are used in case there are characters in the data that could be misinterpreted as column delimiters but are needed as text in a field regardless, such as a comma after someone's name. Another strategy is to use a column delimiter character that could not be misinterpreted as a column delimiter because it does not exist as data in any of the rows.

Eg:
'111111','JOE','BLOGG;S JR',1111.22

In the above example there are four columns each separated by a "," comma as a column delimiter and the data itself has quotes as field delimiters. The 2nd last column has a ";" semicolon inside the field delimiters and will be loaded with the rest of the field as data. The last column is a decimal.

Data Considerations

The character to be used as a column delimiter with the data?
The character (if any) to be used as a field delimiter with the data?
The character to be used as a decimal point if other than "."?
How should a NULL column be represented in the data record?
Non-traditional data types like LOB's, BLOB's and CLOB's can't be unloaded.

Illustration

Consider this code:

The OUTPUT-FORMAT is COMMA-DELIMITED.

The COMMA keyword specifies that a "," will be the delimiter for columns. The normal default is a "," comma.
The input data is treated as EXTERNAL data so columns with field delimiters are treated as character data and those without are treated as numeric data.
NULL-FIELD EMPTY signifies that any NULL columns will have no character positions between column delimiters. The QUOTE parm could be used instead of EMPTY. QUOTE would produce two field delimiters for a NULL value.

Eg: This represents two null columns

With NULL-FIELD EMPTY,,,
With NULL-FIELD QUOTES,'','',
 
FASTUNLOAD
DISCARDS 100
DISPLAY-STATUS 10000
EXCP YES
INPUT-FORMAT TABLE
IO-BUFFERS 50
LOAD-CONTROL FASTLOAD
OUTPUT-FORMAT COMMA-DELIMITED    <---------- delimited format output data
COMMA ','                        <----------, is my column delimiter
NULL-FIELD EMPTY                 <---------- no characters for nulls
QUOTE NONE                       <---------- do not use any field delimiter
PART-INDEPENDENCE NO
SHRLEVEL REFERENCE
SORTSIZE 4M
SORTNUM 4
SQL-ACCESS NONE
VSAM-BUFFERS 96
SELECT * FROM CREATOR.TBEMP;

Expected output

The above unload cards would produce output records like this:

111111,JOE,BLOGG;S JR,1111.22,,,

Here we have seven columns. Five have data and the last two are NULLS.

Microsoft Excel considerations

When a comma delimited file is opened with Microsoft Excel the user must specify first that it is delimited, then specify the delimiter character being used and also the "text qualifier" which is the field delimiter. Excel has a FILE OPEN dialogue where this is specified when it realises that the file to be opened is a text file. Excel can handle both NULL-FIELD EMPTY or QUOTES to signify NULL column values. The intention with NULL data columns in the spreadsheet is to deliver a spreadsheet column that contains no data and that means no SPACE character or a zero, nothing at all. Character data is loaded as text and numeric data is loaded as an unformatted numeric. If decimal places are involved then the period is unloaded with the numeric data and then loaded into the spreadsheet. The user will then have to format the column to show the decimal places.

File Transfer considerations

The file transfer method used by the user should enable the file to be translated from EBCDIC to ASCII(or UNICODE). This will depend on the capability of the file transfer program. Read the file transfer instructions carefully. The aim is to have a pure text file as input to the spreadsheet including any numerics.

Decimal Point considerations

Fast Unload provides a DECIMAL-POINT keyword which may be used to signify a decimal point other than the one specified in DSNHDECP for the unload data. The literal used must not be the same as a delimiter being used. Microsoft Excel has options to signify alternate decimal point characters in order to correctly read the input data as a numeric value. In EXCEL look in OPTIONS, on the Advanced tab, under Editing options, clear the Use system separators check box and type in the separator to be used.

Additional Information

Produce a Comma-Delimited File

Fast Unload User Guide, KEYWORD descriptions for QUOTE, COMMA, NULL-FIELD, DECIMAL-POINT and OUTPUT-FORMAT. There is an additional keyword called ROW-DELIMITER available which allows a specific literal to be specified and generated at the end of each row of data. Normally this is not required by a spreadsheet but I make note of it here for completeness.