Manually Importing Data into a VFE Database

In most applications there is a need to import initial data or test data into an existing blank database structure. With VFE, this is complicated by the need to have VFE default keys produced either during the import or pre-existing in the imported data.

There are, of course, many ways to get data into your VFE database. If your users will need to import data themselves or if the import requires significant user review and cleaning, you may need to write an import routine in your application. If importing the data is a one-shot deal or requires significant pre-processing or programmatic validation and cleaning which you don't want to handle in your application, you'll probably want to import the data outside of VFE.

The basic steps I use for converting and importing data for manual importing into a VFE database are:

1. Write SQL-Select statements pulling from the source data into cursors that match the new table or view structures.

2. Copy the resultant cursors to import tables, storing them in a temporary database (this preserves long field names).

3. Validate the data.

4. Append the import tables into your final tables.

I strongly recommend writing all of the conversion code in a PRG file. You will need to re-run all or part of the code and you will want documentation for what you have done. I write the code out, run chucks of the code at a time, debugging and saving as I go.

 

VFENewKey

The first difficulty in creating VFE-compatible data is that under normal circumstances the VFE IDE or a VFE application needs to be running before you can create default keys with GUID() or VFENewKey(). To solve this problem I modified VFE's keygen.prg procedural file to allow the generation of VFE default keys outside of VFE. This new procedural file, VFEKeyGen.prg, can be downloaded from www.PrimaryDataSolutions.com\downloads.htm.

To use VFEKeyGen, simply SET PROCEDURE TO VFEKeyGen.prg. When you make a call to VFENewkey(), the program will attempt to find the proper appids.dbf, coremeta.dbf and vfemeta.dbf files. If appids.dbf is in the same directory as the table receiving the append, it will find it automatically. If vfemeta.dbf and coremeta.dbf are in the normal METADATA folder in relation to the DATA directory, they will be found automatically as well.

Manually open the appropriate appids.dbf, coremeta.dbf and vfemeta.dbf tables first if they are not in the normal locations or you are creating keys in something other than your VFE tables. If you don't first open the files, you will be prompted to locate the files for each key generated. VFENewKey() also requires that you open the table or view specified in the passed parameter.

Also do not forget to make sure all of your tables have the proper default value generation assigned. If you are importing into tables, make sure you have default value properties set for the tables, even if you only need them on the views in your VFE application. The default value properties must exist in your metadata for VFENewKey() to properly generate keys.

 

Foreign Keys

The next complication is properly populating foreign key values. Since the corresponding primary key must exist before populating a foreign key, one must first create lookup tables, then parent tables and finally child and grandchild tables. This order of table generation will usually guarantee that the primary key exist prior to its need as a foreign key.

 

Example

This conversion is for a re-write of one of the first applications for which I was actually paid. Feel free to laugh at my original data structures, I do. But hey, they're been happily using the application since 1993. The good thing for this paper is that there are examples of all sorts of old, bad data structures that need to be converted.

This application processes results from telemarketing calling. Calls are made a UNIX-based "auto-dialer." The completed call records and various statistics files are offloaded from the auto-dialer and then imported and processed by this application. This application reports on the results, prepares pledge records for a Mailhouse for fulfillment processing and sends operator information to a payroll program for automated time-sheet preparation.

 


*************
*** Setup *** 
*************


Set Procedural file to allow generation of VFE default keys.

SET PROCEDURE TO c:\vfe6\vfekeygen.prg


The deleted flag does not carry over in SQL selects. If you need to import deleted records, create a ldeleted flag (DELETED() AS ldeleted) in the cursor. Once you create you temporary import table you can issue a DELETE FOR ldeleted. However, you can not reliably populate a ldeleted flag in a JOIN, so you will need to create a cursor with the deleted flag prior to any JOINs. I don't need the deleted records, so...

SET DELETED ON


Open Appids, CoreMeta and VFEMeta manually.

USE c:\vfe6\dev\campaignmanager\data\appids.dbf IN 0 SHARED
USE c:\vfe6\dev\campaignmanager\metadata\vfemeta.dbf IN 0 SHARED
USE c:\vfe6\dev\campaignmanager\metadata\coremeta.dbf IN 0 SHARED


Create and open temporary database to store import tables

CREATE DATABASE c:\temp\campimport\campimport.dbf
SET DATABASE TO campimport


***********************
***********************
****Lookup tables first
***********************
***********************


******************
****SeniorTCRs.DBF
****************

This lookup table did not exist in the old data. Previously, the manager's initials were stores in the main table. The new structure calls for a Lookup table with more information.

I need to create a structure with the PK and the initials. The full name information will be manually keyed later.
If the full name fields are required, I would have someone key them into a temporary table prior to importing them into the final table. In this case, they are not required fields in my VFE app.


***Open the VFE table since I need VFENewKey()
USE c:\vfe6\dev\CampaignManager\data\SeniorTCRs.dbf in 0 
  
****Pull unique values first
SELECT UPPER(PADR(SUBSTR(c11,2),3)) as cinitial; &&Make sure field lengths and type are correct
	FROM MLLIST;
	GROUP BY 1;
   	INTO CURSOR seniortcr1


Add PK values into proper structure for import. Note the parameter passed to VFEnewKey() is the final table and field name. Make sure this matches the default value setting for the final table.

SELECT vfenewkey('seniortcrs.cpk_seniortcrs') as cpk_seniortcrs,;  &&Base62 type is padded automatically
	cinitial;
	FROM seniortcr1;
	INTO CURSOR seniortcr2


Look at the Data!!! Make Sure it's correct.

***Copy cursor into table in database will preserve long field names
COPY TO c:\temp\campimport\iSeniortcrs.dbf DATABASE campimport

***Open the final table, since I'll need it later
USE c:\temp\campimport\iSeniortcrs.dbf IN seniortcr1

USE in seniortcr2

 

******************
****Lists.DBF
******************

This table did not exist at all in the old data structure. The old data will all link to an "unknown" record, so all we have to do is open the table, append one record and fill in unknown for the description. I'll hard-code the one PK in the SQL statement when needed.

SELECT 0
USE c:\vfe6\dev\campaignmanager\data\lists.dbf
APPEND BLANK &&The PK will be automatically generated.
replace cdescription WITH 'Unknown-Old Data'
lcListUnknownpk=lists.cpk_lists  &&Store pk for use as FK later.
USE IN lists


******************
****Shifts.DBF
******************

This LU table did not exist at all in the old structure. Instead of shift 1,2,3, It'll now be morning, afternoon, evening. I'll add records manually and hard-code the FKs where necessary.

SELECT 0
USE c:\vfe6\dev\campaignmanager\data\shifts.dbf
APPEND BLANK &&The PK will be automatically generated.
replace cshift WITH 'Morning'  &&PK is 01
APPEND BLANK &&The PK will be automatically generated.
replace cshift WITH 'Afternoon' &&PK is 02
APPEND BLANK &&The PK will be automatically generated.
replace cshift WITH 'Evening' &&PK is 03
USE IN  shifts


******************
****Operators.DBF
******************

This table is actually created and updated automatically by a payroll program written in VFE.. The computer with the new program cannot access the payroll data, so the code to create this
file already exists in another VFE application. I just need to open the file to get the PK values.

USE c:\vfe6\dev\campaignmanager\data\Operators.dbf




******************
****Locations.DBF
******************

This LU table did not exist at all in the old structure. For this import, all have the same value. I'll add records to the final table manually and hard-code the FKs where needed.

SELECT 0
USE c:\vfe6\dev\campaignmanager\data\locations.dbf
APPEND BLANK
REPLACE clocation WITH 'Virginia Beach' &&PK=01
APPEND BLANK
REPLACE clocation WITH 'Newport News' &&PK=02

USE IN locations



******************
****CampaignTypes.DBF
******************

This table did not exist in the old data structures. It is basically a metadata table defining the structure of a code structure for a particular client. This table will need to be manually keyed, after which I will programmatically validate it against existing codes and populate FKs.



******************
****Campaigns.DBF
******************

This is a main parent table. I am joining on the SenoirTCR.dbf lookup table I created above on the actual display value in order to populate the foreign key. The PK of this table is being populated with GUID(). Numeric fields are padded out by adding 0s in the right length and format. I also used VFEGenview on the VFE table in order to cut-and-pasted the field list for all of the "as"s.

SELECT 	guid() as cpk_campaigns,;  &&Pk value
	PADR(LEFT(c2,AT('-',c2)-1),10) as ccampaign_code,; &&This was previously a compound key of code and starttime I'm not splitting out
	CTOT(DTOC(c1)+' '+SUBSTR(c2,AT('-',c2)+1)) as tstart_time,; &&Combining old data and time fields into datatime field
	CTOT(DTOC(c1)+' '+c5) as tend_time,; &&Combining old data and time fields into datetime field
	000000+c6 as ncalls,;  &&00000+ pads numeric field to proper length
	000000+c7 as nhang_ups,;
	000000+c9 as nmaybes,;
	000000+c8 as nyeses,;
	00000000.00+c10 as ypledges,;
	000000+nohelp as nno_help,;
	seniortcr2.cpk_seniortcrs as cfk_seniortcrs,; &&grabbing PK of LU for FK 
	IIF(c3='1','01',IIF(c3='2','02','03')) as cfk_shifts,; &&hard-coding PK conversion
	'   ' as cfk_campaigntypes,;  &&Populating this will take manual processing later
	'01' as cfk_location,; &&This I'm hardcoding
	c1, c2; &&Retain former keys in temporary table for later JOINS
	FROM mllist JOIN seniortcr2 ;  &&joining LU table on old display value to get FK
		ON UPPER(SUBSTR(mllist.c11,2))==seniortcr2.cinitial;
	WHERE c1>={1/1/2000};	&&&Limiting data to more recent for testing
	INTO CURSOR ccampaigns

Look at the Data!!! Make Sure it's correct.


***Create table in database
COPY TO c:\temp\campimport\icampaigns.dbf DATABASE campimport

******************
****OperatorShifts.DBF
******************

In the old data structure a new file was created for each day to hold this data. The new data structure will have one file. the first step is to combine the older files into one table.

LOCAL ladayfiles[1], lcntr

SET DEFAULT TO d:\dans\nrtw\cchdata

***Get array of files.  They are named with the data, so they're easy to identify
=ADIR(ladayfiles,'??-??-??.dbf')

**Create blank file structure
SELECT 0
USE (ladayfiles[1,1])
COPY STRUCTURE TO c:\temp\campimport\alldayfiles.dbf
USE c:\temp\campimport\alldayfiles.dbf excl

***The old files didn't need a date field.  We do.
ALTER TABLE alldayfiles ADD COLUMN ddate D

**To speed up replace
INDEX ON ddate TAG ddate

**Loop through array and import files
FOR lcntr=1 TO ALEN(ladayfiles,1)
	APPEND FROM (ladayfiles[lcntr,1])
	**Fill in the date which is the old file name 
	replace ddate WITH CTOD(LEFT(ladayfiles[lcntr,1],8)) FOR EMPTY(ddate)
endfor	


Now we have one file from which we can create our correct structure. I'm joining on the parent table on the old key in order to populate the foreign key of the parent.

SELECT 	UPPER(SUBSTR(oper,2)) as coperator,;  &&Unfortunately we can't use a FK here in the final data
	0000.00+ log as nlog,;  &&pad all numeric fields to proper length
	0000.00+ work as nwork,;
	0000.00+ pause as npause,;
	0000+fcalls as nfront_calls,;
	0000+fmsg as nfront_messages,;
	0000+ftob as nfront_to_back,;
	0000+fyes as nfront_yesses, ;
	0000+fmbe as nfront_maybes,;
	0000000.00+ fpledge as yfront_pledges, ;
	0000+bcalls as nback_calls,;
	0000.00+ btalk as nback_talk_time, ;
	0000+byes as nback_yeses,;
	0000+bmbe as nback_maybes, ;
	0000000.00+ bpledge as yback_pledges,;
	0000.00+ bonus as ybonus, ;
	empnum as cempnum,;
	000.00+ ftalk as nfront_talk,;
	guid() as cpk_operators,;  &&Create new Primary Key
	icampaigns.cpk_campaigns as cfk_campaigns;  &&Grab PK from campaign table for FK
	FROM alldayfiles JOIN icampaigns on;
		alldayfiles.c2 == icampaigns.c2 and;  &&Join on former compound keys
		alldayfiles.ddate = icampaigns.c1;
	INTO CURSOR cOperators

Look at the Data!!! Make Sure it's correct.

	COPY TO c:\temp\campimport\ioperators.dbf DATABASE campimport	



******************
****Calls.DBF
******************

Due to former space limitations, these records were not maintained by the old application. For the purposes of testing, I need to import some of this data that exist in flat files. The fastest method to convert this data would be to import the flat files into a matching DBF structure, which I would then convert to my new structure. However, since I will need an import routine in my application for importing these files as campaigns are imported, I will write the code now and thus be able to test it with a large data set.

**First create temporary table to import into from VE table structure.
SELECT 0
USE c:\vfe6\dev\campaignmamanger\data\calls.dbf
COPY STRUCTURE TO c:\temp\campimport\icalls.dbf
USE c:\temp\campimport\icalls.dbf excl



**All files in this directory are flat files that can be imported
DIMENSION lakermits[1]
ADIR(lakermits,'j:\temp\cchdata\kermits\*.*')

SET EXACT off

FOR lcntr=1 TO ALEN(lakermits,1)
	lcSDFCallFile=ALLTRIM(UPPER(lakermits[lcntr,1]))
	ldcalldate=lakermits[lcntr,3]
  	lccampcode=SUBSTR(STRTRAN(lcSDFCallFile,'.',''),2,LEN(STRTRAN(lcSDFCallFile,'.',''))-5)
	SELECT icampaigns
	LOCATE FOR ccampaign_code=lccampcode AND TTOD(tstart_time)=ldcalldate
	IF FOUND('icampaigns')
		lccamppk=icampaigns.cpk_campaigns    &&The FK to the campaign is determined by the campaign code
	ELSE                                         &&which is part of the file name
  		lccamppk=''
	endif		

	lnfilehandle=FOPEN((lcSDFCallFile))
	?lnfilehandle
	IF lnfilehandle>=0  &&opened properly
		=FSEEK(lnfilehandle,0,0)  &&make sure at beginning of file
		DO WHILE !FEOF(lnfilehandle)
			lcnextline=FGETS(lnfilehandle,500)  &&Grab line to CRLF
			IF !EMPTY(lcnextline)
				INSERT INTO icalls;  &&I copied these fields from a VFEGenView code window
					(CPK_CALLS, YPLEDGE, CNAME, CPHONE, ;
					CFILL, CFAX, CCOMPANY,;
					CSTREET, CCITY, CSTATE,;
					CZIP,CSALUTAION, CFLAG,;
					CMAILCODE, CRELREC,CDISTRICT,;
					CSTATE_SENATE, CSTATE_HOUSE, cfill2,;
					CCOMMENT1, CCOMMENT2, CFRONTER,;
					CBACKER, CSIC_CODE, CFILLER3, ;
					CFK_CAMPAIGNS, campcode, calldate);
				VALUES;
					(GUID(),VAL(left(lcnextline,9))/100,SUBSTR(lcnextline,10,35),SUBSTR(lcnextline,45,10),;
					SUBSTR(lcnextline,65,10),SUBSTR(lcnextline,55,10),SUBSTR(lcnextline,75,35),;
					SUBSTR(lcnextline,110,30),SUBSTR(lcnextline,140,20),SUBSTR(lcnextline,160,2),;
					SUBSTR(lcnextline,162,10),SUBSTR(lcnextline,172,25),SUBSTR(lcnextline,197,1),;
					SUBSTR(lcnextline,199,6),SUBSTR(lcnextline,205,15),SUBSTR(lcnextline,220,4),;
					SUBSTR(lcnextline,224,7),SUBSTR(lcnextline,231,7),SUBSTR(lcnextline,238,6),;
					SUBSTR(lcnextline,263,100),SUBSTR(lcnextline,363,41),SUBSTR(lcnextline,404,4),;
					SUBSTR(lcnextline,408,4),SUBSTR(lcnextline,234,4),SUBSTR(lcnextline,412,55),;
					lccamppk,lccampcode,ldcalldate)

			ENDIF
		ENDDO
		=FCLOSE(lnfilehandle) &&close file
	ENDIF
endfor

Look at the Data!!! Make Sure it's correct.

 

******************
****Mailcodes.DBF
******************

This is a Lookup table of codes in call records, used to identify the source list of original call record. It will not be using a surrogate key to match to the call record, since it is not required that this code be in the lookup table. The users can, if they desire, records more information by mailcode in the lookup table, but it is not necessary.

SELECT cmailcode ;
	FROM icalls ;
	GROUP BY 1 ;
	INTO CURSOR tempmailcode

SELECT 	GUID() as cpk_mailcodes,;
		UPPER(cmailcode) as cmailcode,;
		lcListUnknownpk as cfk_lists,; &&PK for "Unknown List"
		SPACE(100) as cdescription;
	FROM tempmailcode;
	INTO CURSOR tempmailcode2

Look at the Data!!! Make Sure it's correct.

COPY TO c:\temp\campimport\imailcode.dbf DATABASE campimport	


The final step is simply to append each of the temporary files into the final table.

SELECT 0
USE c:\vfe6\dev\campaignmanager\data\calls.dbf 
APPEND FROM c:\temp\campimport\icalls.dbf
...etc.


All Done!

--Dan Goodwin
Primary Data Solutions

First Published: 11/14/2001