English

How To: Remove duplicate records from a table

Summary

This article provides a script that will remove duplicate records from a table for a user specified field.

Procedure

This script will evaluate only one field to determine if a record is duplicated. You can modify the script if your comparison needs to include multiple fields.

  1. Open an new script.

    A. Activate the Project window.
    B. Click the Scripts icon.
    C. Click New.

  2. Copy and paste the following script:

    Code:
    '-- RemoveDuplicateRecords.ave

    '-- This script will determine if duplicate records exist within a table based
    '-- on a user-defined field. It will create a new table without the duplicate records.
    '-- You will need to have a table document as the active document when running
    '-- this script. It will prompt you for the field to compare values on in order to determine
    '-- if it is a duplicate record. It will also prompt you for an output filename.

    atable = av.getactivedoc
    avtab = atable.getvtab
    selbitmap = avtab.getselection

    '-- Set all bits in the selection bitmap to false
    selbitmap.clearall

    '-- Determine a field to run the comparison on
    '-- Sort the field in ascending order

    afieldlist = avtab.getfields
    sortfield = msgbox.choiceasstring(afieldlist, "Please choose a field that will determine if duplicate records exist:", "Fields")
    atable.sort(sortfield, false)

    '-- Create a starting value of nil to compare table values
    '-- If your table has nil values, you can choose a new value

    avalue = nil

    '-- For each record in a Vtab
    '-- Convert aRowNumber in the table to a record number in the VTab of the table
    '-- The record number will now be dependent on the order of the sort field
    '-- Compare each record's value string to the previous record's value string
    '-- Each record that is determined not to be a duplicate is set to TRUE in
    '-- the Selection Bitmap for the Vtab

    for each rec in avtab
    arecordnumber = atable.convertrowtorecord(rec)
    comparisonstring = avtab.returnvaluestring(sortfield, arecordnumber)
    if (comparisonstring = avalue) then
    continue
    avalue = comparisonstring
    else
    selbitmap.set(arecordnumber)
    avalue = comparisonstring
    end
    end
    avtab.updateselection

    '-- Export the selected records to a new file (anewVtab)
    '-- Create and open a new table based on the newVtab

    newFileName = FileDialog.Put ("$HOME\newfile.dbf".asfilename, "*.dbf",
    "Choose output filename!")
    anewvtab = avtab.export(newFileName, dbase, true)
    anewtable = table.make(anewvtab)
    anewtable.setname(atable.getname++"Without Duplicate Records")
    'av.getproject.adddoc(atable)
    anewtable.getwin.open
    selbitmap.clearall
    avtab.updateselection

  3. Click the
    [O-Image] Script compile button
    button to compile the script.
  4. Click the
    [O-Image] Run compiled script button
    button to run the script.