EXPORT (not included in filePro Lite)

Syntax:

Then: export type filename options

filename can be an expression by assigning it as an alias as in:

Then: export type aaa=(exp) options

Version Ref: 4.8 (enhanced) (not included in filePro Lite)

Description:

EXPORT creates spin-off files to be used with other programs. EXPORT can create two types of files, ASCII files or application specific files:

Structured ASCII (Fixed length ASCII):

Then: EXPORT ASCII name -X [-A]

Delimited ASCII:

Then: EXPORT ASCII name R=r F=f O=o C=c [-A]

where "r", "f", "o", "c" are delimiters for the ASCII file and optional flag [-A] allows you to append to the end of a file. If the [-A} flag is used and the file does not exist, filePro will create it.

"R" is the record indicator.
"F" is the field separator.
"O" is opening field delimiter. <-- Not often used.
"C" is closing delimiter. <-- Not often used.

-A means to append the exisitng file.

Example:

Then: EXPORT ASCII test=test.txt R=^J f=\^ -A

The above would append a file named "test.txt" in your current directory containing a "linefeed" character at the end of each record with and a "^" between each data field. Notice the different use of "^" symbol. In the first case it is used as a control character to send a "linefeed" and in the second case it is considered as a literal "^" since it is preceeded with a "\" backslash. Also notice that the "O" Opening and "C" Closing delimiters are not used in the above example. These are also optional and seldom used.

IMPORTANT: You may use a few special codes with the delimited ASCII format as delimiters or field separators:

\r for carriage return
\f for form feed
\n for new line
\t for tab

You can also use punctuation characters, ASCII codes, and control codes as delimiters and separators. Use the caret ( ^ ) followed by a letter to indicate a "CTRL" character e.g. ^J, ^L, ^M, etc. Refer to the "Character Table" in "Define Processing" help for "CTRL" characters. In cases where you want to use the caret "^" as a literal, remember to preceed it with a backslash e.g. \^.

Note: -A flag is only available for the "EXPORT ASCII" function.

Specialized Export Formats:

       Then: EXPORT DIF name
       Then: EXPORT MULTI name [ R=n | C=n ]
       Then: EXPORT WORD name
       Then: EXPORT WordPerfect name

Then: name(1)=aa ; name(2)=ab ; ... ; name(n)=zz

Using EXPORT requires a minimum of two statements: the first defines the name of the spin-off file (sometimes called a merge file); the second (and additional statements, if necessary), tells the program which fields in the exported file are to accept the data.

Each "EXPORT" definition must be alone on its "Then" line. Use at least three characters for the filename, and don't start with a number.

Each field assignment specifies the data to be put into the fields of the exported file.

Then: mergename(n)=exp

where "mergename" is the filename (or alias of the exported file), "n" is the specific number designating the desired field in the exported file, and "exp" is the supplied data. Note that you can assign results of text and math formulas to merge fields and that you can use literals. For example:

Then: mergename(2)="Show me";mergename(4)=3;mergename(8)=aa+bb

You can, of course, put more than one assignment per line (separate them with semicolons), and use more than one line.

Structured ASCII (Fixed length ASCII)

Then: EXPORT ASCII name -X

Creates an ASCII file called "name" in structured format.

This is a very useful export format. The entire field is placed in the export file. No field separators are required because each field takes up exactly its length in characters whether there is data in the field or not. The record delimiter is a new line by default. This format is becoming more and more of a standard means for exchanging files between systems and applications.

NOTE: Structured ASCII files can be immediately read by filePro. All that is needed is the layout of the field structure, i.e., the map of the file. This list of fieldnames, lengths, and data types is simply entered into filePro as an "alien" file under Define Files. The structured ASCII file is set as the "Data File Name", and it can then be instantly read and written to by filePro programs. Indexes can be built on the alien file as well. (There are only two important differences between alien files and regular filePro files. Regular filePro files have a 20 byte header at the beginning of each record that holds system maintained information such as record creation date, created by, etc. Secondly, an alien file can not "reclaim" deleted record space by adding such records to a "freechain" of available records [a multi-user function].)

IMPORTANT: Make sure to consider the record delimiter when using an alien file. This is usually one or two characters that are not mentioned in the file layout (or map). If your records look like scrambled eggs when you go into Inquire, Update and Add on an alien file, try adding a one-character field to the end of the map. DO NOT RESTRUCTURE THE FILE WHEN PROMPTED! Try viewing the file again in IUA. If it still doesn't look right, try changing the one character field at the very end of the map to a length of 2 characters. Again, DO NOT RESTRUCTURE THE FILE WHEN PROMPTED! This should clear things up and the file will look okay. This last field does not ever have to be shown on the screen or used anywhere. It is a place holder and simply must be in the map to allow it to overlay the data correctly.

The specialized export formats, can be used by various application programs. The DIF format is sometimes available to spreadsheet programs. The MULTI format is used by Multiplan an archaic spreadsheet program. (This export format allows you to use the optional row equals or column equals parameters to designate where exported data will begin on a spreadsheet.) The WORD format is used by WordStar an archaic word processing program. The WordPerfect format is used by the WordPerfect word processing program. (HINT: The WordPerfect format can be easily read by Microsoft Word. Do not use the WORD format for anything but WordStar.)

ASCII files: EXPORT can create ASCII files in either structured or delimited form. Structured ASCII files set aside a specific number of characters for each field in every record. Fields in the exported file retain their specified length regardless of whether the supplied data for these fields fills that length or not. Each record in a structured ASCII files is separated by a newline. Delimited ASCII files set a specific character as a field separator (delimiter), and, the fields in the export file shrink or expand to fit the length of the data within each field. Delimited files may also employ opening and closing record indicators, however, these special delimiters are not frequently used these days.

Application specific files: EXPORT can create several application specific files. These formats can be used to merge data from filePro into various spreadsheet and word processing applications.

Technical Notes:

Overriding extensions

EXPORT adds default extensions to the merge file name, depending on the parameter. For example, if your merge name is "letters" and your parameter is either ASCII or WORD, the spin off file's name is "letters.wp". Other extensions are ".dif" for DIF and ".sl" for MULTI.

For example:

Then: export ASCII aaa=/tmp/rawdata f=, r=\n
Then: aaa(1)=2 ; aaa(2)="" ; aaa(3)="P" ; aa(4)=14 ; end

filePro will append a ".wp" to this type of an export if you do not already have a suffix on the export filename. The code above will create a file called "/tmp/rawdata.wp". To change this behavior, just add the suffix you would rather have: ".txt", ".doc", etc. To override any of the default extensions ("dif," "sl," or "wp"), set the exported filename equal to the name you want with the new extension. Use a complete pathname if desired:

Then: export EXPORTTYPE alias=filename.newext [options]

IMPORTANT: Remember that on DOS 3.1 systems an extension can only be comprised of 3 letters.

There is also an environment variable, PFADDWP, that turns the behavior of adding ".wp" to exported filenames off and on. The default is ON.

5.0.14 Change

EXPORT ASCII/WORD would always export the same number of fields, regardless of whether the fields were assigned to on each record, even if they were only referenced in a comment. Now, filePro will only export the number of fields as the highest-reference field actually assigned.

For example:

If:

Then: out[1] = 1 ; out[2] = 4

If: xx = "y"

Then: out[3] = 3 ; out[4] = 4

If:

Then: ' out[5] = 5

filePro would previously always exported 5 fields. Now, if x="y" is true, it will export 4 fields, and if false will export 2 fields.

To revert back to the old behavior, set PFEXPORTALL=ON.

Overriding Default Directories and Paths

On DOS systems, filePro puts the exported spin off file in the current directory.

On UNIX/XENIX systems, it puts the file in ".../fpmerge" directory (as governed by the PFDIR environment variable and the /etc/default/fppath file). There must be an "fpmerge" directory in the same directory as your "filePro" directory.).

You can place the exported file in any directory (for which filePro has write permission) by setting the exported filename equal to the desired pathname:

Then: export exporttype alias=\dir\mergename.ext [options]

Use the separator mark (\ or /) appropriate to your operating system.

Restrictions:

An "IMPORT filename" definition must be alone on its "Then" line.

Only one EXPORT or IMPORT statement with the same merge file name is allowed per processing table. In other words, you can't have the statement, "EXPORT MULTI List" followed in a few elements by "IMPORT WORD List."

Examples:

ASCII Files

Fixed Length ASCII:

Then: export ASCII filename -X

When you use the "-X" flag, the program creates one long string per record, maintaining the defined lengths of the filePro fields. In other words, it does not strip blanks on either side of any data. For example, your first data field is 10 characters long, the second is 15 characters long and the third is a 7 character numeric field. The exported string would look like this (without the quotes of course):

"John Smith 123.45\r"

The record delimiter (carriage return or line feed), depends on the operating system.

Delimited ASCII:

Then: export ASCII aaa=/tmp/merge.txt f=, r=\n

When you specify field and record delimiters, the program creates one long string per record, by marking each exported field with the designated field separator. It is important to note that BLANK space to the right of any supplied data will be removed. For example, your first data field is 10 characters long, the second is 15 characters long, the third is a 12 character field, and the fourth is a 7 character numeric field. The exported string would look like this (without the quotes of course):

"John,Smith,policeman,123.45\r"

If you make an assignment to an exported field in a delimited ASCII format and the data assigned is null (i.e., the field or expression being assigned is blank), the exported string will still surround the designated exported field with the field separator. For example if the above example was assigned in the following way:

Then: aaa(1)=2;aaa(2)=3;aaa(3)=4;aaa(4)=19

and field 4 on this particular filePro record was blank, the exported string would look like:

"John,Smith,,123.45\r"

Microsoft Excel Import Files

Although Microsoft Excel will import various formats, a "Comma Separated, Quote Delimited" format is more commonly used. Use the filePro EXPORT word option to create this type of file.

Simple:

Then: export Word cust
Then: cust(1)=1 ; cust(2)=3 ; cust(3)=4<6

Elaborate:

       Then: 'The following will create a merge file of name/addresses
       Then: 'that will automatically close up blank lines when merging.
       Then: FNAME:1
       Then: LNAME:2
       Then: COMPANY:3
       Then: ADDRESS1:4
       Then: CITY:5
       Then: STATE:6
       Then: ZIP:7
       Then: COUNTRY:13
       Then: export Word merge=C:\wpmerge.wp
       Then: dim array[4](50);clear array
       Then: i(1,.0)="1"
         If: FNAME ne "" or LNAME ne ""
       Then: array[i] = ""{ FNAME < LNAME;i=i+"1"
         If: COMPANY ne ""
       Then: array[i] = ""{ COMPANY;i=i+"1"
         If: ADDRESS1 ne ""
       Then: array[i] = ""{ ADDRESS1;i=i+"1"
         If: CITY ne "" or STATE ne "" or ZIP ne "" or COUNTRY ne ""
       Then: array[i] = ""{ CITY < STATE < COUNTRY < ZIP;i=i+"1"
       Then: merge(1)=array["1"]
       Then: merge(2)=array["2"]
       Then: merge(3)=array["3"]
       Then: merge(4)=array["4"]
       Then: end

Word Processing and Spreadsheets Files

WordPerfect:

Simple:

Then: export WordPerfect cust
Then: cust(1)=1 ; cust(2)=3 ; cust(3)=4<6

Elaborate:

       Then: 'The following will create a merge file of name/addresses
       Then: 'that will automatically close up blank lines when merging.
       Then: FNAME:1
       Then: LNAME:2
       Then: COMPANY:3
       Then: ADDRESS1:4
       Then: CITY:5
       Then: STATE:6
       Then: ZIP:7
       Then: COUNTRY:13
       Then: export WordPerfect merge=C:\wpmerge.wp
       Then: dim array[4](50);clear array
       Then: i(1,.0)="1"
         If: FNAME ne "" or LNAME ne ""
       Then: array[i] = ""{ FNAME < LNAME;i=i+"1"
         If: COMPANY ne ""
       Then: array[i] = ""{ COMPANY;i=i+"1"
         If: ADDRESS1 ne ""
       Then: array[i] = ""{ ADDRESS1;i=i+"1"
         If: CITY ne "" or STATE ne "" or ZIP ne "" or COUNTRY ne ""
       Then: array[i] = ""{ CITY < STATE < COUNTRY < ZIP;i=i+"1"
       Then: merge(1)=array["1"]
       Then: merge(2)=array["2"]
       Then: merge(3)=array["3"]
       Then: merge(4)=array["4"]
       Then: end

Note : Later versions of WordPerfect adopted the use of standard merge file formats. If you are using WordPerfect 6.0 or later, try using EXPORT WORD to create a standard CSV format and select the "ASCII Delimited" option in WordPerfect's merge options.

WORD (WordStar not Microsoft Word):

Here is a typical WORD record export:

"Smith", "John", "123.45", "01/06/1986" <cr/lf>

WordStar uses quotation marks as field delimiters, commas as field separators, and carriage return line feed codes as record separators.

HINT: Microsoft Word can make use of a delimited ASCII file as a merge document.

MULTI:

Below are two EXPORT MULTI statements. Data from fields 22 and 23 in the source filePro file will be arrayed across the spreadsheet starting on row 3. In this example, the exported filename (the one to be merged with the spreadsheet) is called "merge."