PARADOX IMPORT/EXPORT NOTES

ANSA Technical Support Group, 1987






















Paradox is a trademark of Ansa Software.
dBASE II and dBASE III are registered trademarks of Ashton-Tate,Inc.
1-2-3 and Symphony are trademarks of Lotus Development Corporation.
IBM is a registered trademark of International Business Machines Corporation
PFS:FILE is a registered trademark of Software Publishing Corporation.
VisiCalc is a registered trademark of Lotus Development Corporation.
Wordstar, Wordstar 2000, and MailMerge are registered trademarks of MicroPro
International Corporation.
RBASE is a trademark of Microrim Corporation.
WordPerfect is a trademark ofthe WordPerfect Corporation

Author: J. Frykland, W. Dorrity
Editor: W. Dorrity
DATA IMPORT and EXPORT IN PARADOX


Introduction

Perhaps the most difficult task facing computer users is that of converting
data from one format to another. Convenience or necessity may require that you
bring information into Paradox from an outside source; an existing low level file
management package or spreadsheet may contain information that that you wish to
maipulate with Paradox . Likewise, the nature of your work may make it necessary
to "hand off" information contained in Paradox tables to other applications such
as a graphics package or spreadsheet. In most situations, import and export of
data to and from Paradox can be accomplished with a minimum of fuss and
complication.

Information or data can exist in a wide variety of formats, however. Even
though there are a number of well-defined conventions for data file formats,
there are enough exceptions to the few existing rules to make life interesting
for you. Generally speaking, you will want to interchange information with two
main sources: mini/mainframe "data sets" and microcomputer applications packages.
What follows is a general discussion of the data formats that are most common to
each. Many of the problems associated with data format conversion yield to
relatively simple solutions once the nature of the format is understood.

In this section, we will examine some of the techniques and issues
surrounding data conversion. Specifically, we will deal with the following
items:

ASCII files - delimited and fixed length

Lotus 1-2-3 and Symphony files

dBASE II and III+ files

PFS:File records

DIF files

WordPerfect Secondary Merge files
ASCII files

ASCII is an acronym for American Standard Code for Information Interchange.
Another standard, ASCIII (American Standard Code for Irrelevant Information
Interchange) has been proposed but not yet adopted. ASCII files are also called
DOS, or text files. An unwritten but common convention is that of using the .TXT
file extention to indicate that the file is in ASCII, or DOS text format. Some
programs will automatically supply the .TXT extention to an exported file; most
allow you to use your own. Consistency in file naming conventions will always
benefit you.

ASCII files are perhaps the most basic file storage format, the "lowest
common denominator" among computer systems. There are two major types of ASCII
file formats: delimited and fixed length.

o Delimited

The term "delimited" refers to a file of records whose elements or fields
are separated by a particular character, such as a comma. In most cases, you can
expect the records to share the following characteristics:

- Each record is on a separate line of its own

- The end of each record is marked by a particular character or
combination of characters. The most common end-of-record
delimiter is a carriage return/line feed combination (decimal
013/010 or Ctrl-M Ctrl-J).

- The records will vary in length according to the amount of
information contained in each one of them.

In Example 1 shown below, the commas are clearly visible, but the carriage
return/line feed combinations are not. This would also be the case of you were
to type the file to the screen from DOS (with the TYPE command). The reason for
this is that some types of ASCII characters cannot be represented on the screen -
they provide control information instead.

Example 1


Edward,Jones,123 Main Street,Buffalo,New York,10011
Bill,"Smith, Esq.",3388 Pine Street,San Francisco,California,94101



Note that each field is separated by a comma. The second record is somewhat
different in that Smith, Esq. is enclosed in quotes. In most cases, it is the
rule that:

Fields containing commas are enclosed in quotes, followed by the
delimiter (in this case, a comma).

Trouble Shooting Tips for Delimited Files

If delimited records do not correctly import to Paradox, there are a number
of things to investigate:

- Be sure that the fields of each record are consistently separated by
the same type of separator. A missing separator will always cause
problems.

- Each record should have the same number of fields. Empty fields must
still be separated by the default separator. In Example 2, note that
in the first record, the salutation Mr. is separated from the first
name by a comma. The second record has no salutation, but its place
must still be represented, or "held" by a comma.

If the second record were missing that initial comma, there would
appear to be one less field than there actually is. Otherwise, Paradox
will interperet that leading comma as a blank field, and all will be
well.

Example 2


Mr.,Edward,Jones,123 Main Street,Buffalo,New York,10011
,Bill,"Smith, Esq.",3388 Pine Street,Fresno,California,94101




Appended ASCII Delimited Files

When importing ASCII delimited files, you are given an option to append the
file to an existing Paradox table. Should a record and/or field not match the
Paradox record/field definitions, it will be added to a PROBLEMS table which the
user may view after the import. Some of the conditions that will cause an ASCII
record to end up in the PROBLEMS table are:

- Number of fields not the same as in the defined Paradox table.

- Non-numeric data in a numeric field.

- Date field contains an invalid format; (e.g., month > 12, etc.)

- Text data wider than the field definition.

New records imported from an ASCII file are added at the end of sequential
files. When appending is not desired, Tools/More/Empty will empty the table.
If you want to keep the old data in a separate file, shortcut the job of creating
a new table by copying the structure of the existing table. For indexed files,
records are added to the Paradox table in key field sequence. ASCII records
containing key field values for existing Paradox records will overlay the corres-
ponding PARADOX record.
If you do not wish your duplicate keyed records to overlay existing records,
you can import to a non-keyed table. Append the non-keyed table to the keyed
table and duplicates go into the PROBLEMS table. Now you can decide what to do
about the duplicates. Remember that PROBLEMS is a temporary table and disappears
when you exit Paradox; rename it if you wish to save the table.


ASCII Delimited Table Structure

The Paradox import algorithm which sets up the table structures makes a
best guess regarding field type. If bad records are found, such as bad dates in
a date field, the field type is changed to alphanumeric and the field is imported
as characters. The same type of approach should be used with problems in an
ASCII delimited import file. If all, most, or many records are put in the
PROBLEMS table on an ASCII delimited import, first check that the alphanumeric
field sizes are large enough. Then check for bad dates or numeric fields.
Change all the field types to A, import the file, then restructure numeric,
dollar, and date fields one at a time until the bad field causes records to be
put into the PROBLEM table. If there are problems with a file, experiment with
the table structure using the RESTRUCTURE command. Remember that Tools/Copy will
allow you to back up a table if you want to save intermediate steps of your
experiments.

Also, remember that the PROBLEMS table is a temporary table. If it contains
information you wish to work on, rename it to something other than PROBLEMS.
That way when you exit and restart PARADOX the information will still be
available.

Quotes in strings: If a string has quote marks within an alpha string, the
import should work correctly. For instance, "12" knife", would import as: 12"
knife. Paradox looks for a delimiter followed by a separator, in this case a
quote mark followed by a comma, as the end of the string. If there are problems,
remember the delimitor and/or separator can be changed using the CUSTOM script.



o Fixed Length ASCII Text Files

The term "fixed length" usually applies to records whose elements or fields
are not separated by a particular character and all of the records are the same
length. In most cases, you can expect fixed length text records to share the
following characteristics:

- Each record is on a separate line of its own.

- The end of each record is marked by a particular character or combination of
characters. As is the case with ASCII delimited format records, the most
common end-of-record delimiter is a carriage return/line feed combination
(decimal 013/010).

- Addtionally, each record is the same length. Spaces between fields provide
the "padding" necessary to achieve the same length for each record.
In Example 3, spaces separate the fields of each record, and even though
they cannot be seen, they pad the end of each record so that all are the same
length. As before, the carriage return/line feed combinations (decimal 013/010)
are invisible.

Example 3


8/13/1984 INTERNATIONAL PAINT $1260.00 1984 8 13
8/21/1984 COMPUTER RESULTS $380.00 1984 8 21
9/04/1984 CENTER FOR IND. LIVING $800.00 1984 9 4
9/04/1984 FEDERAL RESERVE BANK $5,200.00 1984 9 4
9/13/1984 COMPUTER RESULTS -200.00 1984 9 13




In Example 4, zeroes (0) separate the fields of each record. This format is
less common, but may be encountered in some mini/mainframe data sets.

Example 4


8/13/19840000INTERNATIONAL PAINT0000000$1260.0000001984000800001300
8/21/19840000COMPUTER RESULTS00000000000$380.0000001984000800002100
9/04/19840000CENTER FOR IND. LIVING000000800.0000001984000900004000
9/04/19840000FEDERAL RESERVE BANK0000005,200.0000001984000900004000
9/13/84000000COMPUTER RESULTS00000000000-200.0000001984000900001300





ANSA's Flimport Program - Fixed Length Import Utility

You may have noticed that Paradox has no specific options within the
Tools\ImportExport menu selections that deal with fixed length text records.
However, Ansa does make available a program called Flimport. Flimport is a
program (FLIMPORT.EXE) that creates a Paradox table from fixed length ASCII
format records. Based on information you supply, Flimport will use a separate
specification file to create a new table or append records to an existing table
without the need for comma separation between fields.

The specification file is created and maintained using interactive
features which emulate many of the concepts found in Paradox itself. Flimport
also has a batch mode which allows you to import files in a non-interactive
environment. Further documentation of Flimport operational specifics is supplied
with Flimport diskette in the form of a DOS text file, FLIMPORT.DOC.


Fixed Length Export Techniques and Considerations

As we have noted, neither Paradox 1.1 or 2.0 have specific options within
the Tools\ImportExport menu selections that deal with either import OR export of
fixed length text records. While Flimport provides an easy solution to fixed
length file import, we are seemingly left without a convenient way to export
fixed length files. However, the problem of fixed length text file export yields
to clever use of the Paradox Report Generator. The technique is as follows:


STEP 1: From the Main Paradox menu, select Report\Design\FreeForm to
design a new report for the table whose records you wish to export
in fixed length text format. Be certain to select the FreeForm
report option instead of the Tabular option.

STEP 2: Use Control Y (Ctrl-Y) to delete all but a single blank line in
the Form band. In other words, there should be no blank lines or
text in the page or report footers. Your report spec should look
like this:

Changing report R2 for TEST table Report Ins 1/1
Form Band
....+...10....+...20....+...30....+...40....+...50....+...60....+...70....+...8*
page
form

form
page



STEP 3: From the Report main menu, use Field\Place\Regular to place the
appropriate fields on the single blank line in the report form. Do
not make use of FieldSqueeze or LineSqueeze options from the
RemoveBlanks submenu selection; use of either will result in
variable length instead of fixed length records. Your report spec
should now look something like this:

Changing report R2 for TEST table Report Ins 1/1
Form Band
....+...10....+...20....+...30....+...40....+...50....+...60....+...70....+...8*
page
form
AAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAA 9999999999 99 AAAAAAAAAAAAA
form
page


The AAAAAAAA and 99999999999 sequences represent the alpha and numeric
fields that you have placed in the form band.


STEP 4: From the Report main menu, select Settings\PageLayout\Length, and
set the page length to C for continuous. If the page length is
left at anything but continuous, as many extra carriage
return/line feeds as it takes to "fill out" the remaining page
length will be inserted at the end of the report - this will
surely cause problems for any program for which the exported data
is intended.

STEP 5: From the Report main menu, select Output\File, and supply a
suitable file name in response to the prompt for a file name. The
report will be written to disk as a DOS text file, and every
record will be of the same length with the fields separated by
spaces. The report writer will automatically terminate the file
with a ^Z (ASCII 26) end of file marker.

STEP 6: Press the Do_It! key (F2) to save the report spec and return to
the main Paradox menu.


Trouble Shooting Tips for Importing Fixed Length Files

The following information may help you avoid problems with fixed length
files. It describes which ASCII formats will be successfully imported into a
given Paradox field type. As a general rule, both leading and trailing blanks
are ignored. A source field that contains all blanks will be imported into
Paradox as an empty (null) field, as will an invalid source field. Fixed length
files with problems, i.e., regular occurrences of unwanted characters, invalid
formats per the following list may be "massaged" with a good text editor that
supports a powerful search-and-replace feature.

Field Type Acceptible Values

N (numeric) All digits with an optional decimal
point followed by 1 or more digits; leading blank spaces
and dollar signs will be ignored along with embedded
commas; for negative values, the leading digit must be
immediately preceeded by a minus sign

$ (dollar) Same as N (numeric).

S (integer) Same as N (numeric) but any decimal portion will be
ignored; maximum value is +32767 and minimum value is
-32768.

D (date) The following formats are valid:
m/dd/yy mm/dd/yy
m/dd/yyyy mm/dd/yyyy
yy/mm/dd yyyy/mm/dd

You may also use a dash in place of the slash to
separate parts of a date.

A (character) Any valid ascii character will be imported; errors will
occur when, after trimming off trailing blanks, the
remaining value is longer than the Paradox field.

If all else fails, you may elect to import the fixed length file from within
Paradox with the Tools\ImportExport\Import\Ascii\Text option. This will bring in
each record as a single field, provided the record length does not exceed 132
characters.

Your only alternative at this point is clever use of the Paradox SUBSTR()
function found in the Paradox PAL User's Guide (pp 482 for Release 2.0 and pp 208
for Release 1.1). In this case, SUBSTR() will be used to locate the individual
elements of the field containing the single long text record. Whatever the
SUBSTR() function yields at a given location within the long text field may then
be assigned to an appropriate field in the same table. You are, in effect,
parsing the single text field into discreet elements. Additionally, what SUBSTR() finds may also be assigned to one or more
variables. The values contained in the variables may then be assigned to an
appropriate field in a different table.

You may wish to use Modify\Restructure to add fields to the table containing
the long text record. The fields you add to the table will correspond to the the
number and data type of the elements into which the long text record will be
parsed with the SUBSTR() function.

As an example, consider the following table that we have imported from a
fixed length text file using the Tools\ImportExport\Import\Ascii\Text option.
Note that the Struct table (obtained with the Paradox menu Tools\Info\Structure
selection) shows a text field with a size of 132 characters.


TEST Text
5 9/13/84 COMPUTER RESULTS -200.00 1984 9 13
6 9/13/1984 INTERNATIONAL PAINT +1600.00 1984 9 13
7 10/01/1984 ACES SEMINARS 831.27 1984 10 1
8 1984/10/08 INTERNATIONAL PAINT 1480.00 1984 10 8
9 84/1/23 COMPUTER RESULTS 1740.00 1984 10 23
10 10/23/1984 INTERNATIONAL PAINT -931.77 1984 10 23
11 10/26/1984 PACIFIC BELL 4475.00 1984 10 26
12 11/13/1984 INTERNATIONAL PAINT 3800.00 1984 11 13
13 11/16/1984 ANSA CORP 1482.00 1984 11 16
14 11/28/1984 INTERNATIONAL PAINT 203.35 1984 11 28
15 12/04/1984 PAUL NOLL & ASSOCIATES 3630.00 1984 12 4
16 12/10/1984 ANSA CORP 660.00 1984 12 10
17 12/10/1984 COMPUTER RESULTS 945.00 1984 12 10
18 12/12/1984 ANSA CORP 2700.00 1984 12 12
19 12/12/1984 INTERNATIONAL PAINT 1280.00 1984 12 12
20 12/24/1984 PACIFIC BELL 4300.00 1984 12 24
21 1/11/1985 INTERNATIONAL PAINT 840.00 1985 1 11
22 1/16/1985 ANSA CORP 1120.00 1985 1 16


STRUCTField NameField Type
1 Text A132


Examine the overall pattern of the data contained in the sample Receipts
table. Here, there is a date, followed by some text, followed by a dollar
amount, followed by another date, followed by two more columns of numbers. All
In all, there are six discreet, identifiable data elements for each record. Most
importantly, the data elements fall neatly into six regularly spaced columns.
Accordingly, it is desirable to have six fields into which these elements will
go. The success of this technique relies on the fact that you have made a
careful analysis of the pattern into which the overall structure of the table
falls. In this case, it is possible to determine that all of the data appears to
"line up" in fiarly well-defined columns. With this in mind, here are the steps
that will parse the long text field into six separate fields:

STEP 1: From the main Paradox menu, select Modify\Restructure, and add six
additional fields of the appropriate width and data type to the
table containing the long text record. Because the data may
contain leading or trailing blanks, it may be wise to select the
Alphanumeric data type for the additional fields. Press the
Do_It! (F2) key. As an example, see the Struct table for the
modified TEST table below:


STRUCTField NameField Type
1 Text A132
2 Date 1 A13
3 Item A30
4 Amount A15
5 Date 2 A13
6 Qty 1 A5
7 Qty 2 NA5


Here we see that there are now seven fields: the original long text field
that Paradox created during the import operation, along with the six new fields
that we will use to hold the parsed Text field.


STEP 2: From the main Paradox menu, select Scripts\Editor\Write to begin a
new script. It is usually a good idea to press the Insert key as
soon as the empty script editor page comes up.

STEP 3: Write the following script:

EDIT "TEST"
SCAN ; Start with the first record
X = [TEXT] ; Set X equal to the contents of the TEXT field in the TEST table
; This is largely a matter of convenience - saves you from having
; to write [TEXT] in each SUBSTR() statement.

[DATE 1] = SUBSTR(X,1,10) ; Set the DATE 1 field equal to whatever occupies
[ITEM] = SUBSTR(X,14,22); ten characters of the TEXT field, starting at
[AMOUNT] = SUBSTR(X,40,9) ; position 1
[DATE 2] = SUBSTR(X,53,4)
[QTY 1] = SUBSTR(X,61,2)
[QTY 2] = SUBSTR(X,67,2)

ENDSCAN
Do_It! ; End the edit session and save the results - end of script.

STEP 4: Play the script. This will parse the long text field into
the six other fields that we created earlier.

STEP 5: Use Modify\Restructure to delete the long text field from the
data base, if desired.Additional Notes and Comments - Text File Manipulation

In each case, the SUBSTR() function uses X to represent the contents of the
TEXT field in the TEST table. The first number following the X variable
represents the starting position in the field, counting from the left - the
second number in the SUBSTR() function represents the number of characters to
select beginning at the previously specified starting position. For example, in
the case of SUBSTR(TEXT,14,22), what we are really saying is, "in the field TEXT,
move 14 characters to the left from the beginning of the field and select the
next 22 characters. In the example below, it is easy to see that grabbing 22
characters starting at the 14th position from the beginning of the field will
neatly bracket "COMPUTER RESULTS" (22+14=36 spaces).

1...+...10....+...20....+...30....+...40
TEST Text
5 9/13/84 COMPUTER RESULTS -200.00

Why did we specify the value of 22? We did so because we had taken the time
to determine that the data in the second column of the Text field in the TEST
table consistently began at the 14th character position, and never exceeded 22
characters in length. Further modification of the table can be accomplished by
the use of Modify\Restructure in order to globally alter an alphanumeric data
type to numeric, for instance. Field names and widths may be altered as well.
Other PAL string manipulation functions are available to format data in a Paradox
table:

2.0/1.1 DATEVAL() Converts a string or the contents of a field to a date.

2.0 FIELDSTR() Yields the value of the current field as a string.

2.0/1.1 FORMAT() Formats an expression or the contents of a field.

2.0/1.1 LEN() Returns the length of a string.

2.0/1.1 LOWER() Converts a string or the contents of a field to lower
case.

2.0/1.1 MATCH() Compares a string or the contents of a field to a
pattern and optionally maps the elements of the pattern
to a user specified set of variables. Very powerful.

2.0/1.1 NUMVAL() Converts a string or the contents of a field to a
number.

2.0/1.1 SEARCH() Searches for a substring in a string or the contents of
a field.

2.0/1.1 STRVAL() Converts an expression or the contents of a field to a
string.

2.0/1.1 UPPER() Converts a string or the contents of a field to upper
case.
Lotus 1-2-3 (1A and 2.01) and Symphony (1.XX) Worksheet Files

For all intents and purposes, Paradox 1.1 and 2.0 treat 1-2-3 and Symphony
files alike. Lotus 1-2-3 and Symphony files are imported directly into a new
table whose data structure is determined by Paradox. This gives you the
opportunity to look over the newly imported table to see if the results are what
you expected. You may wish to use either the Paradox Modify/Restructure feature
or write a PAL script if you want to change field names, data type, or data
format.

Paradox converts Lotus 1-2-3 and Symphony files by treating each row as a
record and each column as a field. Since spreadsheets place few restrictions on
data within a column, it is occasionally difficult to determine a consistent data
type for a Paradox field. For instance many spreadsheet users use rows of dashes
to visually separate various sections of the worksheet. While this contributes
to the aesthetic appeal of a worksheet, extraneous dashes and other characters
placed in the worksheet can complicate the business of spreadsheet conversion to
Paradox format. In the example below, a line of dashes separates a column title
from the column of data. The Date column is in Lotus date format; Start Time,
Customer Type, and Short Date columns are in text format; and Serial Number is in
numeric format.

A B C D E F
1 Date Start Time Serial Number Customer Type Short date
2 -----------------------------------------------------------
3 09-Jun-87 08:58:38 1000001 D 6/87
4 09-Jun-87 09:06:50 1000002 R 6/87
5 09-Jun-87 09:09:54 1000003 R 6/87


In the process of conversion, Paradox regards the dashes as a text item.
Because the dashes occur directly beneath the column title, Paradox assumes that
the column is a mix of text and numbers. Accordingly, Paradox tries to create a
field of a type general enough to handle both numbers and characters. As a
consequence, the fields in the Paradox table are assigned an alphanumeric data
type even though the spreadsheet column primarily contains numbers.

Preparing the Spreadsheet for Import to Paradox

To resolve potential conflicts, edit the spreadsheet to eliminate any
ambiguities. "Sanitize" the spreadsheet as follows:

1. Place column titles in Row 1; the first row that contains text is used to
generate Paradox field names.

2. Remove extraneous items used to make the spreadsheet look pretty; eliminate
rows and columns containing dashes, asterisks, exclamation points, etc. If
these are left in the worksheet, Paradox will do its best to incorporate
them into the converted table - with unexpected results.
3. Be sure that all columns contain the same data type and the same formatting
options. In other words, columns with numbers should not also contain text
(other than the column title).
In the Lotus worksheet example below, note that the line of dashes has been
removed, and that the data begin directly underneath each column title. This
spreadsheet format will almost always import to Paradox without difficulty.

A B C D E F
1 Date Start Time Serial Number Customer Type Short date
2 09-Jun-87 08:58:38 1000001 D 6/87
3 09-Jun-87 09:06:50 1000002 R 6/87
4 09-Jun-87 09:09:54 1000003 R 6/87


The structure of a Paradox table derived from the import of a sanitized
spreadsheet like the one shown in the previous example will look something like
this:

EXAMPLEDateStart timeSerial numberCustomer typeShort date
1 6/09/87 08:58:38 100001 D 6/87
2 6/09/87 09:06:50 100002 R 6/87
3 6/09/87 09:09:54 100003 R 6/87

STRUCTField NameField Type
1 Date D
2 Start time A10
3 Serial number N
4 Customer type A3
5 Short date A6



In the STRUCT table above, note the data types that Paradox has used in the
process of converting the worksheet to the EXAMPLE table; contrast that with the
Lotus format:

Field/Column Name Paradox Field Type Lotus Format

Date D Date
Start time A10 Text
Serial number N Numeric
Customer type A3 Text
Short date A6 Text


As a rule, Paradox converts Lotus worksheet columns into the following field
types:

PARADOX 1-2-3 / Symphony

Alpha Label cells
Number Number cells (decimal places <> 2)
Dollar Number cells (decimal places = 2, or formatted as currency)
Date Number cells formatted as Date


Trouble Shooting Tips for Importing Lotus Worksheet Files

o Dissimilar Data Types in Lotus Worksheet Columns

When values in a column are not all the same data type, the following rules
apply:

1. Any column that contains a label field (text) will be converted to a
Paradox alphanumeric field, even if the column in the Lotus worksheet
consists primarily of numbers.

2. The highest number of decimal places specified in a column is used to
distinguish number and dollar fields.

3. A column containing both dates and numbers will become a Paradox
numeric field.


o Numeric Worksheet Columns Import as Alphanumeric Data

There may be occasions when a column of numbers in a Lotus worksheet may
import to Paradox as an alphanumeric data type, even though the column appears to
contain only numbers. Often, the culprit lies in the Lotus worksheet itself in
the form of a seemingly blank cell within the column of numbers. Some users are
in the habit of "erasing" a cell by hitting the space bar - while this seems to
eliminate the number that was in the cell, it also replaces the number with a
space character. The space character, while invisible, is still a text
character, and will cause the entire column of numbers in the Lotus worksheet to
convert to the alphanumeric data type. To be certain that a cell in a column of
numbers is truly empty, use /Range Erase instead of the space bar to erase a cell
in the Lotus worksheet.

An empty row between the worksheet column titles and the top of the numeric
column will have similar results. Close up the empty row in the worksheet with
either the \Move command or the \Worksheet Delete Row command.


o Complex Lotus Worksheets

If your Lotus worksheet is really complex, it may not lend itself to direct
import to Paradox, and it may not be practical in terms of time and effort to
sanitize the worksheet. If conversion of a particular worksheet's data to
Paradox is a regular occurrance, it may pay to devise a Lotus macro to combine
named ranges in the original complex worksheet into a more orderly intermediary
worksheet. The intermediary Lotus worksheet should contain a series of macros
that perform the following actions:

1. Erase the worksheet range into which the named ranges will be imported
from the primary complex worksheet.

2. Use \File Combine Add NamedRange (\FCAN) to bring in the values
contained in each of the complex worksheet's named range. If you use
\File Combine Copy NamedRange (\FCCN) instead, you will bring along any
formulae contained in the complex worksheet's named range. Since the
formulae will almost certainly have no referent in the intermediary
worksheet, the result of the formulae will yield either nonsense or an
ERR message in the cell - either will produce undesirable results when
imported to Paradox.

It is assumed that you have already set up the appropriate column
headings in the intermediary worksheet, and that the macro will
position the cell pointer just underneath the the appropriate column
heading prior to combining the named range.

3. Finally, the macro will use \File eXtract Values (\FXV) to extract only
the data and the column headings from the intermediary worksheet to
another worksheet. This step is necessary because Paradox may
interperet the macro itself to a one or more data elements simply
because it is part of the intermediary worksheet. By using \File
eXtract Values to extract the just the data and column headings to a
Lotus worksheet file, we have ensured that the only information present
in the final worksheet is that which we wish to import.

A final point: This technique lends itself well to very large and complex
worksheets. Given sufficient memory and small enough worksheet size, it would
not be necessary to use an intermediary worksheet for this process. Rather, you
could simply use the \Range Value command to copy columns contained in named
ranges in the original complex worksheet to another area in the same worksheet.
This would allow you to group the data together with the appropriate column
titles. \File eXtract Values could then be used to extract the data directly to
a new outside worksheet.


o General Tips and Hints - Lotus Worksheet Import

1-2-3 / Symphony Macros look like PAL scripts. Since macros are stored in
label cells, macros will be imported into Paradox "as is". This will result in
Paradox fields which look similar to PAL programs. If the fields are located in
a numeric column, the column will be imported as alpha instead of numeric. The
easiest fix is to clean up the worksheet before it is imported into Paradox.
Otherwise, it will be necessary to delete the records (or just fields depending
on where the macros are stored) and restructure columns from alpha to numeric if
the field type is incorrect.

Text dates import as alphanumerics. Label or text fields in the form
"mm/dd/yy" will be imported into Paradox as an alpha field. The column can be
restructured to the date format. During RESTRUCTURE, a record with a bad date
will be put into the PROBLEMS table.

1-2-3 / Symphony dates are numbers. 1-2-3 dates are stored as numbers in
serial fashion with the value "1" representing 1/1/1900 and the value "73049"
representing 12/31/2099. Lotus considers 1900 to be a leap year, which it is
not. Paradox has chosen to be consistent with 1-2-3 dates. Dates imported from
1-2- 3 to Paradox will display the same date in both programs.

Check field types after import. After an import, check the structure of the
table imported into to be sure that the field types are what you expect them to
be. Paradox makes every attempt not to throw away data records during an import.
If the field types are not what you want, change the field types using the
Modify\Restructure command.

Columns with mixed datatypes are bad news. Columns which have mixed data
types on the spreadsheet will have a general (alphanumeric) field type in
PARADOX. For instance, mixed numbers and dates in a column will import as
numeric. This mix cannot be restructured successfully. Any time you see numbers
where you expected dates, usually numbers in the 31000 range for current dates,
you should suspect a mixed datatype problem in your spreadsheet column.

Restructure only one column at a time. When restructuring more
than one data column, restructure only one column at a time. If there are data
conversion problems which cause records to be put into the PROBLEMS table,
working with one column at a time will allow you to pinpoint the problem.
Copying the table to a temporary table at each step of the restructure lets you
recover from problems without starting over.

1-2-3 and Symphony include a "General" format which displays numbers in the
form 999.99. A column in this format may look like a logical candidate for a
Paradox Dollar field. Remember, however, that in this case you have not
specifically chosen two decimal places because Lotus stores all real numbers in
floating-point form, and does not allow a choice of decimal places for the
General format and you have not specifically chosen the Currency format.

Therefore, Paradox will import a column of data with the General format as
Numbers, not as Dollars.

Paradox recognizes range formats. Paradox does not use the Lotus global
format information; instead it uses RANGE formats. A spreadsheet with two
decimal places, but with each column formatted with the RANGE command will be
imported to Paradox with the Dollar field type. A Lotus spreadsheet globally
formatted to dates will have to be reformatted using the range command.

When does Paradox provides field names? If field names are missing, Paradox
will create a field name in the form: "Field-n" where n represents the column
number of the field. In the case that duplicate field names are imported,
duplicate names will be changed to the form "Field-n" where n represents a number
beginning with 1 that uniquely identifies the field.

A spreadsheet imported to Paradox then exported back to 1-2-3 or Symphony
may not have exactly the same format it started with. The same thing could
happen going from Paradox to Lotus and back to Paradox. Remember to check field
names and formats for files making a round trip. Reformatting should handle any
problems in 1-2-3 and Symphony.

Invalid File Conditions. Paradox 1.1 will terminate the import and display
the message "Invalid Lotus File" if the file is not in the Lotus file format. If
an attempt is made to import an empty Lotus file, created by saving a spreadsheet
with no entries, Paradox will exit with an error message. If you ever try this,
delete the file ZZZXZZ which is left in your directory.

Paradox 2.0 will return to the main menu and display the message "missing data
records," with no further result.


Additional Notes and Comments - 1-2-3 and Symphony Files

You can sanitize and import most spreadsheets into Paradox without ever
getting into details about 1-2-3 internals. For the owner of an unusually
complex spreadsheet or the curious reader we provide more detailed information.
If you do not fit into either of these categories, you may safely skip this
section.

Conceptually, a spreadsheet is a two-dimensional grid of cells. You can
assign a meaning to a row of cells (e.g. "sales figures for the last 12 months")
or a column ("quantity in stock"), but rows and columns mean very little to the
spreadsheet program. For instance, it would be possible to transform the layout
of a spreadsheet so that all rows become columns and all columns become rows.
This might confuse you, but would make no difference at all to the program.

Database programs are different. In a database, each row of a table
contains a record: one complete name and address, all the information about one
customer order, or data about one inventory item. Each column contains all the
occurrences of a given piece of information in a table: the phone number for each
person in the name and address table, for instance. Since all the values in a
column are the same kind of data, they must all be the same data type.



For example, all quantity-on-hand fields must be numbers, all birthdates
must be dates, and so forth. Paradox applies all validity checks and image
settings to an entire column. The only exceptions are alphabetic strings - where
the field type determines the maximum length and the shorter strings are allowed
- and the use of scientific notation - because Paradox will mix scientific and
standard notation in a column in order to handle large numbers gracefully.

The important point is this: on import, Paradox must choose one datatype for
each column. The spreadsheet doesn't give Paradox any help in making this
decision, since it will allow any data type in any column. Paradox tries to
select the most appropriate field type for each column, based on the values in
the column (and sometimes their formats). If the portion of a 1-2-3 spreadsheet
to be imported to Paradox is being used as a database, this should not be
difficult; all the cells in a column, except the fieldname labels, should
already contain the same kind of data and use the same data type.

1-2-3 and Symphony can store three pieces of data for each cell: its
formula, its value, and its format. Ordinarily, a cell's value is displayed in
the spreadsheet, and the format and formula of the current cell are displayed at
the top of the screen. To understand completely how Paradox the ExportImport
feature works, you must know how Paradox uses these three pieces of data.
Consider the following points:

- The formula and value of a cell together determine what datatype is used for
the cell - floating point number vs. integer, for instance. This is an
internal issue, which Lotus generally hides from the user.

- A global format may be defined. In this case, the program applies this
format option to each cell, unless it is overridden, but does not store the
global format data with each cell.

- The formula, value, and format of a cell, along with the global format and
settings for such things as column-width, determine what is displayed on the
screen. Thus we must distinguish the string that is displayed from the
underlying data that determines the display.

Now consider how Paradox uses this data:

1. Paradox generally does not use formulas. This has interesting implications.
For instance, a Lotus worksheet cell whose formula is @DATE(85,9,18) has a
numeric value, and therefore will not be treated as a date unless its format
makes it a date. (The one instance where the formula is used is in a RANGE
formatted as TEXT. Formulas of the form MM/DD/YY are imported as alpha
fields, so they can be restructured as Paradox dates if desired.)

2. Paradox never pays attention directly to the way a cell is displayed
although it does use the value and format data that help produce the
display. For example, column width settings in a spreadsheet have no effect
on import. In addition, Paradox's import function never sets Paradox IMAGE
settings. For example, a column of numbers with 4 decimal places will be
imported to an number field, but the display will only show 2 decimal places
- the default - unless it is changed by the user.

3. Paradox ignores the more esoteric Lotus format options: PERCENT, +/-, and
COMMA (,). All of these are just special ways of displaying data, usually
numeric data.

4. Paradox may convert label cells either to data values or to fieldnames. To
understand how it makes its choice, recall that Paradox is expecting to
import a piece of a worksheet in 1-2-3's database format. This means that
Paradox expects the first row of the worksheet to contain labels, which are
used in 1-2-3 to identify columns. If it finds them, it converts them to
fieldnames. If it does not, it uses the first row that does contain labels:
all labels in this row become fieldnames, Paradox generates field-names of
the form "Field-n" for any column that doesn't contain a label in this row,
and all rows above this one in the worksheet are ignored. If there are no
labels in the worksheet, Paradox generates fieldnames and imports the entire
worksheet.

5. Paradox ignores centering and fill options for labels, whether they become
field names or values. The width of an alphanumeric column will be two
characters more than the longest label that becomes a field value, or one
character more than the longest string representation of a date or number,
or as wide as the fieldname, whichever is greatest.

6. This leaves us with only numeric values for cells and the DATE, CURRENCY,
COMMA (,), FIXED, GENERAL, and SCIENTIFIC formats left to consider. These
formats must be cell or range formats, not global settings.

Paradox will use its Date field type for any column formatted entirely with
any DATE options. It will use its Dollar field type if every field in a
column is formatted CURRENCY (any number of decimals), PUNCTUATION /2
decimals, FIXED/2 decimals, or SCIENTIFIC/2 decimals. 1-2-3 formats that
produce a Paradox Dollar field may be mixed within a column. Any column
that does not fall into one of the cases discussed so far becomes a Number
field.


1-2-3 / Symphony Export Overview

Paradox converts 1-2-3 and Symphony files by treating each record as a row
and each field as a column in the spreadsheet. The one exception is that row 1
of the spreadsheet will contain the Paradox field names. The remainder of the
rows will contain data. However, we do not observe the 2048 row limit of 1-2-3
(Version 1A).

The following table shows how Paradox converts its fields to
1-2-3 / Symphony format:

PARADOX 1-2-3 / Symphony

Alpha Label Cell with default format
Number Number Cell with default format
Dollar Number Cell with currency format
Short Integer Cell with default format
Date Number Cell with date format

Column widths in 1-2-3 and Symphony are for display purposes only. They do
not restrict the length of the data to be stored by 1-2-3. PARADOX will set
column widths on exported files according to the following rules:

PARADOX 1-2-3 / Symphony

Alpha Maximum of field name length and field width up to a
maximum of 72 characters
Number, Dollar Maximum of field name length or default
Short,Date width of 9



1-2-3 / Symphony Export Techniques and Considerations

ASTERISKS DISPLAYED IN COLUMN: If the file exported to 1-2-3 comes up with
some or all of a column of numbers displayed as asterisks, the column width is
too narrow. Position the cursor in the column, then use the Lotus /WCS
(Worksheet Columnwidth Set) command to set the column wide enough to display the
numbers.

LOTUS DISPLAYS 72 CHARACTERS: The widest column displayed in 1-2-3 is 72
characters, but wider alpha fields from Paradox can be stored and edited in
1-2-3.

A spreadsheet imported to Paradox then exported back to Lotus spreadsheet
may not have exactly the same format it started with. The same thing could
happen going from Paradox to Lotus and back to Paradox. Remember to check field
names and formats for files making a round trip. \Modify\Restructure should
handle any problems in Paradox.

dBASE II/III Import Overview

Transferring dBASE files into Paradox is very straightforward since dBASE
has very strict data type rules. The following table shows how Paradox fields
are derived from dBASE II fields:

Paradox Dbase II/III

Alpha Character, Logical, Memo (dBASE III only)
Number Number (decimal places <> 2)
Dollar Number (decimal places = 2)
Date Not supported in Dbase II, supported in III

For dBASE character fields, Paradox creates an alpha field of the same size,
while dBASE logical fields are converted into one character alpha fields.

dBASE III supports up to 4,000 characters in a memo field. Paradox will import
only the first 254 characters and ignore the rest (not 255 characters as the
Paradox manual says).


dBASE II/III Import Techniques and Considerations

INSUFFICIENT DISK SPACE: If there is insufficient disk space to import a dBASE II
file into Paradox, a partial file will be written and will not be erased. That
file will remain as a corrupted table and cannot be deleted. It is necessary to
delete the file from DOS.

INVALID FILE CONDITIONS: Paradox will terminate an import and display the message
"Invalid dBASE file" under the following conditions:

1. Unrecognized dBASE file format.
2. Empty dBASE II file.

DBASE III TO PARADOX TO DBASE III: One implication of all the field handling
rules is that a database imported to Paradox then exported from Paradox may not
have exactly the same format it started with. The same thing could happen going
from Paradox to dBASE III and back to Paradox. Remember to check field names and
formats for files making a round trip. Restructure should handle any problems in
Paradox.

MEMO FIELDS ARE IMPORTED AS ALPHA DATA FIELDS IN PARADOX: dBASE III memo fields
are treated as alpha fields in Paradox and are truncated, if necessary, to fit
into a Paradox alpha field.

MISSING DBASE MEMO FILE: dBASE III stores memo fields in a separate .DBT file.
An attempt to import a file with memo fields defined in the dBASE data structure,
but missing the .DBT file will cause an error in Paradox (File Open 104). The
solution is easy; just make sure both the .DBF and .DBT files from dBASE III are
in the directory you are using.


dBASE Export Techniques and Considerations

Transferring files from Paradox to dBASE is relatively simple. As we have
noted earlier, dBASE has very strict data type rules. Paradox observes the 32
field maximum limit in dBASE II, but does not observe the 1000 character maximum
for records. For dBASE III, Paradox observes the 128 field maximum limit but
does not observe the 4000 byte per record maximum. The following table shows
how Paradox fields are converted to dBASE II fields:

PARADOX dBASE II/III

Alpha Character of same width
Number Numeric - 19 digits with 4 decimal places
Dollar Numeric - 19 digits with 2 decimal places
Short Numeric - 5 digits with 0 decimal places
Date Character of width 8 - data is converted
into a character string of form "mm/dd/yy"

By exporting 19 digit numbers for Paradox number and dollar formats, we
avoid the need to scan the entire file to determine the actual digit and decimal
place requirements. This would significantly slow the export process. As in
Paradox, dBASE allows restructure of the file after import from an outside
source.

dBASE stores all data as strings of characters. This means that the width
and number of decimal places specified for fields in dBASE can affect results of
calculations. This differs from spreadsheets where the column width and number
of decimal places affect only the display format.

TWO FORMATS WILL EXPORT TO DBASE: Data may be exported from Paradox to dBASE
using either dBASE format or ASCII delimited format. ASCII data is input to
dBASE using the "APPEND FROM filename DELIMITED" command. The data structure
appended to must have been created using dBASE. Exporting directly to the dBASE
format is easier.

PARADOX TO DBASE TO PARADOX: One implication of all these rules is that a
database exported from Paradox to dBASE and then imported back to Paradox may not
have exactly the same format it started with. The same thing could happen going
from dBASE to Paradox and back to dBASE. Remember to check field names and
formats for files making a round trip. Modify\Restructure should handle any
problems in Paradox.

PARADOX DOES NOT EXPORT MEMO FIELDS: Paradox imports memo fields as regular data
fields. Once defined as a data field, that field will be exported as data, not a
memo field.

NO EXCESS FIELDS EXPORTED TO DBASE: Since dBASE II is limited to 32 fields, and
dBASE III to 128 fields, only the first 32 or 128 fields will be exported in the
dBASE format. In the case of dbASE II however, sometimes the dBASE data fields
are not correctly aligned if the Paradox table has more than 31 fields (dBASE II)
or 128 fields (dBASE III). Check your data in dBASE. If the data characters are
in the wrong field, restructure a copy of your Paradox file to 31 fields (dBASE
II) or 128 fields (dBASE III), then Export to dBASE.

INVALID FILE CONDITIONS: Paradox will terminate an import and display the message
"Invalid dBASE file" if the file has an unrecognized dBASE file format.
PFS:File, PFS Professional File, IBM Filing Assistant Import Overview

PFS and IBM Filing Assistant are programs written by Software Publishing
Corporation. The menus and appearance of the programs differ, but the file
structures are equivalent. Since Paradox is only affected by the file
structure, we can restrict discussion to one program. When we refer to PFS, this
is shorthand for "PFS and IBM Filing Assistant".

Paradox converts PFS and Filing Assistant files by converting PFS fields to
Paradox fields, and PFS forms become Paradox records. PFS does not have any
specific data types, but treats every field as a character string. Therefore,
Paradox must make a preliminary pass of the entire PFS file to determine the
field types. Any inconsistencies in fields will result in a field type general
enough to handle the inconsistencies. The table below describes how Paradox maps
PFS field information to the various field types:

PARADOX PFS:file

Alpha Any field containing a non-numeric
character
Number All fields containing numeric
characters and decimal places <> 2
Dollar Fields with numeric characters and
decimal places = 2
Date All fields are in the format
mm/dd/yy or yy/mm/dd

PFS supports a concept called "attachment pages" which can be attached to
the last page of a PFS form. These are quite similar to dBASE III memo fields
and are treated much the same during conversion. Attachment pages are converted
to alpha fields and only the first 255 characters are retained in Paradox.

PFS users will often make the input screen look pretty by using dashes,
asterisks, etc. The problem with these special characters is that Paradox cannot
always sift through them and extract the intended field name. As a consequence,
the user may see some strange field names which can be changed easily by a
Modify\Restructure in Paradox.

If field names are missing, Paradox will create a field name in the form:
"Field-n" where n represents the column number of the field. In the case that
duplicate field names are imported, duplicate names will be changed to the form
"Field- n" where n represents a number beginning with 1 that uniquely identifies
the field.
PFS Import Techniques and Considerations

CHANGE FIELD NAMES: As implied earlier, PFS does not give Paradox much help in
choosing field names. On import, Paradox will take the first 255 characters of
whatever string it gets from PFS as a fieldname, and sifts the string looking for
alpha characters. The first likely 25 non-blank characters before the colon are
selected and modified if necessary to make a unique fieldname. If field names
are unsatisfactory, clean up the PFS form before importing, or rename the Paradox
fields afterwards.

RESTRUCTURE FIELD TYPES: After an import, also check the field types to be sure
they are what you expected. If the field types are not what you want, change the
types using the RESTRUCTURE command.

IMPORTED ATTACHMENT PAGES EXPORT AS FIELDS: On import, Paradox assigns a name to
the attachment field, truncates it to 255 characters if necessary, then treats it
like any other field. As a result, a Paradox table exported to PFS never yields
attachment pages.

ILLEGAL DATES CONVERTED: Paradox decides that a column should have the field type
D if all values in the column have the right structure: strings of "mm/dd/yy" or
"yy/mm/dd". Then it begins converting these strings to its own format. In the
process, it may find that some of them are not valid dates ("02/29/85", for
instance). At this point Paradox faces a dilemma:

- There is no PROBLEMS table in which to put the offending record.

- It should not accept into a date field any value that would not be accepted
through data-entry.

- Blanking out the field implies incorrectly that the PFS file did not have a
value for the field.

Paradox handles these problems by carrying extra days into the next month
and by carrying extra months into the next year: "02/29/85" becomes "03/01/85"
and "13/01/85" becomes "1/01/86". This may be the best of a bunch of bad
situations. Unfortunately, it doesn't always work, and some invalid dates are
still produced. These bad dates can in turn result in problems with PARADOX's
display of the table, such as missing record numbers, but these problems are less
serious than they look. The solution is to correct or erase the invalid values.

RECORD ORDER (OR IT ONLY SEEMS BACKWARD): PFS stores records in LIFO (last in,
first out) order. On import, Paradox, conforms to this scheme by creating a
table with records in descending order by the PFS record number. On export,
Paradox writes records to PFS in first-to-last order. However, PFS will reverse
the order of these records when it displays them to the user. The result then,
is that a file that is imported from PFS and then exported again will have its
sequence reversed by the process, as will a table that is exported to PFS and
then imported back to Paradox. This is not a failure of Paradox's, but results
from the fact than Paradox does not try to compensate for PFS's unusual way of
ordering records.

INVALID FILE CONDITIONS: Paradox will terminate the import and display the
message "Invalid PFS File" under the following conditions:

1. Unrecognized PFS file format.
2. PFS file with no fields described.
3. Empty PFS file.
PFS AND IBM FILING ASSISTANT Files Export Overview

Paradox-exported PFS file will resemble a Paradox default free-form report
specification in appearance. Each Paradox field name will appear at the
beginning of a line and will be followed by a colon per PFS requirements. Each
of these combinations will be followed by sufficient blank space to accomodate
the corresponding Paradox field type. For example, Paradox field type A80 will
require two lines in PFS to accomodate both the field name and data input space.

Since PFS allows access to a maximum of 21 lines per screen page, Paradox
will export up to 21 fields per page before defining any subsequent pages. This
may not be what the user wishes to see, but he/she has the ability to change the
screen layouts in PFS. As a warning, Paradox does not observe the 32 page limit
imposed by PFS.

The PFS manual provides several suggestions for designing a form. One of
those advises that for fastest possible retrieval of records the user make the
first item in the form the one searched on most often. PFS uses this field to
build a quick search table in the first 8k bytes of the the file. While Paradox
is not able to duplicate the PFS algorithm for populating this table when
exporting, the user may reconstruct this table in PFS. This is done by selecting
the ADD/MOVE/CHANGE ITEMS feature from the PFS design menu and then pressing
to execute the change.


PFS Export Techniques and Considerations

IMPORTED ATTACHMENT PAGES EXPORT AS FIELDS: On import, Paradox assigns a name to
the attachment field, truncates it to 255 characters if necessary, then treats it
like any other field. As a result, a Paradox table exported to PFS never yields
attachment pages.

RECORD ORDER: This explanation is a repeat of the paragraph in import. Skip it
if you've read it already. PFS stores records in LIFO (last in, first out)
order. On import, Paradox, conforms to this scheme by creating a table with
records in descending order by the PFS record number. On export, Paradox writes
records to PFS in first-to-last order. However, PFS will reverse the order of
these records when it displays them to the user. The result then, is that a file
that is imported from PFS and then exported again will have its sequence reversed
by the process, as will a table that is exported to PFS and then imported back to
Paradox. This is not a failure of Paradox's, but results from the fact than
Paradox does not try to compensate form PFS's unusual way of ordering records.

PFS SPEEDUP TABLE: PFS builds a type of index table which speeds record (i.e.
form in PFS) access. Since the algorithm for building the table is proprietary
to PFS, it cannot be exported from Paradox. However, the user can force PFS to
rebuild the table by selecting ADD/MOVE/CHANGE ITEMS from the PFS menu and
pressing .

Additional Notes and Comments - PFS:FILE Internals

You probably want to skip this section unless you're having problems
importing a PFS file.

File definition: To define a file, a PFS user creates a data entry form or
lets PFS create a default form. There is only one data-entry form per file,
though each form may be up to 32 pages long. The user provides no other
structural definition of the file; PFS learns everything it knows about the
contents of the file from the form. Among the things it deduces are:

1. Field names: Field names are strings of characters on the data-entry form
that end in colons. There is no distinction between field names and other
literals; all the characters up to a colon are the field name. PFS:file can
work this way because it always displays data on forms, but clearly it
creates problems for Paradox Import, since Paradox needs a fieldname of 25
characters or fewer for each column.

2. Data fields: Every field name on a form indicates the presence of a data
field immediately after the colon that ends the fieldname. Maximum field
lengths are set implicitly as the number of characters from the start of the
field to the next literal on the screen.

3. Index Field: PFS uses the first eight characters of the first data field to
create a type of index table which permits faster access to the data
records.

4. Data types: Every field is alphanumeric and variable in length.

Data structure: Each PFS record is a chain of 128-byte blocks. Fields are stored
in the chain in sequence as variable-length strings. A description of the file's
form is stored in a header, followed by the data records, which are chained in
LIFO (last in, first out) order. There are no indexes. The first eight
characters of the first field of each record are used to create an entry in a
table, which allows queries based on that field to be answered more quickly. The
table is a hash table, and the hashing scheme is proprietary. So Paradox cannot
re-create the hash table when it exports a table.

Data entry: PFS data entry, like query and display operations, is done through
the form. In fact, PFS does not use the word "record", referring to each record
as a "form" instead. This offers some room for confusion, partly because PFS
uses the word "form" in two different senses, and partly because it is
non-standard usage in database terminology. We'll consistently use the word
"record" here for what PFS calls a "form".

The PFS attachment page feature is like the dBASE III memo field; it allows the
user to append variable-length comments or narrative to a record. More than one
attachment page per record is allowed. In this case, the pages are thought of as
one multi-page field, not as multiple fields - for instance, a record must have
an attachment page #1 before it can have an attachment page #2. Since all PFS
fields are variable length, the attachment page feature is really not much more
than a way to provide a variable number of form pages per record.
Visicalc (DIF) Files

The DIF format was developed to allow transfer of data among different
software packages and programming languages. The DIF file is a text file with
data information explicitly defined. VECTORS specifies the number of columns
(fields), and TUPLES specifies the number of rows (records) in the file. There
are two sections in each DIF file, headers and data. Unless you wish to print a
DIF file and decode it, the next section will tell you more than you ever wanted
to know about DIF files.


VISICALC (DIF) Import Overview

DIF files are quite similar to Lotus files in that data types in any given
vector may differ between tuples. (Remember that "vector" and "tuple" in DIF
parlance refer to fields and records respectively.) Since the DIF format is
intended to allow import of data from programs which can be very dissimilar to
the Paradox database structure, it may be difficult to determine a consistent
field type for a Paradox field. Data imported from data base programs and other
programs with structured data should have few problems. Paradox uses the
following conversion rules for DIF files:

PARADOX DIF File

Alpha Text
Number Numbers (Decimal places <> 2 )
Dollar Numbers (Decimal places = 2 )
Date Text vectors all in the form
mm/dd/yy or yy/mm/dd

Please note that when values in a vector are not the same datatype, the following
cases apply:

1. Any vector that contains text will be converted to a Paradox alpha field
with the exception of text in the format "mm/dd/yy" or "yy/mm/dd" which will
be treated as a Paradox date.

2. The highest number of decimal places specified in a vector is used to
distinguish number and dollar fields.

3. A vector containing both dates and numbers will become a Paradox alpha
field.

There are two methods of generating Paradox field names from a DIF file.
The first, and probably the better one, is to use the LABEL feature of DIF to
supply the exact name for each vector (field). If the user does not have this
option or if the LABEL feature is omitted, Paradox will create a field name in
the form: "Field-n" where n represents the column number of the field. In the
case that duplicate field names are imported, duplicate names will be changed to
the form "Field-n" where n represents a number beginning with 1 that uniquely
identifies the field.

VISICALC (DIF) Import Techniques and Considerations

INSUFFICIENT SPACE: If there is insufficient disk space to import a DIF file into

Paradox, a partial file will be written and will not be erased. That file will
remain as a corrupted table and cannot be deleted. It is necessary to delete the
file from DOS.

INVALID DIF FILE IMPORTED FROM RBASE: If you get an "Invalid DIF file" error
message while importing a DIF file created by RBASE, check the values specified
for the counts in VECTORS and TUPLES. ("Read Arcane Details About DIF Files"
earlier in this section if you skipped it.) If the counts are switched, then use
your text editor to swap the values given in the VECTORS and TUPLES counts.
(Your word processing program will work for this only if it has a text editor
mode, such as Wordstar's Non-document mode). The VECTORS count should equal the
number of fields in your file, and the TUPLES count should be the number of
records.

INVALID FILE CONDITIONS: Paradox will terminate the import and display the
message "Invalid DIF File" under the following conditions:

1. TABLE, VECTOR, or TUPLE information missing.
2. No data records in the file.
3. Actual number of fields not equal VECTOR number of fields.
4. Empty DIF file.
5. Invalid DIF data types.



VISICALC (DIF) Export Considerations

LONG FIELDS IN LOTUS: TRANSLATE in 1-2-3 and Symphony will not handle DIF files
which have fields larger than 72 characters. TRANSLATE will issue an error
message and quit translating at the field which is wider than 72 characters.
Export directly to 1-2-3 or Symphony files to avoid the problem.



Additional Notes and Comments - DIF Files

Five types of headers (topics) are applicable to Paradox files, however
there are other topics supported by the DIF standard. The five topics Paradox
uses are TABLE, VECTORS, TUPLES, LABELS, and DATA. Headers are of the form:

Topic
Vector number, numeric value
"String value"

The TABLE topic is required as it identifies the file:

TABLE
0,version

"title"
The VECTORS topic is required and defines the number of fields in a tuple:

VECTORS
0,count
""

The TUPLES topic is required and contains the number of records in the file:

TUPLES
0,count
""

The LABELS topic is optional and identifies field names to be given to each
vector in the file:

LABEL
vector number, line number
"label"

The DATA topic identifies the end of the header section and the beginning of the
data section of the file:

DATA
0,0
""

The data section is made up of data values organized by tuples in vector
sequence. Thinking in terms of a spreadsheet, all the data in the first row is
written, then all the data in the second row is written, and so on until the end
of the data is reached. In Paradox, all the fields in the first record are
written, then all the fields in the second record are written, and so on. Each
data value has two lines of the form:

Type indicator, number value
String value

Type indicators contain the values:

-1 A flag indicating the beginning of a tuple (BOT) or end of data
(EOD); the string value will tell which it indicates.

0 Flag indicating numeric data. The value is in the number value
field.

1 Flag indicating a character string. The string is located in the
String value field.


THE DIF FILE by Donald H. Beil, published by the DIF Clearing House,
describes the DIF standard. It was the basis for this discussion of DIF files.
It is recommended as a reference if you are interested in more information re-
garding DIF files.
WordPerfect Secondary Merge Files

Introduction

Because of the growing popularity of WordPerfect and Paradox, there is
increasing interest in the best techniques for data interchange between the two
packages. Production of form letters from a list of names is usually the
frequent application demnding data file transfer between the two packages.

Although the Paradox Free Form report generator can perform a satisfactory
job of constructing a form letter, it lacks many of the features of WordPerfect.
For instance, the Paradox Free Form report generator offers nothing in the way of
block move or copy, search and replace, or other formatting capabilites.

On the other hand, WordPerfect offers limited ability to input and maintain
lists. Data entry in WordPerfect Secondary Merge format is laborious.
WordPerfect offers nothing in the way of a structured means of data entry, and
offers no validation of input to correspond to Paradox's Forms View with its
Valcheck and Picture facilities. The WordPerfect Sort facility uses an obscure
syntax, is difficult to use, and requires considerable effort to learn.

In general, Paradox's relational and data manipulation abilities eclipse
those of WordPerfect (where WordPerfect offers them at all), and WordPerfect
offers superior text formatting and manipulation capability. Thus, it is often
more practical to input and maintain lists in Paradox, and export all or part of
the list as desired to WordPerfect's unique Secondary Merge format.


Paradox to WordPerfect Secondary Merge Files - Export Techniques and
Considerations

You may have noticed that Paradox has no specific options within the
Tools\ImportExport menu selections that deal with WordPerfect Secondary Merge
Files. WordPerfect, like many other word processing packages, uses a unique
format for field and end-of-record delimiters. Fields are separated by a Merge R
(Ctrl-R followed by a hard carriage return); records are separated by a Merge E
(Ctrl-E followed by a hard carriage return). The decimal representation of Merge
R is 018\013; that of Merge E is 005/013. Finally, Paradox offers no means of
representing WordPerfects unique field and end-of-record delimiters in the Custom
Configuration Program - control characters, e.g., ^R and ^E cannot be entered.

The problem of converting Paradox tables to WordPerfect Secondary Merge
Files yields to a fairly simple solution. WordPerfect includes a Convert program
(CONVERT.EXE), ususally found on the Learning diskette supplied with the
WordPerfect package. Because there is no way to directly convert from Paradox to
WordPerfect Secondary Merge File format and vice versa, it is necessary to use an
intermediary file format usable by the conversion facilities offered by both
programs. The file format most closely supported by both programs is what
Paradox calls ASCII Delimited; WordPerfect's term for this is Mail Merge format,
Mail Merge being an old WordStar term for a more-or-less standard comma delimited
ASCII text file.

Let's look at the STRUCT table of a data base called Expwp.

STRUCTField NameField Type
1 Date 1 A13
2 Item A30
3 Amount A15
4 Date 2 A13
5 Qty 1 N
6 Qty 2 N

The STRUCT table shows that the Expwp table is composed of four
alphanumeric fields of varying size and two numeric fields. The Paradox
Tools\ExportImport\Export\ASCII\Delimited option converted the Expwp table to the
resulting ASCII file:

"10/23/1984"," INTERNATIONAL PAINT "," -931.77","1984",10,23
"10/26/1984"," PACIFIC BELL "," 4475.00","1984",10,26
"11/13/1984"," INTERNATIONAL PAINT "," 3800.00","1984",11,13
"11/16/1984"," ANSA CORP "," 1482.00","1984",11,16
"11/28/1984"," INTERNATIONAL PAINT "," 203.35","1984",11,28
"12/10/1984"," ANSA CORP "," 660.00","1984",12,10
"12/10/1984"," COMPUTER RESULTS "," 945.00","1984",12,10
"12/12/1984"," ANSA CORP "," 2700.00","1984",12,12
"12/12/1984"," INTERNATIONAL PAINT "," 1280.00","1984",12,12
"12/24/1984"," PACIFIC BELL "," 4300.00","1984",12,24
"84/1/23 "," COMPUTER RESULTS "," 1740.00","1984",10,23

Next, the ASCII file was processed through WordPerfect's CONVERT program. The
CONVERT program offers the following options:

1 WordPerfect to another format
2 Revisable-Form-Text (IBM DCA Format) to WordPerfect
3 Navy DIF Standard to WordPerfect
4 WordStar 3.3 to WordPerfect
5 MultiMate 3.22 to WordPerfect
6 Seven-bit transfer format to WordPerfect
7 Mail Merge to WordPerfect Secondary Merge
8 WordPerfect Secondary Merge to Spreadsheet DIF
9 Spreadsheet DIF to WordPerfect Secondary Merge

Enter number of Conversion desired _

Choice 7 - Mail Merge to WordPerfect Secondary Merge - was chosen to convert
the ASCII file to WordPerfect Secondary Merge file format.

CONVERT will prompt you for additional information - getting this right will be
essential to success in the conversion process.

First, you are asked to:

Enter Field delimiter characters or decimal ASCII values enclosed in {}
,

o At the cursor, type in a comma followed by a .


Next, you are asked to:

Enter Record delimiter characters or decimal ASCII values enclosed in {}
{013}{010}

o Carefully enter {013}{010} followed by a at the cursor
prompt.



Finally, you are asked to:

Enter character to be stripped from records
"

o Carefully enter a " (double quote) followed by a .


The result of this operation will produce a "clean" Wordperfect Secondary
merge file. For brevity's sake we do not show the entire file:

10/23/1984
INTERNATIONAL PAINT
-931.77
1984
10
23
10/26/1984
PACIFIC BELL
4475.00
1984
10
26
11/13/1984
INTERNATIONAL PAINT
3800.00
1984
11
13


WordPerfect Secondary Merge Files to Paradox Table Format - Techniques and
Considerations

The problem of converting WordPerfect Secondary Merge Files to Paradox table
format requires a somewhat more complex solution. As before, it is necessary to
use an intermediary file format usable by the conversion facilities offered by
both programs. WordPerfect's Convert program (CONVERT.EXE), is a bit on the
stingy side when it comes to conversion from Secondary Merge File format to any
other format. In fact, the only conversion option for Secondary Merge Files
offered under the WordPerfect to another Format menu selection is that of
converting Secondary Merge to Spreadsheet DIF.

Not a problem, you say - Paradox will import DIF files through the Visicalc
option under the Tools\ExportImport\Import menu selection. True enough.
Unfortunately, WordPerfect's Secondary Merge to Spreadsheet DIF conversion leaves
off a vital piece of information that Paradox requires to successfully convert
DIF to Paradox table format. Specifically, Convert does not supply the DIF
file header that identifies the field names for the table. Thus, Paradox will
use the contents of the first record in the Secondary Merge file for the field
names of the Paradox table. Given the structure of Secondary Merge files, Convert
really has no way to know what the field names are or should be unless we supply
them.

In order to give WordPerfect's Convert program the information it needs to
build the correct field name headers for the DIF file, it will be necessary to
modify the Secondary Merge file that we wish to convert. Here's how it can be
done:

Let's look at the structure of a typical WordPerfect Secondary Merge file.

10/23/1984
INTERNATIONAL PAINT
-931.77
1984
10
23
10/26/1984
PACIFIC BELL
4475.00
1984
10
26
11/13/1984
INTERNATIONAL PAINT
3800.00
1984
11
13

There are only three records here, and the format is that of the standard
WordPerfect Secondary Merge file, i.e., a ^R to indicate the end of each field,
and a ^E to indicate the end of each record.
Examine the structure of each record - there are six fields for each record,
and the information contained in them seems to fall into the following
categories: a date, a name, an amount, another date, a quantity 1, and another
quantity. A Paradox table based on these fields might have the following
structure:

STRUCTField NameField Type
1 Date 1 A13
2 Name A30
3 Amount A15
4 Date 2 A13
5 Qty 1 N
6 Qty 2 N

With the aforementioned Paradox table structure in mind, we will now add a
record to the beginning of the WordPerfect Secondary Merge file. In other words,
the record that we will add will now be the first record in the file. The record
that we will add will consist of nothing more than the field names that we would
like our Paradox table to have:

Date 1
Name
Amount
Date 2
Qty 1
Qty 2
10/23/1984
INTERNATIONAL PAINT
-931.77
1984
10
23
10/26/1984
PACIFIC BELL
4475.00
1984
10
26
11/13/1984
INTERNATIONAL PAINT
3800.00
1984
11
13


Once you have modified your WordPerfect Secondary Merge file per the example
above, Convert will produce a DIF file that Paradox will import. The resulting
table will have the field names that are conmtained in the first record of the
WordPerfect Secondary Merge file.