Tip #22 - Importing Data to filePro

palmtree with sun behind it

TIP #22 - Importing a Data file from ASCII to filePro

Moving data from one software’s data structure to another is very common these days. When you need to move data from some other software to filePro, we call that importing data. You will have to prepare for this data transfer with the following steps:

Develop or know where the data will be put in your filePro database. You will need to know filenames and field numbers that should be loaded with this new data.

Find out what kind of file will be sent. Two kinds of files are possible:

Fixed length files: If the file has fixed length records and fields, it does not need delimiters. This is the easiest type of data file to use. It is structured exactly like filePro and can be dealt with under the non-filePro file structure. Then all the processing to load the data is exactly like you would use when reading a filePro file. You can use the lookup command and run a output process that will move the data to the correct filePro data files. We will not consider this type of data file for this lesson.

Sequential files with variable lengths: You need to know the field and record delimiters and if the data is enclosed with any special characters. Common data exchange is done with cvs files. That would mean that the field delimiter is a comma, the record delimiter is a new line and the data is enclosed with quotes. A sample of this type of file might 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"

It is also common for the field delimiters to be tabs. That is easy to work with also.

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

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

(This is difficult to show here, but the data will look like it is in columns in an editor, when TAB is used as a field delimiter.)

You might want to get a sample file to look at with any editor you have available and see what it looks like. A hex editor will actually show you if the data will have any invisible characters, like tabs, CR/LF (carriage return/line feed) or the like.

Refer to tip 15 for an explanation of the various types of data files.

Now that you have the data destination and data source defined, you can get the import function started.

The logic for importing is a little different than that of an export, so I would like to explain it a bit. When you export, the data exists in filePro so an output function will select, sort and then output it as it does any output from filePro. However, when you are doing a import of data, the data does not usually exist in filePro, so you will have to create the data records during the import. In order to do this, you need somewhere in filePro to sit and process the data for the import. I usually use record 1 in the file that is to be loaded to do this function. So the next thing to do is to go to Inquire Update Add and add one record to the new filePro file and record it.

Then make a selection set, while you are in IUA (Inquire/Update/Add). From the IUA menu, press 2 - Scan Records, 2 - Extended Selection, U - Update. Under field type @rn, under rel type eq and under Value type 1, press ESC to save and the S - Save, select new and press ENTER and type "one". Now you should have an extended selection called "one" that will select record one only.

When the output processing is executed, it will stay on record one the entire time and loop around and around until all the data is loaded and written.

In the processing the flow goes as follows:

read a record
load the data fields in record one
create a new record at the end of the file
copy the information on record one to the new record
clear the data from record one
then repeat from the top until you hit the end of the file.

I like to use a record in the file where the data will be written, because the edits will be applied when I write the data into the fields on record one. It is also possible to use dummy variables for this and not change the record one at all.

Also, if this file is to receive data more than once it might be helpful to have a permanent place to process this import that can be excluded from other functions. This could also be done with a qualifier, but that adds a level of complexity that I am trying to avoid at this time.

Source Filename

DOS/NETWORK: The file is read from the current directory. Use a full pathname to be sure of the source 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 read from /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: import 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

\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

Mar 30, 1998 22:00 File Name: customer Page 1
Processing: import
1 ------ - - - - - - - - - - -
If:
Then: dim fields(30):1
2 ------- - - - - - - - - - - - - - - - -
each If:
Then: import ascii name=namelist r=\n f=, o=" c="
3 ------- - - - - - - - - - - - - - - - -
If: not name
Then: end
4-------- - - - - - - - - - - - - - - - - - - - - -
If:
Then: 1=name(1)
5 ------- - - - - - - - - - - - - - - - -
If: ‘Billing address
Then: 25=name(2);26=name(3);27=name(4);28=name(5);29=name(6);30=name(7)
6 ------- - - - - - - - - - - - - - - - -
If: 25 eq "" and 26 eq "" and 30 eq "" ‘Shipping address
Then: 2=name(2);3=name(3);4=name(4);5=name(5);6=name(6);7=name(7)
7 ------- - - - - - - - - - - - - - - - -
If:
Then: lookup cust=customer r=free
8 ------- - - - - - - - - - - - - - - - -
If:
Then: show "Loading"<25;copy cust; write cust
9 ------- - - - - - - - - - - - - - - - -
If:
Then: clear fields; goto each

The next step is to run the processing you have created. It will be necessary to create an output processing only type output. From the main menu select "3" to Define Output, enter the filename: "customer", then enter the format name "import", select the "5" - A processing-only format. The next screen allows you to assign a password, do so as required, then press ESC. A sort screen appears and is not required for this type of function so press ESC again. That is all that is required to make the output format that will run your program.

Now you will need to execute this report to load the data. Make sure the file called "namelist.wp" is in the correct directory and has read permission for filePro.

NOTE: Unix systems and Network systems have security features that can make files impossible to see or read. Using the locations that are expected by filePro will help with that type of security. Be sure filePro owns the files on a Unix system for best results. Also be sure filePro can read the file from the directory you will be using.

LOADING THE DATA

If this will only be done once, it is not necessary to make a menu line. Just select D - Request Output from the main filePro menu. Enter the filename "customer", then the format name "import", then press ENTER to skip the index question, Press ESC to skip the sort screen, "N" - Do not select all records, L - Load, highlight "one" and press ENTER. Then press ENTER to select. You should see the names of the customer displayed as they are loaded.

To add a menu line that can be repeated just open a user menu for changes and type:

/fp/dreport customer -f import -s one

That will do it. Remember it is using record one as a loading record. Do not store any real data on record one or it will be erased. Also, be sure record one is a recorded record, or nothing will happen.

This tip posted on 05/02/2007 by Nancy Palmquist. Your comments and suggestions are gratefully accepted. If no one is reading this, I will direct my efforts elsewhere, so let me know if you are finding this helpful. I will be looking for topics that can be covered in a page or two to use for future tips.