DOCUMENT #758
===========================================================================
Working With ASCII Files
===========================================================================
PRODUCT: R:BASE VERSION : ALL
===========================================================================
CATALOG: Programming In R:BASE AREA : Logic and Data Manipulation
Databases often need to share data with other programs. The easiest way to
transfer data between different programs is to use ASCII files. Most every
software product has the ability to read data from or export data to an
ASCII file. There are two main types of ASCII files that are used for
transferring data: delimited and fixed. R:BASE can easily create or read
either one. Make sure the file type and format you use matches the
requirements of both programs.
The R:BASE Import/export utility (FileGateway) is a menu drive program for
importing and exporting data. You can create and read both delimited and
fixed ASCII files using it. This article discusses creating and reading
ASCII files using the R:BASE programming language rather than the
Import/export utility.
ASCII Delimited File
An ASCII delimited file is a file where the different data items or fields
are separated by commas and text items are enclosed in double quotes. Each
row of data is on a single row in the file, and each row in the file ends
with a carriage return, line feed. There are no blank spaces required
between data items, just the comma to separate them. In a delimited file,
you count commas to find the different data items. The second item follows
the first comma. The last name in the example below is designated as the
third item, for example. A sample file might look like this:
133,"Peter","Coffin","206-624-5126",11/26/83
160,"Mary","Simpson","206-881-8008",06/01/84
165,"Darnell","Williams","206-249-5523",07/05/89
166,"John","Chou","206-481-1801",07/10/89
167,"Sandi","Watson","206-881-1008",07/10/89
Note that each row can be a different length depending on the data itself.
The text items have double quotes around them as well as commas between the
fields. Number and date fields do not have quotes. Some programs will put
all the items in quotes; some won't put any item in quotes. The standard
ASCII delimited file has commas between data items and text items in double
quotes. Text items are generally enclosed in quotes because they may contain
commas as part of the data. If you don't put the text in quotes, then
programs can't tell if the comma is actual data or is delimiting fields.
Making an ASCII delimited file
In R:BASE, use the UNLOAD and LOAD commands to create and read ASCII
delimited files. R:BASE creates the file using your current QUOTE and
DELIMIT settings (usually ' and ,), but you can change these values to
meet the requirements of other programs. You need to pay particular
attention to your QUOTE character. The default QUOTE character in R:BASE is
a single quote; in delimited ASCII files the default is double quotes. In
most instances, you change your QUOTE character to double quotes to export
or import a delimited ASCII file, then change it back to the normal single
quote.
The UNLOAD command creates a delimited ASCII file with data from a table or
from a view. The following sequence of commands will export the data from
the customer table in the Concomp sample database as an ASCII delimited file
with double quotes around text fields. The AS ASCII option on the UNLOAD
command formats the data as ASCII delimited.
-- change the QUOTE setting to double
SET QUOTES=NULL
SET QUOTES="
-- open a DOS file to hold the data
OUTPUT customer.del
-- put the data in the file
UNLOAD DATA FROM customer AS ASCII
-- close the file
OUTPUT SCREEN
-- change the QUOTE setting back to single
SET QUOTES=NULL
SET QUOTES='
The delimiter character used by R:BASE when creating a delimited ASCII file
is the current DELIMIT character, usually a comma (,). It is easy to build
a command file that will create an ASCII delimited file with a different
delimiter between data fields. For example, to make an ASCII delimited file
with fields separated by a "#" and text enclosed in single quotes use the
following code:
-- set variables to easily change the DELIMIT character
SET VAR vpound = (CHAR(35))
SET VAR vcomma = (CHAR(44))
-- change the DELIMIT character to #
SET DELIMIT = NULL
SET DELIMIT = .vpound
-- open a DOS file to hold the data
OUTPUT empdata.del
-- put the data in the file
UNLOAD DATA FOR employee AS ASCII
-- close the file
OUTPUT SCREEN
-- change the DELIMIT setting back to a comma
SET DELIMIT = NULL
SET DELIMIT = .vcomma
The file that is generated is shown in Figure 1 on page _:
If you don't know what the current settings for QUOTE and DELIMIT are, use
the CVAL function to query the database for the current setting. These
settings are stored in the database and are reset each time a database is
connected. This code creates a delimited ASCII file with the specified
QUOTE and DELIMIT settings regardless of the current settings for those
special characters.
-- set variables to change and reset the character
SET VAR vpound = (CHAR(35)) +
vdouble_quote = (CHAR(34)) +
vdelimit=DELIMIT +
vquote = QUOTES
-- R:BASE lets you set a variable to a single word TEXT value without
-- using quotes around the TEXT value so you can retrieve settings
-- with the CVAL function.
SET VAR vdefault_delimit = (CVAL(.vdelimit))
SET VAR vdefault_quote = (CVAL(.vquote))
-- change the characters
SET DELIMIT = NULL
SET DELIMIT = .vpound
SET QUOTE = NULL
SET QUOTE = .vdouble_quote
-- open a DOS file to hold the data
OUTPUT empdata.del
-- put the data in the file
UNLOAD DATA FOR employee AS ASCII
-- close the file
OUTPUT SCREEN
-- change the settings back to their original values
SET DELIMIT = NULL
SET DELIMIT = .vdefault_delimit
SET QUOTE = NULL
SET QUOTE = .vdefault_quote
Loading an ASCII delimited file
ASCII delimited files can be imported into a table using the LOAD command.
For example, employee data in the file empdata.del shown in Figure 2 is
added to the employee table in the Concomp database with the LOAD command
sequence shown below:
SET QUOTES=NULL
SET QUOTES="
LOAD employee FROM empdata.del
SET QUOTES=NULL
SET QUOTES='
If the software generating the ASCII file lets you specify settings,
create the file using single quotes around the data items. Then you don't
need to change your QUOTE setting in R:BASE. But, R:BASE does make it easy
to change your QUOTE and DELIMIT setting depending on the requirements of
the other software. They can be changed for exporting the data to an ASCII
file, or for loading the data from an ASCII file.
You don't need to use the optional AS ASCII clause on the LOAD command to
load a delimited ASCII file. The AS ASCII clause provides for faster
loading, but turns off some of the built-in error checking of the LOAD
command. When using the AS ASCII option, for example, invalid data types do
not return an error, rather the field is loaded as NULL with no notification
to the user. Only use the AS ASCII option when you are sure you have no
errors in the data to be loaded.
ASCII Fixed Field File
An ASCII fixed field file doesn't have commas between the data items. Each
item starts in a specific column. There may not even be a blank between
different data items. As with a delimited file, each row of data in the
file corresponds to a row of data in the database. In a fixed ASCII file,
the phone number field, for example, always starts in column 31. Each row
in a fixed field ASCII file is the exact same length. A fixed field ASCII
file looks like this:
133 Peter Coffin 206-624-512611/26/83
160 Mary Simpson 206-881-800806/01/84
165 Darnell Williams 206-249-552307/05/89
166 John Chou 206-481-180107/10/89
167 Sandi Watson 206-881-100807/10/89
A fixed field file is always columnar. Programs know to find the data by
looking in specific column locations.
Making an ASCII fixed field file
R:BASE creates and reads fixed field files using the SELECT and the LOAD
commands. If the file will have fewer than 255 characters per row, you can
use a report to create the file. Fixed field ASCII files often require the
data to be formatted in certain ways. For example, integer numbers may be
required to be 10 digits with zeros filling the unused spaces. Either
picture formats in a report or the FORMAT function can be used for this.
Using a report
To create a fixed field ASCII file from a report, locate the columns or
variables on a single detail line in the desired column locations. You can't
have any wrapping fields. There are no header or footer lines of any kind,
just a single detail line. Only fields are located, there is no literal text
on the report. Format the fields as desired. Set the lines per page to 0 in
Page Settings under Layout. Answer Yes to the question Remove initial
carriage return. Then, print the report to a file.
Using a report to create a fixed field ASCII file works well when your
data row will be less than 255 characters wide. When you have more data,
use the SELECT command to generate the data to put in the file.
Using the SELECT command
The SELECT command has a number of options for formatting the output. An
option that is often used when creating a fixed field file is to specify the
display width for each field. All data types have a default display width
that displays all the data in the field except for NOTE fields. NOTE fields
automatically wrap at 15 characters unless a display width is specified. The
display width for DATE and TIME fields automatically adjusts to the defined
format.
In addition to the display width for each field, you need to adjust the
display width for the row. The default WIDTH setting is 79; it is set for
screen display. The maximum value for the WIDTH setting is 5000. Set the
width large enough to accommodate a full row of data.
With SELECT, R:BASE automatically puts a space between each field, and one
at the beginning of the line. For example, to create a fixed field ASCII
file about transaction information, use the following commands:
-- turn off column name display
SET HEADINGS OFF
-- set the row width
SET WIDTH 100
-- create the file
OUTPUT trans.fix
SELECT LJS(CTXT(transid),6)=6, SUM(extprice)=12 +
FROM transdetail GROUP BY transid ORDER BY transid
OUTPUT SCREEN
SET HEADINGS ON
The output looks like this:
4760 $27,000.00
4780 $9,500.00
4790 $76,800.00
4795 $176,000.00
4800 $99,750.00
The default space between fields can be adjusted for by using the field
width specification. Many programs, however, cannot correctly read an ASCII
fixed field file with a leading space at the beginning of a line. The
leading space could not be removed until R:BASE 4.5 Plus!. R:BASE 4.5 Plus!
has a variable, MICRORIM_SELMARGIN, that specifies the beginning column in
which data is written with the SELECT command. This lets you create fixed
field ASCII files with the data beginning in column 1 of the file.
-- set the initial column for data
SET VAR microrim_selmargin=1
-- turn off column name display
SET HEADINGS OFF
-- set the row width
SET WIDTH 100
-- create the file
OUTPUT trans.fix
SELECT LJS(CTXT(transid),6)=6, SUM(extprice)=12 +
FROM transdetail GROUP BY transid ORDER BY transid
OUTPUT SCREEN
SET HEADINGS ON
The output now looks like this, no leading space on each row.
4760 $27,000.00
4780 $9,500.00
4790 $76,800.00
4795 $176,000.00
4800 $99,750.00
Here's an example of a SELECT command that zero fills the customer number
and exports the first 60 characters of the NOTE field data. The data comes
from the Contact table in the Concomp sample database. The custid column is
left justified to remove the leading space reserved for the plus/minus sign.
SET VAR microrim_selmargin=1
SET HEADINGS OFF
SET WIDTH 500
OUTPUT contact.fix
SELECT (LJS(FORMAT(custid,'00000000'),8))=8, contfname, +
contlname=12, contphone=12, (SGET(continfo,60,1))=60 +
FROM contact
OUTPUT SCREEN
SET WIDTH 79
SET HEADINGS ON
Figure 3 shows the the data in the file contact.fix that is created with the
select command..
Loading an ASCII fixed field file
One of the new features of R:BASE 4.5 Plus! is an option on the LOAD command
to load fixed field ASCII files. This simplifies programming significantly
because the command is included in an application and data imported without
having to use FileGateway, a data entry form or prompts!
The new LOAD command option is AS FORMATTED. When using this option, you
follow the keywords AS FORMATTED with column names to load and their
corresponding start and end locations in the file.
For example, the employee data in the file empdata.fix shown in Figure 4 is
added to the employee table in the Concomp database with the LOAD command
shown below:
LOAD employee FROM empdata.fix AS FORMATTED USING +
empid 1 3, emptitle 4 12, empfname 13 18, emplname 19 26, +
empaddress 27 48 empcity 49 58, empstate 59 60, empzip 61 65, +
empphone 66 77, empext 78 80, hiredate 81 88, entrydate 89 96
Note that columns which are not specified in the column list are loaded as
null. If the column is defined as an autonumber column, new values will
automatically be assigned as each row is added to the table; don't include
it in the column list. If an autonumber column is included in the column
list, R:BASE will expect to find data for it in the fixed field file and
will load the autonumber column with the data in the file.