Tod means Fox

In this post I’ll show how you can use Beyond Compare’s scripting ability to compare DBFs from VFP.

“Your true value depends entirely on what you are compared with.”
Bob Wells

Occasionally there is a need to compare tables in two directories to make sure that they are the same (or different). You can use my Intersect example from my last post using VFP code, but this will only get you to the row. Additional code would be needed to identify the exact column that is different. Although this task is certainly possible in VFP, I thought that using Beyond Compare (BC) would be a nicer solution.

Not too long ago I found a way to ‘integrate’ BC from within VFP using BC’s script capability. It’s tough to find good documentation on how to do it, though. I’m all ears if anyone has a good reference. Anyway, the following code demonstrates how I was able to accomplish the task. The output is sent to an HTML file (which I thought was easiest to view/navigate).

LOCAL lcDirA, lcDirB, lcDisplayOption,;
    lcFileA, lcFileB, cVar As String
LOCAL ARRAY aDirA[1,1], aDirB[1,1]
LOCAL llDiffOnly AS Boolean
LOCAL n AS Integer
 
*-- step 1, get the directories
lcDirA = GETDIR()
lcDirB = GETDIR()
IF !DIRECTORY(lcDirA + "BC_A")
    MKDIR lcDirA + "BC_A"
ENDIF
IF !DIRECTORY(lcDirB + "BC_B")
    MKDIR lcDirB + "BC_B"
ENDIF
 
*-- step 2, load the tables into the array
lnDirA = ADIR(aDirA,lcDirA+"*.DBF")
lnDirB = ADIR(aDirB,lcDirB+"*.DBF")
 
*-- step 3, some options
llDiffOnly = .t. && set to .f. to display all records
lcDisplayOption = IIF(llDiffOnly,"display-mismatches","display-all")
 
*-- step 4, loop through the files that appear in both (the intersect)
FOR n = 1 TO lnDirA
     IF ASCAN(aDirB,aDirA[n,1]) > 0
        *-- copy to CSV (you will have to handle memo fields differently!)
        lcFileA = lcDirA+aDirA[n,1]
        USE (lcDirA+aDirA[n,1])
        COPY TO lcDirA + ADDBS("BC_A") + FORCEEXT(aDirA[n,1],"CSV") TYPE CSV
        USE IN (aDirA[n,1])
        lcFileB = lcDirB+aDirA[n,1]
        USE (lcFileB)
        COPY TO lcDirB + ADDBS("BC_B") + FORCEEXT(aDirA[n,1],"CSV") TYPE CSV
        USE IN (aDirA[n,1])
     ENDIF
NEXT
 
*-- Step 5, set up the script file
lcResultFile = FORCEEXT("c:tempBC_" + DTOC(DATE(),1) ,"HTM")
TEXT TO cVar NOSHOW
    load &lt;<lcdira+"bc_a">&gt; &lt;<lcdirb+"bc_b">&gt;;
    expand all
    select all.files
    option confirm:no-to-all
    file-report layout:side-by-side
    options:&lt;<lcdisplayoption>&gt;
    title:"&lt;&lt;_SCREEN.caption&gt;&gt;"
    output-to:&lt;<lcresultfile>&gt;
    output-options:html-color,wrap-none
ENDTEXT
 
STRTOFILE(TEXTMERGE(cVar),"c:tempbcvfp_script.txt")
 
*-- Step 6, Run Beyond Compare
RUN /N C:Program FilesBeyond Compare2bc2.exe @c:tempbcvfp_script.txt
</lcresultfile></lcdisplayoption></lcdirb+"bc_b"></lcdira+"bc_a">

Please note, that BC will open its own dialog that will display script, log,
and error information. I usually check the box ‘Close when finished’ so I have an idea when BC actually completed the task. Then I just browse to the HTML file created.

Of course, there is plenty of room for improvement in the above code. I’ve started a project to combine this with several other methods to do an exhaustive comparison of tables from one directory to another (which would include index, structure, and memo field comparisons). I hope to publish some of that code in upcoming blog entries.

Beyond Compare
A general scripting reference for BC

*Update*
It seems I always get bit by this: In my code example above, the HTML parser removed several chunks of code because they were in between < and >. I need to get into the habit of using the ISO-8859-1 character set codes instead. Blogger doesn’t convert these things for me… The code in the above example has been updated.