Using Phonetic Matching To Move Excel Data Into A Visual Foxpro Database

Using Phonetic Matching to move Excel Data into a Database

Abstract String and Name matching applications can provide powerful capabilities for easily identifying records belonging to specific individuals. This can prove very useful in moving legacy data such as in Microsoft Excel Spreadsheets into an existing database. In Visual FoxPro, you could use either the SOUNDEX() or the DIFFERENCE function for this purpose. This article demonstrates a brief application of the DIFFERENCE command and its use in matching names to retrieve account IDs from a Visual FoxPro Table. It also demonstrates how data previously stored in an Excel spreadsheet is moved into tables in a Visual FoxPro Database. Introduction Many situations can occur in real-life applications that will require string matching. For examples, you may know the name of a person but not the unique Account ID of the person. Making an exact match of the first name or any other names for that matter may not work especially in countries where there is no standard name spelling! For example, in Ethiopia, the same name could be spelt in three different ways. Take the following examples: Helena and Helina In this situation, you can use string matching to find out if names are phonetically similar so that you could then use decide is you had found the right person. Using Visual FoxPro’s DIFFRERENCE command, you can determine if two words sound the same. This function powerfully rescued us in just one such situation with consistently 98% of above accuracy! In our integrated Schools Management System application, End-of-Year master marks lists prepared in Excel needed to be imported into the Marks list table. These master Marks list did not contain column for Student IDs. A sample of the master marks list is shown later under the section Our Database later in this article. This meant that the only way to match a mark entry on the spreadsheet to the correct student record in the system is by matching the names. It was not practical to make the teachers update the spreadsheet because of the sheer number of homerooms and number of students involved. Moreover, because most of the teachers knew Ms Excel and had already prepared their roasters using Excel, this presented the most effective way to get the data directly from the Excel spreadsheet into FoxPro database. So how will this work; step-by-step? Lets see how:

1. Step 1: You will need to declare and initialize variables that you will be using in your code. We declared the following variables:

* Declare Variables used LOCAL oEx AS Object,cSex as Character,intTerm as Integer,intAge as Integer,cBatchCode as Character LOCAL cMsg as Character,ws AS Object,intCols AS Integer LOCAL intRows as Integer ,cFile AS Character,intCnt as Integer LOCAL intStudsInClass as Integer,cCurrName as Character LOCAL nScore ,nExamTotal ,nExamAvg lOCAL intRankInClass as Integer,cBehaviour as Character,intDaysAbs as Integer,cDropOut as Character,cPromoted as Character LOCAL cDetained as Character LOCAL cRC as Character LOCAL intRecNumb as Integer LOCAL cFirstName AS Character LOCAL cLastName as Character LOCAL cStudName as Character LOCAL intNoOfStuds as Integer LOCAL cRFirstName as character LOCAL cRMiddleName as Character LOCAL intFirstRank as Integer LOCAL intSecRank as Integer LOCAL cCourseName as Character LOCAL cParamCode as Character LOCAL nParamVal1 as Number LOCAL cParamVal2 as Character LOCAL cBatchCode as Character LOCAL nTotPossMarks as Number LOCAL lCurricular as Logical LOCAL cStudCode as Character SET EXACT ON DIMENSION arrSubjs(22,1) STORE 0 TO intRows,intCols,intCnt,intStudsInClass,intTerm,intAge,intRecNumb,nScore ,nExamTotal,nExamAvg,intRankInClass,intDays STORE 0 TO intFirstRank,intSecRank,nParamVal1,nTotPossMarks STORE "" TO cFile,cMsg,cCurrName,cSex,cBatchCode,cRC,cDropOut,cPromoted,cDetained,c Behaviour,cFirstName,cMiddleName,cStudName STORE "" TO cFirstName,cLastName,cRFirstName,cRMiddleName,cBatchCode,cParamVal2,cSt udCode

These variables are used to hold data obtained from the Excel spreadsheet so that these can be evaluated prior to conversion. The variable oEX is used to hold the reference to the Microsoft Excel object,

cFileis used to obtain the name, path and location of the Microsoft Excel file to be processed. The Variable intRows represents the total number of rows in the excel spreadsheet while intCols represents the total number of columns to be processed. Another most important variable here is the array variable arrSubjs declared with the DIMENSION command. This will be used to hold subject header detail read back from the spread sheet. This will then be later appended from to the cursor TSubjs using the APPEND FROM ARRAY command. Of course, you will naturally have to create an interface so that users can enter the academic year to be processed and the grace and class to be processed. Our interface looks something like fig 1.

Fig 1: the End of Term Report form that users may use to enter the data they want. The Grade to be processed is entered in the starting grade (spnStartGrade) field. The specific section in the Class field (txtClass). The academic year is entered in the Academic Year fields (txtAcdStartYear and txtAcdEndYear) and the user simply clicks the

import excel spreadsheet should look as in Figure 2:

button. The finished form when run

Fig 2. An example of the End of Term Report window when run with data entered for Grade 10C. For more information on the tables and fields being processed, see our Database later in this article 2. Step 2: Because the Microsoft Excel spreadsheet is already created and saved in the Microsoft Excel 2007-2003 format, we will need to open the file and create an Automation object from it using Visual FoxPro’s GetObject() Function as in the following line of code: * Obtain the file name and path to be processed...Excel Files only cFile = GETFILE("XLS","Enter Excel file to import","Excel Roaster") THISFORM.lblStatus.Caption = "*** Now obtaining headers from spreadsheet...Please Wait***" oEX = GETOBJECT(cFile)

In this case, the GETFILE() function displays the open file dialog box so that the user can select an Excel file. The GetObject() function opens the file and creates an Excel automation object from it. The reference to the object is stored to the object variables oEX! Now that we have an object variable to this line of code, we can set and obtain property values for the object just as we would with any other object. 3. Step 3: We need to determine the subjects that will be processed. This is important because different school years will take different subjects. The fifth row on the spreadsheet as shown contains the list of subjects. Since Students can be graded only for subjects that have already been defined in the schools management system database, we will need to read these back from the spreadsheet and then obtains the correct subject IDs from the Subjects table within the application system. Also, we will check the teacher’s timetable defined in the system to enable us obtain the name and ID of the teacher taking the subject for the defined school year. In the piece of code shown below, a FOR… ENDFOR loop is the principal and fastest loop that could have been used to iterate the cells of the Excel Spreadsheet. In each iteration through the loop, the array, arrSubjs is updated with the value returned from the Excel Spreadsheet. You can create a temporary table to hold the list of subjects by entering the following code: * Now Determine the subjects you will be processing and then read * them from the Excel Spreadsheet into the Array and then create a cursor with them FOR intCols = 7 TO 28 && These columns contain Column Numbers and Dates cMsg = oEX.ActiveSheet.Cells(4,intCols).Value IF RTRIM(UPPER(cMsg)) <> "T REMARK" *intCnt = intCnt = 1 *DIMENSION arrSubjs(intCnt,1)

arrSubjs(intCols - 6 + 1,1) = cMsg *arrSubjs(intCnt,1) = cMsg ENDIF ENDFOR * Create a cursor and put the subjects you found into them to make it easier to process CREATE CURSOR TSubjs (CourseCode c(20),TotPossMarks N(3,2),Curricular L,CourseName c(50),EmployeeCode c(15),EmployeeName c(50)) SELECT TSubjs APPEND FROM ARRAY arrSubjs USE sYSUBJECTS IN 0 USE AdmTeachSubj IN 0 SELECT TSubjs GO TOP SCAN SELECT SySubjects GO TOP LOCATE FOR ALLTRIM(UPPER(SySubjects.CourseName)) = ALLTRIM(UPPER(TSubjs.CourseCode)) IF FOUND() REPLACE TSubjs.CourseCode WITH SySubjects.CourseCode REPLACE TSubjs.CourseName WITH Sysubjects.CourseName REPLACE TSubjs.TotPossMarks WITH SySubjects.TotPossMarks REPLACE TSubjs.Curricular WITH SySubjects.Curricular ENDIF SELECT AdmTeachSubj GO TOP LOCATE FOR ALLTRIM(UPPER(AdmTeachSubj.CourseCode)) = ALLTRIM(UPPER(TSubjs.CourseCode)) ; AND AdmTeachSubj.Grade = THISFORM.spnStartGrade.Value ; AND AdmTeachSubj.Class = THISFORM.txtClass.Value ; AND AdmTeachSubj.CurrYear = THISFORM.txtAcdStartYear.Value AND AdmTeachSubj.NextYear = THISFORM.txtAcdEndYear.Value IF NOT FOUND() THISFORM.lblstatus.Caption = "*** Stop Error-Subject: " + RTRIM(TSubjs.CourseCode) + " has not been defined for Grade: "; + ALLTRIM(STR(THISFORM.spnStartGrade.Value)) + THISFORM.txtclass.Value *strMsg = "This subject has not been defined for this class!" *MESSAGEBOX(strMsg,MBINFO,chrProgTitle) *RETURN ELSE REPLACE TSubjs.EmployeeCode WITH AdmTeachSubj.EmployeeCode REPLACE TSubjs.EmployeeName WITH AdmTeachSubj.EmployeeName ENDIF SELECT TSubjs ENDSCAN USE IN SySubjects USE IN AdmTeachSubj

4. Step 4: Create a temporary cursor into which you will then read-back the records from the Excel spreadsheet. The versatile CREATE CURSOR statement achieves this because it allows you to define the exact fields to be contained in the temporary table as well as their attributes. Once you have created the temporary table, you can perform all types of operations with it such as APPEND (add new records), DELETE (remove existing records), LOCATE (Search for and match existing records) and so on. We have used a CREATE CURSOR instead of a CREATE TABLE because Visual FoxPro removes the temporary table once you have finished using it – beautiful is it not? This was created with the following commands: * Now Begin the Actual Process of reading the data from the array into the system * 1) Create a Cursor to hold the records you are reading back CREATE CURSOR TStudMarks(BatchCode c(20),StudCode c(20),StudName c(20),; Sex c(1),BirthDate D,Age I,Grade I,ClassCode c(1),AcdStartYear I,AcdEndYear I,Term I,; CourseCode c(10),CourseName c(25),Employeecode c(15),EmployeeName c(50),TotPossMarks N(3,2) DEFAULT 100,Curricular L,; ExamMark N(3,2) DEFAULT 0,ExamAvg N(3,2),Behaviour c(1),NoOfStuds I,RankInClass I DEFAULT 0,GradeCode c(2),; NoteCode c(2),PromStatus c(10),DaysAbs I DEFAULT 0)

5. Step 5: The second step is to create a temporary cursor that contains relevant records from the students master table already in the application database. Notice how the richness of the Microsoft Visual FoxPro Language and its tight coupling with data achieves this with a simple SELECT INTO CURSOR statement and the COUNT function elegantly returns the total number of students in the cursor. The total number of records returned must match the Grade, Class, and Academic Year that you entered in the data collection form. This will also enable us compute the total number of records to be processed. This was achieved with the following piece of code: * Compute the Total number if students in the class * We need a list of Student Names to use to parse cMsg = "SELECT Distinct StudentCode,FirstName,MiddleName,StudentName,BirthDate FROM AcdStudents WHERE AcdStudents.Grade = " + ALLTRIM(STR(THISFORM.spnStartGrade.Value)); + " AND AcdStudents.Class = '" + THISFORM.txtClass.Value + "' AND (AcdStudents.Status = 'ADMITTED' OR AcdStudents.Status = 'Active' OR AcdStudents.Status = 'SUSPENDED') INTO CURSOR TStuds" &cMsg

SELECT TStuds * Calculate the total number of students in the table * This helps us determine the total number of excel rows * that we shall have to loop through to read exam grade mark details COUNT TO intStudsInClass IF intStudsInClass <= 0 cMsg = "There are no students in this class!" MESSAGEBOX(cMsg,MBINFO,chrProgtitle) RETURN ENDIF intNoOfStuds = intStudsInClass intStudsInClass = intStudsInClass * 4 && For example, 4 Quarters in the master marks list for each student

6. Step 6: Read the records from the Excel Spreadsheet and update the TStudMarks Cursor created earlier. Once you have populated this table, you can update the Students master marks list table. This was achieved with the following piece of code: FOR intRows = 5 TO intStudsInClass && The total number of rows will equal total number of records you expect to process FOR intCols = 1 TO 35 && No of columns in the Worksheet DO CASE CASE intCols = 1 && This is the S/No column...we are not interested LOOP CASE intCols = 2 && This is the name column...if the name has changed then we are processing for a new person...make sure of this cMsg = oEX.ActiveSheet.Cells(intRows,2).Value && We assume that Column 2 is always the name IF ISNULL(cMsg) LOOP ENDIF THISFORM.lblStatus.Caption = "Now processing records for " + cMsg IF NOT ISBLANK(cMsg) && Row is not blank IF ALLTRIM(UPPER(cMsg)) <> ALLTRIM(UPPER(cCurrName)) * we are procerssing for someone new...get the name so that we can update the database with it cCurrName = cMsg cSex = oEx.ActiveSheet.Cells(intRows,3).Value && We assume that Column 3 is always sex on the spreadsheet IF UPPER(ALLTRIM(cSex)) = "SEX" && This must be a header row intCols = 35 LOOP ENDIF

intTerm = oEx.ActiveSheet.Cells(intRows,5).Value && We assume that column 4 is Age and 5 is always the quarter nTotal = oEX.ActiveSheet.Cells(intRows,28).Value && Total Column is 28 nExamAvg = oEX.Activesheet.Cells(intRows,29).Value && Exam Avg is 29 intRankInClass = oEx.ActiveSheet.Cells(intRows,30).Value && Student's Rank is column 30 on the Excel Spreadsheet cBehaviour = oEx.ActiveSheet.Cells(intRows,31).Value intDaysAbs = oEx.ActiveSheet.Cells(intRows,32).value cPromoted = oEx.Activesheet.Cells(intRows,33).Value cDetained = oEX.Activesheet.Cells(intRows,34).Value cDropOut = oEx.ActiveSheet.Cells(intRows,35).Value * We are not interested in the Yearly Averages IF TYPE('intTerm') = 'C' IF ALLTRIM(UPPER(intTerm)) = "AV." && This is the Av eraging row...ignore it LOOP ENDIF IF ALLTRIM(UPPER(intTerm)) = "QUARTER" && This is a page Header Row intCols = 35 LOOP ENDIF LOOP ENDIF && IF intTerm ENDIF && If Alltrim(Upper(cMsg)) LOOP && Go back to to so we can increment to next col ENDIF CASE intCols > 5 && We are beginning to process the actual subject data * 1) We need to obtain the subject name from the Subject cursor... * Since the subjects were stored according to Column number order, this should be easy * to enable us do this, we have to determine the record number in the TSubjs cursor. * this is important because when we read back the column headers, we did not * discriminate between a subject Header column and a T-ReMark Column in which * case inside the cursor, Record one, contains a valid Subject Header 'Amharic' * but record 2 contains T Remark (not a subject but the teacher's remark on the * students performance on the Amharic Subject), Record 3 Contains 'English', a

* valid subject header and record 4 again contains T-Remark and so on. * Please note that the approach below is done to help you understand how the * record numbers are computed. but a better way to compute the position of * a valid subject header in the table is the formular used below the SELECT TSubjs * command but commented out SELECT TSubjs IF intCols % 2 = 0 * This is an even number...Add 1 to it to obtain the record number intRecNumb = (intCols % 6) + 1 ELSE intRecNumb = (intCols % 6) ENDIF GO intRecNumb record

creating

&& Move to the specified

cCourseCode = TSubjs.CourseCode cCourseName = TSubjs.CourseName cEmployeeCode = TSubjs.EmployeeCode cEmployeeName = TSubjs.EmployeeName nTotPossMarks = TSubjs.TotPossMarks lCurricular = TSubjs.Curricular IF TYPE('cCoursecode') = "L" IF cCourseCode = .F. * This is not a sibject LOOP ENDIF ENDIF IF intCols >= 6 AND intCols <= 28 * obtain the Term for the record you are

intTerm = oEx.ActiveSheet.Cells(intRows,6).Value && We assume that column 4 is Age and 5 is always the quarter IF TYPE('intTerm') = 'C' IF ALLTRIM(UPPER(intTerm)) = "AV." && This is the Av eraging row...ignore it LOOP ENDIF IF ALLTRIM(UPPER(intTerm)) = "QUARTER" && This is a page Header Row intCols = 35 LOOP ENDIF LOOP ENDIF && IF intTerm information for that subject

* 2) Now Obtain Score and subject

nScore = oEX.ActiveSheet.Cells(intRows,intCols).Value * 3) Now obtain the remark code

intCols = intCols + 1 cRC = oEX.Activesheet.Cells(intRows,intCols).Value intRankInClass = oEx.ActiveSheet.Cells(intRows,30).Value IF ISNULL(intRankInClass) intRankInclass = 0 ENDIF intDaysAbs = oEx.ActiveSheet.Cells(intRows,31).value IF ISNULL(intDaysAbs) intDaysAbs = 0 ENDIF ENDIF IF intCols > 28 && This is a totls column, we must go to LOOP && next row to read info for another term/quarter ENDIF IF intCols = 28 && this is the total column *nTotal = oEX.ActiveSheet.Cells(intRows,28) ENDIF IF intCols = 29 && This is the students exam average *nExamAvg = oEX.Activesheet.Cells(intRows,29) ENDIF IF intCols = 30 && Rank in Class *intRankInClass = oEx.ActiveSheet.Cells(intRows,30) ENDIF IF intCols = 31 && Conduct *cBehaviour = oEx.ActiveSheet.Cells(intRows,31).Value ENDIF IF intCols = 32 && Number of days absent this quarter *intDaysAbs = oEx.ActiveSheet.Cells(intRows,32).value ENDIF IF intCols = 33 && Promoted Clumn *cPromoted = oEx.Activesheet.Cells(intRows,33).Value ENDIF IF intCols = 34 && Detained Column *cDetained = oEX.Activesheet.Cells(intRows,34).Value ENDIF IF intCols = 35 && This is the Drop Out Column *cDropOut = oEx.ActiveSheet.Cells(intRows,35).Value ENDIF * We will now append the record to the TStudMarks Cursor SELECT TStudMarks APPEND BLANK

cBatchCode = "NEW" + ALLTRIM(STR(RECCOUNT())) REPLACE TStudMarks.BatchCode WITH cBatchcode REPLACE TStudMarks.StudCode WITH cStudCode REPLACE TStudMarks.Studname WITH cCurrName IF UPPER(ALLTRIM(cSex)) = "F" REPLACE TStudMarks.Sex WITH "FEMALE" ELSE REPLACE TStudMarks.Sex WITH "MALE" ENDIF REPLACE TStudMarks.Age WITH intAge REPLACE TStudMarks.Grade WITH THISFORM.spnStartGrade.Value REPLACE TStudMarks.ClassCode WITH THISFORM.txtClass.Value REPLACE TStudMarks.AcdStartYear WITH THISFORM.txtAcdStartYear.Value REPLACE TStudMarks.AcdEndYear WITH THISFORM.txtAcdEndYear.Value REPLACE TStudMarks.Term WITH intTerm REPLACE TStudMarks.CourseCode WITH cCourseCode REPLACE TStudMarks.CourseName WITH cCourseName REPLACE TStudMarks.Employeecode WITH cEmployeeCode REPLACE TStudMarks.EmployeeName WITH cEmployeeName REPLACE TStudMarks.TotPossMarks WITH nTotPossMarks REPLACE TStudMarks.Curricular WITH lCurricular REPLACE TStudMarks.ExamMark WITH nScore REPLACE TStudMarks.ExamAvg WITH nExamAvg REPLACE TStudMarks.Behaviour WITH cBehaviour REPLACE TStudMarks.NoOfStuds WITH intNoOfStuds REPLACE TStudMarks.RankInClass WITH intRankInClass REPLACE TStudMArks.DaysAbs WITH intDaysAbs REPLACE TStudMarks.GradeCode WITH cGradeCode REPLACE TStudMarks.NoteCode WITH cRC IF cPromoted = "Y" REPLACE TStudMArks.PromStatus WITH "PROMOTED" ELSE REPLACE TStudMArks.PromStatus WITH "REPEATED" ENDIF

*

ENDCASE

ENDFOR

ENDFOR SELECT TStudMarks GO TOP

&& Make TStudMarks the current active work area

The above piece of code is a rather long piece of code is it not? So what is happening here? There are two loops that guarantee that we shall obtain all the information we need. The first or outer loop is the intRows loop that counts up to intStudsInClass. This allows us to loop through all rows in the spreadsheet. A second loop nested within this loop is the intCols loop that ensures that we loop through all columns in the spreadsheet so that the mark entry details for all subjects

isread-in. A DO CASE…ENDCASE structure lets us decide what column we are processing! If the value of intCols is 1 (one) then this is the No (Serial No) column field in the spreadsheet, we do not need this so use the LOOP clause of the FOR NEXT…ENDFOR loop to cause the loop to iterate prematurely, thus incrementing intCols to 2 (two). If the value of intCols is 2 (two) then we reading the name of the student. Because each student will have four rows (four terms) of student marks, it is important to determine if we have started processing a new student so as to acquire the valid information (term, quarter, etc) from the spreadsheet and this is what the next few statements do. We are not interested in the average row so we will make the loop repeat again. Now that we have determined the student being processed, we need to determine the exact columns being processed and subject columns begin from column 6 (CASE intCols > 5). The current column being processed determines the subject we are reading back. For example, if you are processing Column 6 or 7 then we can obtain the subject name by moving to record 1 on the TSubjs cursor created earlier. If intCols is 8 or 9 then we must move to record 3 in TSubjs cursor. This is how we achieve this (as already shown on the code sample above): SELECT TSubjs IF intCols % 2 = 0 * This is an even number...Add 1 to it to

obtain the record number

ELSE ENDIF

intRecNumb = (intCols % 6) + 1 intRecNumb = (intCols % 6)

The subject headers are in the cursor TSubjs. TSubjs contains all entries on Row 4 beginning from Column 6 to column 27. If you examine the code that created and populated the cursor, you will notice that the code does not discriminate between an actual subject header or what is a teacher remark (though it out to). This means that your TSubjs cursor looks like this: RecNo CourseCode 1 Amharic 2 T Remark 3 English 4 T Remark 5 Math 6 T Remark 7 Biology 8 T Remark 9 Chemistry 10 T Remark 11 Physics

12 13 14 15 16 17 18 19 20 21 22

T Remark History T Remark Geography T Remark Computer T Remark Civics T Remark H & PE T Remark

This table shows that each subject has two entries! The subject itself and its teacher remark (T Remark) code. In the above piece of code, we use the modulus (% OR MOD) function that returns a remainder. So if the value of intCols % 2 is zero (0) then we need to add one to the value returned to obtain the right subject header in the TSubjs cursor else we shall use the value returned as it is. The statement GO intRecNumb moves us to the correct row in the TSubjs cursor so that the correct data is retrieved and stored in memory variables such as cCourseCode, cCourseName, etc for easy usage, The next statement will process all rows between columns 6 and 28 (intCols >=6 AND intCols <= 28). It will read back the score for each subject from the Excel spreadsheet, storing the value obtained in the memory variable nScore and the exam ranking for each student, storing it in the memory variable intRankInClass and so on! Once the relevant information has been read back, the lines SELECT TStudMarks, APPEND BLANK and those that follow it will populate the TStudMarks cursor. This is the first step – populating the TStudMarks cursor with the records found in the Excel Spreadsheet. The next step shall be to populate TStudMarks cursor with the correct ID’s and Birth Dates from the TStuds cursor that contains a list of students drawn from the Students Master List table AcdStudents. Step 7 covers how to perform this action, 7. Step 7: Obtain the Student IDs from the Students Master Table. As you will notice, the Excel spreadsheet does not have a column for Student ID! So we need to use phonetic matching (names that sound the same) to match the names obtained from the spreadsheet to the names contained in the Students Master table for the students of the specified grade and class. Phonetic Matching helps us achieve this by using the DIFFERENCE command that returns a number between 1 to 4 to indicate the level of similarity of pronunciation between two words. The

higher the number returned, the greater the similarity in pronunciation. This was achieved using the following code: * Now process the students record by matching first and last names THISFORM.lblStatus.Caption = "Now matching names and Student IDS...Please wait!" **** This code was designed to help us find student ID codes by phonetic matching **** i am commenting is out because we have decided to add a column for Student ID **** to the Ms excel Data Grid Object SCAN cStudName = UPPER(RTRIM(TStudMarks.StudName)) intCol = RAT(cStudName,CHR(32),1) cFirstName = LEFT(cStudName,intCol) cLastName = RIGHT(cStudName,LEN(cStudName) - intCol) * Now Match the record in the Students master table THISFORM.lblStatus.Caption = "Now matching " + cStudName SELECT TStuds GO TOP SCAN cRFirstName = UPPER(RTRIM(TStuds.FirstName)) + " " + UPPER(RTRIM(TStuds.MiddleName)) intFirstRank = DIFFERENCE(cStudName,cRFirstName) cMsg = "Comparing " + cStudName+ " to " + cRFirstName + " With this result rank: " + ALLTRIM(STR(intFirstRank)) THISFORM.lblStatus.Caption = cMsg IF intFirstRank >= 3 cMsg = TStuds.StudentCode SELECT TStudMarks REPLACE TStudMarks.StudCode WITH cMsg REPLACE TStudmarks.BirthDate WITH TStuds.BirthDate cRFirstName = "" cRMiddleName = "" cMsg = "" EXIT ENDIF ENDSCAN ENDSCAN SELECT TStudMarks && Make TStudMarks the current active cursor

In the above piece of code, the intFirstRank >=3 determines if we accept a match or not. If you wanted a match that is as close as possible, you could use the value 4, being the highest value returned by the DIFFERENCE function. 8. Step 8: Update the Students Marks List table on your master database with the details that you have collected into the temporary cursor TStudMarks. You can do this with the following piece of code:

* Now that we have matched the ID's, we need to update the * For each record in the cursot, find the matching record * in the AcdStudMark table. if the record exists then a mark * entry already exists...make changes to it else just create it THISFORM.lblStatus.Caption = "Now preparing to update master marks list...Please wait!" cBatchCode = "" IF NOT USED("AcdStudMarks") USE AcdStudMarks IN 0 ENDIF SELECT TStudMarks GO TOP SCAN cMsg = "Now updating " + TStudMarks.StudName + " - " + TStudMarks.courseCode + " Term " + ALLTRIM(STR(TStudMArks.Term)) THISFORM.lblstatus.Caption = cMsg SELECT AcdStudMarks GO TOP LOCATE FOR ALLTRIM(AcdStudMarks.StudentCode) = ALLTRIM(TStudMarks.StudCode) ; AND ALLTRIM(AcdStudMarks.CourseCode) = ALLTRIM(TStudMArks.CourseCode) ; AND AcdStudMarks.Grade = TStudMArks.Grade ; AND AcdStudMarks.Class = TStudMArks.ClassCode; AND AcdStudMarks.AcdStartYear = TStudMarks.AcdStartYear ; AND AcdStudMarks.AcdEndYear = TStudMarks.AcdEndYear; AND AcdStudMarks.Term = TStudMarks.Term IF NOT FOUND() && Record does not exist in the Marks Entry table...create it STORE "" TO cParamCode,cParamVal2,cBatchCode STORE 0 TO nParamVal1 * Generate a New BatchCode cParamCode = "AcdStudMarkNo" nParamVal1 = 0 cParamVal2 = "" lAnswer = THISFORM.CMSysParm21.GetParameter(cParamCode,nParamVal1,cParamVal2,chrP rogTitle) IF NOT lAnswer THISFORM.lblstatus.Caption = "*** Stop Error-Error occurred generating mark entry number ***"; + " for student: " + RTRIM(AcdStudSubj.StudentCode) + "!***" ROLLBACK strMsg = "Error occurred generating mark entry number!" MESSAGEBOX(strMsg,MBEXCLAMATION,chrProgTitle) RETURN ENDIF cBatchCode = "" cBatchCode = ALLTRIM(chrBranchCode) cBatchCode = ALLTRIM(cBatchCode) + ALLTRIM(STR(MONTH(datSystDate)))

cBatchCode = ALLTRIM(cBatchCode ) + ALLTRIM(STR(DAY(datSystDate))) cBatchCode = ALLTRIM(cBatchCode) + ALLTRIM(STR(YEAR(datSystDate))) cBatchCode = ALLTRIM(cBatchCode) + ALLTRIM(STR(nParamVal1)) SELECT AcdStudMarks APPEND BLANK REPLACE AcdStudMarks.BatchCode WITH cBatchCode ENDIF * Record exists in the Marks entry table...save changes to it REPLACE AcdStudMarks.StudentCode WITH TStudMarks.StudCode REPLACE AcdStudMarks.StudentName WITH TStudMarks.StudName REPLACE AcdStudMarks.Sex WITH TStudMarks.Sex REPLACE AcdStudMarks.BirthDate WITH TStudMarks.BirthDate REPLACE AcdStudMarks.Age WITH TStudMarks.Age REPLACE AcdStudMarks.Grade WITH TStudMarks.Grade REPLACE AcdStudMarks.Class WITH TStudMarks.ClassCode REPLACE AcdStudMarks.AcdStartYear WITH TStudMarks.AcdStartYear REPLACE AcdStudMarks.AcdEndYear WITH TStudMarks.AcdEndYear REPLACE AcdStudMarks.Term WITH TStudMarks.Term REPLACE AcdStudMarks.CourseCode WITH TStudMarks.CourseCode REPLACE AcdStudMarks.CourseName WITH TStudMarks.CourseName REPLACE AcdStudMarks.EmployeeCode WITH TStudMarks.EmployeeCode REPLACE AcdStudMarks.EmployeeName WITH TStudMarks.EmployeeName REPLACE AcdStudMarks.ExamMark WITH TStudMarks.ExamMark REPLACE AcdStudMarks.Examavg WITH TStudMarks.ExamAvg replace AcdStudMarks.Behaviour WITH TStudMarks.Behaviour REPLACE AcdStudMarks.NoOfStuds WITH TStudMarks.NoOfStuds REPLACE AcdStudMarks.DaysAbs WITH TStudMarks.DaysAbs ENDSCAN THISFORM.lblstatus.Caption = "*** Process Complete ***"

Our Database: If you want this little system to run then you will also have to reconstruct the database. We created a Visual FoxPro Database (you could call it any name and then added the following tables to it:

SyGradeCodes is the table that holds the list of school grades managed by your school. SyGradeCodes is a part of your data environment. SyTeacherNotes is a table that holds standard Teacher remark codes. These should general contain the same codes as those entered on the T Remark fields on the spreadsheet. These will be verified during the conversion. We did not add this table to the data environment but instead opened it with a USE Statement. The SySubjects table holds the list of subjects being taught to students. When we read a subject name from the Excel spreadsheet, we check this table to obtain its CourseCode, Total possible marks (TotPossMarks) and whether or not it is a curricular subject (Curricular). We did not add this table to our data environment but just used a Use statement as the need arose.

These tables should be added to your form’s data environment. AcdStudents is the Students Master Table. AcdStudMarks holds the students school grade marks for every term. AdmTeachSubj is the teacher’s timetable, used to verify the name and ID of the teacher taking a specified subject for the given term (required information on a Report Card printed from the system). We also have the following tables: The sample spreadsheet from which the data was imported looks like this:

Conclusion: After building the form shown above, simply drop a command button onto the form and then copy and paste all of the code examples shown in this article. Of course, you would need to build the associated tables as well. The objective of this article has been to demonstrate the integration and conversion of existing date in Microsoft Excel Spreadsheets into tables in a Microsoft Visual FoxPro database where exact matching keys don t exist with a fair degree of accuracy by using the DIFFERENCE function provided by VFP. We recognize that the richness of the Visual FoxPro Language means that this could be achieved in many different ways; yet this little write-up contributes a little to the literature on this subject. User friendliness can be employed with this sample in many ways. For example, you may want to give your users the ability to define the structure of their master marks list excel spreadsheet (after all the format may not remain the same for ever) and the format may differ from one school to the other) and so on. The Visual FoxPro Programming language is rich and its database engine very powerful thus allowing you to build the most robust applications quickly. The phonetic matching capabilities using the DIFFERENCE function provides a powerful avenue to integrate existing software and convert existing data from those formats (in this example, Microsoft Excel) into the application system. Careful selection of the commands and functions to use can allow you to build the most powerful and fastest ‘pure fox’ applications.