TIP #15 - Exporting a Data file from filePro to ASCII

Written by Nancy Palmquist, Copyright 1998, All Rights Reserved

One of the neat features of filePro is how easy it is to spin off data files for other programs. I hope this will help take the mystery out of this process. The common data transfer mode is in ASCII Delimited files. The language can mean a few things but if you cannot get clear answers, try the variations below. In most cases, one will work great. If you can get your hands on a sample file, all the better. Look at it with a text editor and see how the data is listed. The file will look something like this:

Joe Smith,123 Main Street,Pittsburgh,PA,15222,(412) 835-9417

Mary Jones,333 West Street,Bethel Park,PA,15102,(412) 835-9419

If you study the two records, you will see 6 fields of data, i.e. Name, Address, City, State, Zip, Phone. These are followed by CR/LF (carriage return/line feed). This is almost always the end of record delimiter if the data will end up on a DOS platform of some sort. (Windows included.)

The data format would be repeated for as many records as you select to send. There are usually only two variations to this format.

Variation 1:Commas are used to mark the end of one piece of data and the start of the next. If your data may contain commas, it is necessary to include quotes around the data. The data would then look like this:

"Joe Smith","123 Main Street","Pittsburgh","PA","15222","(412) 835-9417"

"Mary Jones","333 West Street","Bethel Park","PA","15102","(412) 835-9419"

Variation 2: Usually to use this file as a merge data file, for a word processor or a spread sheet as a chart of data, the first record might require headings and then the data. The sample processing will create a file of this type.

To start an export, first create an Output Format that is a process only type. This is used as the format name and provides the sort and security, if any is necessary. Then load Define Processing and start an output format with the same name as the output format that was created.

Refer to the example processing to set up your programming. This will create a file called "namelist.wp".

Destination Filename

DOS/NETWORK: The file is written in the current directory. Use a full pathname to be sure of the destination file, (C:\namelist). Many versions (4.1 and older) of filePro will insist on adding the .wp to whatever name you give so plan on it.

UNIX: The file is written in /appl/fpmerge. Use a full pathname to be sure of the destination file, (/appl/namelist). Many versions (4.1 and older) of filePro will insist on adding the .wp to whatever name you give so plan on it and rename it later.

FilePro 4.5 and newer - An environment variable will allow you to turn off the .wp extension. PFADDWP=OFF will disable that function. You can add this line to the top of your table to set it just for this process.

PUTENV PFADDWP,OFF (Case is not important)

Varying the Filename

On versions 4.1 and newer, a variable can be used for the filename:

then: fn="name"&yr{"" 'Make the filename you want

then: export ascii name=(fn) r=\n f=, o=" c="

When making a filename be careful of some special codes. Never follow the backslash (\) with a quote ("). If you want to show the filename, do not use \r, \k, \g, \x, \i, \a, \f, \b, \c. These all have special meaning to SHOW, MSGBOX, etc. and will not display properly. However in my tests, they will create a valid filename when used in that context.

Options for special codes
The extra \ in the table below will allow you to print this correctly.  Do not use the extra \ when typing your code.

\\r	carriage return	chr(13)
\\n	new line	chr(13)&chr(10)
\\f	form feed
\\t	tab	chr(9)

Option	Description
Record delimiter: r=	This can be pulled from the chart of options above or can be a keyboard character. Required.
Field Delimiter f=	This can be whatever keyboard character you want, usually it is comma, tab, pipe or tilde(~). Required.
Opening Delimiter o=	This would be the character that opens the data field. It will always be immediately following a field delimiter or a record delimiter. Optional.
Closing Delimiter c=	This would be the character that closes the data field. It will always be immediately preceding a field delimiter or a record delimiter. Optional.

Sample Export Processing

---

Written by Nancy Palmquist

Copyright, 1998 by Virtual Software Systems.  All rights reserved.

---