merge 2 dbfs 1

[SM777]

Whats the best way to merge 2 dbfs?

dbf1 contains 10,000 records
dbf2 contains 15,000 records

some records are in both dbfs. need to merge the two so there is a new dbf (dbf3) with each of dbf1 and dbf2 but no duplicates!

cheers.

 

[circes9]

Hi,
I can rarely remember the easy way, but you could do the following without too much effort...

1. append records from 1st to the 2nd.
2. write a small .prg that indexes the 2nd dbf resulting in all like records being consecutive to each other, then makes one pass through the file comparing each record to the next and if the next is a duplicate, delete it.

Good luck

 

[waterstart]

Here is a way to do it. Do you have dbu.exe? It is a utility shipped with clipper.
Open dbf1 like this - dbu dbf1.
You will be in browse mode. Not how many records you have. Now ESC from browse mode. Press F6 for utilities, Press A for Append from the dropdown menu, choose dbf2. Now press F5 and choose database from the dropdown menu and note how many records are in dbf1. Should be dbf1 and dbf2 records.

Time to remove duplicates.
Here is a function I wrote. It has 4 parameters. Here is what they are and do: cDbf is name of dbf1. cUniqIndex is an index that puts records in order such that duplicates will be adjoining records. bUniqIndex is cUniqIndex in codeblock form. cDupDelIndex is cUniqIndex with 1 additional date field since I had a date field and wanted the latest date record kept. If your records are truly duplicates you will not care which one you keep. In that case cDupDelIndex is cUniqIndex. Anyway here is a call to the function Uniqone().

UniqOne( "actpart",;
"ssn+part_no",;
{ || ssn+part_no },;
"ssn+part_no+descend(dtos(term_date))&quot

/*
function UniqOne(cDbf,cUniqIndex,bUniqIndex,cDupDelIndex)
Remove duplicate records be removing the oldest record.
Index dbf by unique index that also has a last entry date in it
so records are by descending date order. Walk through dbf noting
any duplicates and marking them for deletion. Pack dbf.

Params: dbf
unique index for dbf
unique index with lastentry date incorporated
Returns: nil
*/
static function UniqOne(cDbf,cUniqIndex,bUniqIndex,cDupDelIndex)

*** Take most recently edited rec.
local cUnique
local lOpen
local nWorkarea:= select()

*** Open file if not open.
if (cDbf)->(used())
dbselectarea(cDbf)
lOpen:= .t.
else
opentable(cDbf, KEXCLUSIVE, KSEC3)
lOpen:= .f.
endif

*** Stop if file empty.
if (cDbf)->(lastrec()) < 1
if lOpen = .f.
(cDbf)->(dbclosearea())
endif
return nil
endif

*** Create index with all records.
index on &cDupDelIndex to temp

*** Mark dups for deletion.
cUnique:= eval(bUniqIndex)
dbskip(1)
do while ! eof()
if cUnique = eval(bUniqIndex) // Duplicate record.
dbdelete() // Mark record for deletion.
else // Set index to record being compared.
cUnique:= eval(bUniqIndex)
endif
dbskip(1)
enddo

*** Pack dbf.
(cDbf)->(fpack())

*** Close file if it was closed going into this function.
if lOpen = .f.
(cDbf)->(dbclosearea())
endif

*** Return to the work area selected when first in program.
select (nWorkarea)

return nil