IBM Support

Client Access Data Transfer - Description of File Types

Troubleshooting


Problem

This document describes the file types available in IBM iSeries Access Data Transfer.

Resolving The Problem

Description of File Types

Client Access, iSeries Access, and IBM i Access data transfer support a number of file types. Here is a file type list and a sample of what some of the file types will look like after the file is transferred to the PC:
  • ASCII Text
  •  Basic Random
  •  Basic Sequential
  • BIFF3 (Microsoft® Excel 3)
  • BIFF4 (Microsoft® Excel 4)
  • BIFF5 (Microsoft® Excel 5)
  • Comma Separated Variable (CSV)
  • DIF (IBM® Lotus)
  • DOS Random
  • DOS Random Type 2
  • No Conversion

New file types introduced in Client Access Express V4R4M0:
  • Tab Delimited Text
  • WK4 (Lotus)

New file types introduced in Client Access Express V5R1M0:
  • BIFF7
  • BIFF8
  • IBM® 1-2-3 Lotus

New file type introduced in iSeries Access V5R2M0:
  • 1-2-3 Lotus Version 9

New file type introduced in iSeries Access V5R3M0:
  • Unicode Text

New file type introduced in iSeries Access V5R4M0:
  • Microsoft® Excel XML Spreadsheet


ASCII Text

Data transfer download in ASCII Text file format is the most basic transfer available. Transferring a file as ASCII Text creates the most basic file there is on a PC. Here is a sample of a file that was downloaded as ASCII Text.

This image shows what ASCII text looks like.

Basic Random

Basic Random transfers are a general purpose BASIC file type. They contain fixed length records with no delimiters between fields, no delimiters between records, and no end-of-file markers.  Hexadecimal fields do not get converted when transferring a file as Basic Random.

This image shows what Basic Random data will look like.

Basic Sequential

Basic Sequential file format looks like the following. The downloaded file looks similar to CSV. The main difference between Basic Sequential and CSV is the handling of numeric values. In Basic Sequential, decimal positions are truncated whereas in the CSV transfers, they are maintained.

This image shows what Basic Sequential looks like.


BIFF3 (Microsoft® Excel 3), BIFF4 (Microsoft® Excel 4), BIFF5 (Microsoft® Excel 5)

BIFF3, BIFF4, and BIFF5 all transfer data down and create files on the PC in a native Microsoft® Excel file format. BIFF3 creates a file in an Excel 3.0 format, BIFF4 creates an Excel 4.0 file, and BIFF5 creates a file in an Excel 5.0 format. It is important to note the version of the Excel file. While Excel 7.0 or Excel 97 will read any of these formats, Excel 4.0 will not read an Excel 5.0 worksheet. Excel 3.0 reads only BIFF3 files.

Comma Separated Variable (CSV)

Comma Separated Variable files (also referred to as Comma Delimited files) are another common format for transferring data. In a CSV file, the string values are surrounded by quotation marks and numeric values are not. A comma separates every field. There is much similarity between CSV and Basic Sequential. The main difference is how numeric values are handled.

This image shows Comma Seperated Variable data.

DIF (Lotus)

DIF stands for Data Interchange Format. It was originally instated by Lotus long ago. It is still a valid file format that can be used by several different packages. The data representation of a DIF File is not meaningful when displayed.

DOS Random/DOS Random Type 2

Using DOS Random file type, data transfer downloads the data exactly as it is on the IBM® Power™ Systems. It has no concept of a carriage return/line feed (CR/LF). It is similar to No Conversion except that it does translate the EBCDIC values to ASCII. DOS Random was the only transfer method that would fill in leading zeros on numeric values until V5R1 Client Access Express added a switch on ASCII file format to pad leading zeros.

This image shows DOS Random Type 2 data.

No Conversion

Using No Conversion downloads a file down to the PC in EBCDIC. No Conversion will not convert the EBCDIC code to ASCII and results in unreadable data. However, the unconverted file can be transferred back to the Power Systems and displayed as readable data on the Power System. A file downloaded with No Conversion looks like this:

This image shows No Conversion data.



Tab Delimited Text

Client Access Express includes support for the Tab Delimited Text file format. In a Tab Delimited Text file, the fields are separated by tabs. Each row ends with a line ending combination of carriage return line feed.

This image shows Tab Delimited Text data.


WK4 (Lotus)

Client Access Express data transfer includes the capability to transfer files with the Lotus 1-2-3 .WK4 file format. The Lotus 1-2-3 .WK4 file format can handle a maximum of 8192 rows per sheet. The data is not readable when viewed from a DOS editor.

BIFF7 (Excel 97) and BIFF8 (Excel 2000)

Support was added in Client Access Express V5R1 for Microsoft® Excel BIFF7 and BIFF8 file formats.

BIFF7 is an extension of the BIFF5 file format supported on earlier versions of data transfer. BIFF5 and BIFF7 support have both been expanded to allow multiple sheets to be written to an Excel workbook. To take advantage of this new functionality, select BIFF5 or BIFF7 for the PC file type, then select Details and then Create extra sheets when first sheet overflows. Only 16,384 records are allowed per sheet and overflow sheets are created as necessary. This option is turned OFF by default for the BIFF5 file type. The option is turned ON by default for the BIFF7 file type.

The major difference between BIFF7 and BIFF8 is that BIFF8 character data is stored in UNICODE. Like BIFF5 and BIFF7, BIFF8 support allows multiple sheets to be saved within a workbook. 65,535 records are allowed per sheet and overflow sheets are created as necessary.

1-2-3 Lotus (Lotus 1-2-3 97 Edition)

Support was added in Client Access Express V5R1 for the Lotus 1-2-3 file type, which is the standard type used in the 97 edition of Lotus 1-2-3. Lotus WK4 and Lotus 1-2-3 file types use the same LMBCS (Lotus Multi-Byte Character Set) character conversion routines so the existing WK4 selectively installed option was expanded to include 1-2-3 support. The Lotus 1-2-3 file type also includes support for the UNICODE character set and new 1-2-3 numeric storage types. Like WK4 file support, Lotus 1-2-3 file support includes the capability to read and write records to multiple sheets within a single workbook.

1-2-3 Lotus Version 9

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.

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

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0"}]

Historical Number

13020750

Document Information

Modified date:
09 January 2020

UID

nas8N1018297