Virtual Software Systems - the FilePro Database Applications Specialists

Tip # 24 - Exporting a file with Headings

A very common issue with computer data bases is to exchange data with other software. I recently wrote this routine to export names, addresses to be loaded into other software. For a better documented exchange of data, the first record sent identifies the data order by sending headings for each data field. Use this to match the data to the correct identifiers in your new software.

A process table like this requires a Process-Only Output format be created also.

Naming the destination can be the trickiest part of this program. On a DOS/NETWORK system, the file would be written to the current directory. This can make it difficult to find. To solve this problem, I like to make complete pathnames. Then the file will be exactly where I want it. To do that I have used the Environment variable PFDATA to keep the file on the same drive that filePro is using. Then I asked the user for a filename. This could be hardcoded if you wish.

On Unix, a file created with EXPORT would be assumed to belong in $PFDATA$PFDIR/fpmerge or /appl/fpmerge on many systems. In processing this would be getenv("PFDATA"){getenv("PFDIR"){"/fpmerge"

Try to use environment variables for this type of programming whenever you can. This will make your code easy to move from one place to another.

This type of EXPORT with the r=\n will cause a different end of record marker to be used depending on the Operating system, On DOS the CR LF is used but on UNIX the LF is used. If you are creating a file that will be sent to a DOS platform you will need to translate the LF to CR LF before the DOS system can read it correctly. OR you need to create it with the correct code initially. I often will use the following command to translate after creating the file.

xtod filename.txt >dosfile.txt

(command xtod can be read Xenix TO Dos there is also a dtox command that works the other way.)

Processing written for a DOS /Network system with filePro 4.8.09DN9.
--------------------------------------------------------------------------------
1 ------- - - - - - - - - - - - - - - - -
If: 'export data for import to excel or word processing.
Then: ' nlp 5/31/00
2 ------- - - - - - - - - - - - - - - - -
If: fn ne ""
Then: goto start
3 ------- - - - - - - - - - - - - - - - -
top If: fn eq ""
Then: input fn(30,,g) "FileName: "
4 ------- - - - - - - - - - - - - - - - -
If: not (fn co ".")
Then: errorbox "Please include extention in fileName";pushkey fn{"[prtc];fn="";goto top
5 ------- - - - - - - - - - - - - - - - -
start If:            'change backslash to forward slash for UNIX
Then: fm=getenv("PFDATA"){"\ "{fn{""
6 ------- - - - - - - - - - - - - - - - -
If: ra eq ""
Then: msgbox "FILE CREATED will be on Drive"<getenv("PFDATA")&"\n with filename:"<fn
7 ------- - - - - - - - - - - - - - - - -
If: (1&3&4&5) eq ""     'this is to be sure blank records are not exported
Then: end
8 ------- - - - - - - - - - - - - - - - -
next If:
Then: export ascii mail=(fm) r=\n f=, o=" c="
9 ------- - - - - - - - - - - - - - - - -
If: ra eq ""
Then: gosub sendhea;ra(1,,g)="Y" goto next
10 ------- - - - - - - - - - - - - - - - -
If:     'assign the data in the same order that the headings
Then: mail(1)=5;mail(2)=3<4;mail(3)=1;mail(4)=40
11 ------- - - - - - - - - - - - - - - - -
If:
Then: mail(5)=35;mail(6)=36;mail(7)=6
12 ------- - - - - - - - - - - - - - - - -
If:
Then: mail(8)=7;mail(9)=mid(8,"1","2");mail(10)=""{mid(8,"3","12")
13 ------- - - - - - - - - - - - - - - - -
If:
Then: write mail; end
14 ------- - - - - - - - - - - - - - - - -
sendhea If:
Then: mail(1)="Last Name";mail(2)="First Name";mail(3)="Student ID";mail(4)="Email Address"
15 ------- - - - - - - - - - - - - - - - -
If:
Then: mail(5)="Home Phone";mail(6)="Work Phone";mail(7)="Address"
16 ------- - - - - - - - - - - - - - - - -
If:
Then: mail(8)="City";mail(9)="State";mail(10)="Zip"
17 ------- - - - - - - - - - - - - - - - -
If:
Then: write mail; return
18 ------- - - - - - - - - - - - - - - - -

Written by Nancy Palmquist