Remove Duplicate Records Using Laffdb

Question:

I need to remove duplicate records from my flat ascii database file that contains comma delimited data. I see the LAFFDB Extender has a function called dbCookDatabases with an options that allows you to remove duplicates. However I am confused about how to specify some of the parameters.
Specifically:
What do I specify for the HandleB parameter, since it is not required?
How do I specify the matchingcriteria? I have three columns, however i only want to remove duplicates based on the first column, which contains an index number.
Finally, how do I specify the columnlayout?

Answer:

Regarding how the handleB parameter should be handled, since it is not required. Simply specify 0 for that parameter.

If you are only interested in removing duplicates based on the first index column. This means the MatchingCriteria parameter should read:

matchingcriteria = "e * *" ;check FIRST column for 'Exact character, case insensitive match'.

Where:

�e� - references the first column of data. It tells the function to do an 'Exact character, case insensitive match' on that column.

�*� - for the second and third columns, tells the function the string is irrelevant.

Finally and most importantly the columnlayout parameter tells the function how to handle the columns in the output. For each column in your source database, you need to create a triad that is composed of three comma separated values, and represents one column in your source, temp, and target databases. In the case of removing duplicates, you will need a triad for each column in the source database.

For Example:

�Column1,Column1,Column1 Column2,Column2,Column2 Column3,Column3,Column3�

Here is some sample code that shows how to remove duplicate records using Laffdb. I have also included a test database file.

;**************************************************
;** This sample Removes Duplicates. In this case it adds the
;** sample database to itself. It will create a resulting database 
;** with a duplicate record of every record in the source database.
;**************************************************
ret = AddExtender("Laffd34i.dll")
;**************************************************
;**   dbOpen - open the test database A 
;**************************************************
inputfilename = "C:\Temp\table1.txt"  ; CHANGE TO FIT YOUR NEEDS
if !FileExist(inputfilename)
   str = "Make sure the database exists before attempting to run this example."
   str2 = "See the dbOpen example in this help file, it will create the example database for you."
   Message("Database doesn't exist", StrCat(str, @CRLF,str2))
	exit
Endif
delimiter = ","
bCreateOK = 0
model = 2     ;Traditional
numcols = -1  ;Number of columns match first line, names them, starts reading at 2nd line
format = 0    ;Delimiter based 
optionstring = ""
handleA = dbOpen (inputfilename, bCreateOK, model, numcols, format, delimiter, optionstring)
if handleA == @LAFFDBERROR
    err = dbGetLastError()
	 Message("Error Opening the database A specified", err)
	 exit
Endif


outputfilename = "C:\Temp\RemovedDups.txt" ; CHANGE TO FIT YOUR NEEDS
path = filepath(outputfilename)
if !DirExist(path)
   Message("Note",strcat("Creating the directory ", path))
	DirMake(path)
endif
if FileExist(outputfilename) then FileDelete(outputfilename)


operation =  5 ;Remove Duplicates from A. C = A - duplicates
flags = 0
columnlayout = "ID,ID,ID Name,Name,Name Description,Description,Description"  ;CHANGE TO REFLECT YOUR COLUMN NAMES
matchingcriteria = "e * *" ;check FIRST column for 'Exact character, case insensitive match'.
handle = dbCookDatabases ( outputfilename, model, format, delimiter, optionstring, handleA, 0, operation, flags, columnlayout, matchingcriteria)
if handle == @LAFFDBERROR
	 err = dbGetLastError()
	 Message("Error Cooking the database", err)
endif

;**************************************************
;**   dbDebug - to view the database with the column removed
;**************************************************
ret = dbDebug(handle)
if ret == 0
	 err = dbGetLastError()
	 Message("Error using dbDebug", err)
endif
ret = AskYesNo('Do you want to save changes to the sample database?', 'if so, please press "YES"')
if ret == @YES 
	 saveas = AskFileName("SelectFile", "C:\Temp", "All Files|*.*", "RemovedDups.txt", 0)  ;CHANGE TO FIT YOUR NEEDS
   ;**************************************************
   ;**   Saves Changes 
   ;**************************************************
   ret = dbSave (handle, saveas, "")
   if ret == 0
   	 err = dbGetLastError()
   	 Message("Error Saving the database", err)
   endif
endif
;**************************************************
;**   Closes Database handles
;**************************************************
ret = dbClose(handleA)
if ret == @LAFFDBERROR
	 err = dbGetLastError()
	 Message("Error Closing the database", err)
endif

exit

TABLE1.TXT

ID,Name,Description
01,"Hansen Ola","Sales"
02,"Svendso Tove","Support"
03,"Svendson Stephen","Management"
03,"Flintstone Fred","Support"
04,"Pettersen Kari","Sales"

Article ID:   W15889

File Created: 2004:03:30:15:41:26
Last Updated: 2004:03:30:15:41:26

Database Search

TechHome