ClarisWorks: Exporting Fixed Length Fields

Many types of databases require that each field have a certain number of characters in each field, usually referred to as a fixed length format. ClarisWorks databases use what is called a variable length format. To export ClarisWorks files in a fixed length format, follow the instructions below.

Explanation of Variable and Fixed Length Formats
In a variable length format database file, instead of counting the number of characters to determine where the next field begins, the program looks for a delimiting character. A tab, for example, is placed in between field values in a tab separated text file. If you have two fields, such as First Name and Last Name, here is what the program stores:

In a variable length format : Mary*Smith (where the * represents a tab)
In a fixed length format with 10 characters per field: Mary^^^^^^Smith^^^^^ (where the ^ represents a space).

Notice that there are six spaces after 'Mary' (4 characters 'M-a-r-y' plus six spaces equals ten) and five after 'Smith.' If you type in fewer characters than required by the field, the program adds spaces to make up the difference.

Most fixed length formats allow from 1 to 30 characters per field. Seldom do they allow for more characters than that. Since a variable length format does not care exactly how many characters there are in a field, the amount of text you can put in one is usually much higher. ClarisWorks , for example, allows you 510 characters in a text field .

ClarisWorks will only export variable length fields, but you can create a calculation that will translate your fields to a fixed length format. Then you export only the fixed length field. When that is imported into the database that follows a fixed length format, it will separate the information out into separate fields.

To Export Fixed Length Fields From Clarisworks:
ClarisWorks does not have a mechanism to export just one field, so you have to take a couple of extra steps:

1) Make a copy of the database. CAUTION: DO NOT FOLLOW THESE INSTRUCTIONS ON YOUR ONLY COPY OR YOU WILL LOSE THE INFORMATION IN IT.

2) Create the calculation field:
Assuming that you need the First Name and Last Name fields exported with 10 characters each, here is the calculation that you would use. It is only different from the FileMaker Pro calculation in that each field name has to have apostrophes around it.

ExportField (Calculation, text result) = Left('First Name' & "^^^^^^^^^^",10) & Left('Last Name' & "^^^^^^^^^^",10)

You will need the quotation marks. And, again, the caret (^) stands for a space.

For each additional field, add this to the end of the calculation:

& Left(Name of your field & "^^^^^^^^^", Number of characters there are supposed to be in the field)
The number of carets (^) should equal the number of spaces needed in the field.

3) Change the type of the field from Calculation to Text.

4) Delete all of the rest of the fields from Define fields. If you have not made a copy of your database, or if you are not completely comfortable doing this, then do not attempt this. This will delete the information in your database except where it is listed in the fixed length format, and it is difficult to get it back into separate fields.

5) Go to the File menu and choose Save As and save the file as ASCII text.