Import/Export ASCII Files

ASCII files are a common format found in both the mainframe and PC worlds. Files coming from mainframes usually come in an ASCII or Blocked format.

Suppose you have an ASCII file on a floppy disk which contains 10,000 sales transactions for 1986. You want to LOAD that file into the SALES table in your database. The file name is 86SALES.DAT.

The Record Description to describe this file might be:

RD RD1 The name of the Record Description
store# x(3) is RD1. There is no file type specified.
store_name x(12) The default file type is ASCII. The data
mo x(2) definition statements consist of the
dy x(2) field names, type, and sizes that
yr x(4) comprise the typical record in the file
cust# x(6) 86SALES.DAT. The records are fixed
cust_name x(30) length.
street x(20)
city x(20) Here is a tip: In the RD field descriptions
state x(2) for ASCII files, it is most likely that all
zip x(5) data types will be either x(n) or x(n.f)
item# x(6) such as x(10) or x(8.2). See the topic
descr x(20) Data Types and Rules for Data Transfer .
quan_bot x(4)
price x(6.2)

Lets look at the relationship between the RD in the Application Interface and the SALES table in the database:

RD RD1 TD SALES 50000 Records
store# x(3) store# x(3) key
store_name x(12) item# x(6) key
mo x(2) cust# x(6) key
dy x(2) pdate date key
yr x(4) :1 yr x(4) key
cust# x(6) :1 mo x(2) key
cust_name x(30) :1 dy x(2)
street x(20) descr x(20) key
city x(20) quan_bot n(4)
state x(2) price n(5.2)
zip x(5)
item# x(6)
descr x(20)
quan_bot x(4)
price x(6.2)

Now you are ready to load the 10,000 external records from the diskette in drive A. The LOAD command is

LOAD SALES RD1 a:\86SALES.DAT
(tablename) (rdname) (filename)

The Primary Rule For Data Transfer is
Where the names match, data moves. Differences in field size, type, and the order in which the fields appear are handled automatically when the data transfers. See Data Types and Rules for Data Transfer .

Consider what was left behind in our sample file transfer.

For any field in the RD where there is no corresponding field name in the database table, no data transfered. You can see several fields in the RD where data was left behind. The data base field named PDATE gets its value because data transferred into each of the elementary fields that it is composed of.

Export

An RD with the ASCII file-type can be used to LOAD or UNLOAD data. To UNLOAD the content of the SALES table to a diskette on drive A, issue the following commands:

FILL A SALES
UNLOAD A SALES URD1 A:\SALES86.DAT

A file named SALES86.DAT is created on drive A. It is a standard ASCII file with fixed length records. These are also called System Data Format (SDF) files. URD1 might look like this:

RD URD1
store# x(3)
item# x(6)
cust# x(6)
mo x(2)
dy x(2)
yr x(4)
descr x(20)
quan_bot x(6)
price x(8.2)

All of the data in the SALES table "unloads," as there is a corresponding name in the RD for all of the fields in the SALES table. There is more detailed information available about data transfer. See Rules for Data Transfer .