Data Transfer: PC File Types and Considerations

Troubleshooting

Problem

The document contains information on the supported data transfer PC file types and information on each file type used by IBM i Access Client Solutions and the older Client Access, IBM iSeries Access, and IBM i Access for Windows products.

Resolving The Problem

Note: IBM i Access Client Solutions does not support all of the file types described in this document.
IBM i Access Client Solutions supports the following file types:

Comma-separated values (.csv)
Tab-delimited text (.txt)
Text (.txt)
Microsoft Excel 2007-2010 (.xlsx)
Microsoft Excel 97-2003 (.xls)
OpenOffice (.ods)

DOS Random

PC file types and standards

The Client Access, IBM iSeries Access, and Access Client Solutions data transfer functions support several file types. Each type has unique properties. The user requirements and the capabilities of your applications determine which file type to select.

Many of the PC file types discussed are standardized across platforms and operating systems. Data transfer functions attempt to use the most common features of a file type when an absolute standard is not available. Every effort is made to ensure cross-compatibility between releases and versions of clients.

IBM i ships with a sample database file in library QIWS named QCUSTCDT. This file includes address and credit information for twelve fictional individuals. This table includes both character and zoned numeric data and is intended for use as predefined test data for applications.

To complement the following information, it is advisable to download QCUSTCDT to the PC in all of the file types available in the release of client being used. Look at each file with a text editor (such as Notepad or DOS Edit). Note the differences on how data is stored and converted in each of the PC file types. To see the special characters referred to (such as carriage returns, line feeds, and end-of-file marks), a binary (hex) file editor or viewer can be used. Many excellent binary editors are available, both commercially and as Shareware. DOS also ships with a binary file editor or debugger named DEBUG. DEBUG can be used as a primitive but powerful editor. As with any system utility, refer to the documentation and become familiar with PC file layout and hexadecimal notation before you modify files with a binary editor. Ensure a backup of important data is made before you experiment.

PC file types details

ASCII Text - also referred to as PC Code for DBCS. These files are standard text-only files. ASCII text files have the following features:

A carriage return (X’0D) and line feed (X’0A’) mark the end of each record.
An end-of-file (X’1A’) marks the end of the last record in the file or the end of the file. Data transfer now offers an option to remove the end-of-file indicator by changing the settings under the Details button when ASCII file format is specified.
ASCII files are variable-length. Trailing spaces or blanks are dropped, in most cases (selectable feature starting with Client Access Express).
When you transfer numeric or decimal data, one position is required for the sign.
When you transfer numeric decimal data, one position is required for the decimal when the number has a nonzero scale.
ASCII special characters can be converted to blanks when no EBCDIC equivalent exists.
Packed decimal data requires the following:
-- Extra position for the sign. Either a blank or a minus (-) in the first position of the field.
-- Extra blank position if data+sign is an odd number of positions. Data+sign positions must always be even.
-- Position for the decimal point.
-- Positions to the right of the decimal must be fully populated with digits.

Note for ASCII text numeric values: On IBM i, packed and zoned numeric values indicate positive and negative values by flipping the least significant bit of the numeric value. The values used are as follows:

Preferred positive sign: F
Other positive signs: A,C,E
Preferred negative sign: D
Other negative signs: B

BASIC random - is a general-purpose text file. BASIC random files have the following features:

Records are fixed-length, including leading zeros and trailing blanks as required.
No delimiters are included between fields or records.
No end-of-file marks are used.
Hexadecimal data is unchanged during the transfer.

BASIC sequential - is used for sequential processing by BASIC programs. Due to the use of the comma delimiter, this file type is often useful for other purposes and applications. BASIC sequential files have the following features:

A carriage return (X’0D’) and line feed (X’0A’) mark the end of each record.
An end-of-file mark (X’1A’) must be present after the last record in the file.
Records and fields are variable-length.
Numeric and character fields are written as displayable characters.
Character data fields are enclosed in ASCII double quotation marks (X’22’).
Character data fields cannot contain ASCII double quotation marks as data (X’22’).
Fields are delimited by ASCII commas (X’2C’).
Numeric data fields cannot contain ASCII commas (X’22’) as separators.

BIFF 3 - is the BIFF (XLS) file format as defined for the Microsoft Excel version 3 spreadsheet package.

BIFF 4 - is the BIFF (XLS) file format as defined for the Microsoft Excel version 4 spreadsheet package.

BIFF 5 - is the BIFF (XLS) file format as defined for the Microsoft Excel version 5 spreadsheet package.

BIFF 7 - is the BIFF (XLS) file format as defined for the Microsoft Excel 95 spreadsheet package. This support was added in V5R1M0 Express.

BIFF8 - is the BIFF (XLS) file format as defined for the Microsoft Excel 97 spreadsheet package. This support was added in V5R1M0 Express.

Notes for all BIFF formats:

Microsoft Excel version 5.0 and earlier limited spreadsheet size to 16384 rows. Client Access for Windows 3.1 RUMBA/400 file transfer processes files with more than 16384 rows, allowing the creation of BIFF files that are too large for Excel to load. Because of this, Client Access for Windows 95/NT V3R1Mx, V3R2M0, V4R4M0 Express, V4R5M0 Express, V5R1M0 Express, and iSeries Access Data Transfer limit the amount of data you can download to 16384 rows when you use BIFF 3, BIFF 4, and BIFF 5 (see the following note). Microsoft Excel version 7.0 lifted this restriction and allows for 65535 rows.

Note: New in V5R1M0, BIFF 5 has a selectable option to download to multiple worksheets, each containing up to 16384 records. By default, this option is off. BIFF 7 is an extension of BIFF5 and also allows downloading to multiple worksheets, each containing up to 16384 records. By default, this option is on when you use BIFF 7. BIFF 8 file type is stored in Unicode. BIFF 8 supports downloading to multiple worksheets, each containing up to 65535 records.
The BIFF file version can be determined by looking at the first 2 bytes of the BOF record. For BIFF 5 and BIFF 7, the first 2 bytes of the BOF record are 0908h. For BIFF 4, the first 2 bytes are 0904h. For BIFF 3, the first 2 bytes are 0902h. For BIFF 3 and 4, these values are easy to find because they are typically the first 2 bytes in the file. With BIFF 5 and 7 workbooks, the BOF record is typically encapsulated within the book stream framework.
For a full description of the layout of Microsoft Excel BIFF format files, refer to the Microsoft Office Software Developer's Kit for the applicable version of Microsoft Office and Microsoft Excel. This information includes documentation on identifying the BOF record.
All BIFF file formats (including BIFF8) do not support downloading from IBM i of field lengths larger than 255 characters. If the field is larger, error message CWBFT0011 is issued resulting in PC data truncation

Comma-separated values (CSV) - is similar to the BASIC sequential file type. Features of the CSV file type include:

A carriage return (X’0D’) and line feed (X’0A’) mark the end of each record.
No end-of-file mark (X’1A’) is expected after the last record in the file.
If an end-of-file mark (X’1A’) is present, it might be interpreted as an extra record.
Records and fields are variable-length.
Numeric and character fields are written as displayable characters.
Character data fields are enclosed in ASCII double quotation marks (X’22’).
Note: Some applications such as Microsoft Excel do not include the double quotation marks to differentiate text and numeric data. With a proper FDF and correct data layout, IBM i Access data transfer succeeds.
Character data fields cannot contain ASCII double quotation marks as data (X’22’).
Fields are delimited by ASCII commas (X’2C’).
Note: PC regional settings do not affect this delimiter.
Numeric data fields cannot contain ASCII commas (X’22’) as separators.

Data interchange format (DIF) - developed to handle table (spreadsheet-like) data. The data section has the following basic layout:

Each data value consists of the following lines:
- Type indicator, Number value
- String value
Possible values for type indicator are:
-1 - the data is a special value
0 - the data is numeric
1 - the data is a string
If the data is numeric, the number value contains the numeric data, and the string value is blank.
If the data is a string, the number value contains a zero (0), and the string value contains the text data.
The following indicates a null number, which is also a string, which is not valid. Applications such as Quattro Pro can put data values in this format:
0,0
''

Note: Although DIF is an IBM Lotus specification, it appears to be dropped in newer versions of IBM® Lotus® 1-2-3®. BIFF or CSV can be used as an effective alternative to DIF for spreadsheet-like data.

DOS random - are fixed-length files optimized for DOS sequential stream-file type read and write operations. DOS random files have the following features:

No carriage return (X’0D’) or line feed (X’0A) characters are used.
No end-of-file mark (X’1A’) is expected.
Records are delimited by their constant length, relative positions in the file, and the length of the file.
Binary data is stored as a twos complement equivalent.
Character, date, time, and timestamp data are converted character by character.
Trailing blanks are preserved or added to fill character fields.
Leading zeros are preserved or added to fill numeric fields.
Hexadecimal fields are not converted.
Packed decimal fields are unchanged, except for the last half byte, which indicates the sign. On the PC, X’3’ indicates a positive value, and X’B’ indicates a negative value. On the OS/400 or i5/OS, X’F’ is used to indicate a positive value and X’D’ is used to indicate a negative value.
Zoned decimal fields are converted and the last half byte is changed to indicate the sign. On the PC, X’3’ is used to indicate a positive value and X’B’ is used to indicate a negative value. On the OS/400 or i5/OS, X’F’ is used to indicate a positive value and X’D’ is used to indicate a negative value.

DOS random type 2 - DOS random type 2 files are identical to DOS random type files except for how negative zoned and packed decimal values are handled. DOS random type 2 files follow the IBM Systems Application Architecture (IBM SAA) standard, by using an X’ 7’ half byte to indicate a negative value. This file type is often required for numeric data coming from UNIX- or VAX-based applications.

No conversion - This type transfers the data with no ASCII-to-EBCDIC conversion performed. Consider the following:

Variable-length OS/400 or i5/OS fields are converted to fixed-length fields during an AS/400-to-PC transfer. Trailing blanks are added.
Date, time, and timestamp data are converted to EBCDIC character data during an AS/400-to-PC transfer.
Variable-length null fields are converted to fixed lengths and trailing EBCDIC blanks (or EBCDIC zeros) are added during an AS/400-to-PC transfer.
Trailing blanks are removed during PC-to-AS/400 transfers.

The following formats are new to Client Access Express:

WK4(Lotus) - Available starting with Client Access Express V4R4M0, data transfer now provides the capability to transfer files with the Lotus 123.WK4 file format. This support is classified under the optional installation due to the size of the files required for supporting the function.

Create extra sheets when first sheet overflows. Lotus 1-2-3 (.WK4) files support only 8192 rows per sheet of a workbook. Select this option to create secondary sheets when the first sheet overflows after 8192 rows. If you do not select this option, then the sheet is truncated at 8192 rows.
Include column headings. Select this option to place column heading text on the main sheet.
Include column headings on extra sheets. Select this option to place column heading text on secondary sheets. This option is only available if Create extra sheets when first sheet overflows and Include column headings are selected.

123(Lotus) - Support was added in Client Access Express V5R1M0 for Lotus .123 file type. The .123 file type is the standard type used in the 97 edition of Lotus 1-2-3.

This file type uses LMBCS (Lotus Multi-Byte Character Set) character conversion routines.
Unicode character set support.
Support for .123 numeric storage types.
Capability to read and write records to multiple sheets within a single workbook.

123 Version 9 (Lotus) - Support was added in iSeries Access V5R2 for the Lotus 1-2-3 Version 9 file type. Lotus 1-2-3 Version 9 can handle a maximum of 65536 rows per sheet. Data Transfer supports only NUMBER, LABEL (character), and FORMULA cells resulting in numeric data for this file type.

Tab -delimited text - The Client Access Express Client added support for the tab-delimited text file format. In a tab-delimited text file, the fields are separated by tabs, with each row ending with a line ending combination of carriage return line feed.

The option to Retain trailing spaces on character data fields was added starting with Client Access Express V5R1M0.

Unicode text - Support was added in iSeries Access V5R3 for the Unicode text file type. The Unicode file type can be modified to:

Truncate spaces from the end of records. This option eliminates the trailing spaces from rows in ASCII and Unicode text files.
Pad numeric fields with leading zeros. This option replaces the spaces by padding numeric fields with zeros.
Add ByteOrder Mark(BOM). This option includes a BOM at the beginning of the file. By examining the BOM, some applications can determine whether the file is UTF-8, Unicode Little Endian, or Unicode Big Endian.

Microsoft Excel XML spreadsheet

Support was added in iSeries Access V5R4 for the Microsoft Excel XML Spreadsheet file type. With this file type, all data is written as UTF-8. The "Translate from" and CCSID options are disabled when this file type is selected. This file type can be modified to:

Create extra sheets when the first sheet overflows
Include column headings
Include column headings on extra sheets

PC file type support by client

	Client Access Optimized for Windows 3.1 5763-XK1	Client Access Optimized for Windows 95/NT 5763XD1	Access Client Solutions 5733XJ1
ASCII Text	Yes	Yes	Yes
BASIC Random	Yes	Yes	No
BASIC Sequential	Yes	Yes	No
BIFF 3	Yes	Yes	No
BIFF 4	Yes	Yes	No
BIFF 5	Yes	Yes	No
CSV	Yes	Yes	Yes
DIF	Yes	Yes	No
DOS Random	Yes	Yes	No
DOS Random Type 2	Yes	Yes	No
No Conversion	Yes	Yes	No

Client Access Express supports all of the file types listed in the previous grid, plus the following file types:

	Client Access Express V4R4M0, V4R5M0 5769XE1	Client Access Express V5R1M0 5722XE1	iSeries Access V5R2M0 5722XE1	iSeries Access V5R3M0/V5R4M0 V6R1M0/V7R1M0	Access Client Solutions
BIFF 7	No	Yes	Yes	Yes	No
BIFF 8	No	Yes	Yes	Yes	Yes An option for .xls and for .xlsx
WK4(Lotus)	Yes	Yes	Yes	Yes	No
123(Lotus)	No	Yes	Yes	Yes	No
Tab-Delimited Text	Yes	Yes	Yes	Yes	Yes
123 Version 9 (Lotus)	No	No	Yes	Yes	No
Unicode	No	No	No	Yes	No Instead, has Translate data to: which includes Unicode types
Excel XLSX	No	No	No	No	Yes
OpenOffice (.ods)	No	No	No	No	Yes

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z000000cwMLAAY","label":"Data Access-\u003EData Transfer"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Historical Number

9733712

Was this topic helpful?

Document Information

Modified date:
12 December 2024

UID

nas8N1010034

Tips