Import/Export ASCII Delimited files

The ASCII Delimited file format is common to the PC world. The default delimiters are a comma between fields and double-quote (") around fields that contain commas. You can specify alternative delimiters by employing the optional part of the syntax as described below.

RD rdname DELIMITED [DELIMITER IS chr] [QUOTE IS chr]

For example, if you had an ASCII Delimited file that used slash (/) and single quote (') for delimiters, the RD clause in the record description would be:

RD rdname delimited delimiter is / quote is '

Suppose you have a Delimited 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 Delimited The name of the Record Description
store# is RD1. The file type is Delimited. The
store_name data definition statements consist of the
mo field names that comprise the typical
dy record in the external file named
yr 86SALES.DAT.
cust#
cust_name The Delimited file format already has
street the information about field types and
city sizes, so that information is not
state required in the data definition
zip statement.
item#
descr
quan_bot
price

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

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

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 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.

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 Delimited file-type can be used to LOAD or UNLOAD files. 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 Delimited file using commas and double-quotes as delimiters because the Record Description URD1 included the file type DELIMITED in its RD clause and did not employ any of the optional syntax. URD1 might look like this:

RD URD1 Delimited
store#
item#
cust#
mo
dy
yr
descr
quan_bot
price

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.