I am trying to handle string (non-geometry) duplicates in a large attribute table of a shapefile without having to convert to gdb and back or connect to a postgres db. I need to be able to use a count function for my query. I'd prefer not creating an output file, but rather simply updating the file itself. I've looked at the cursors but I cannot seem to accomplish what I need to with them. I essentially need to get a count for duplicates, do a comparison, and then accordingly append values of one field with the values of another field.

  • What kind of duplicates are you looking for? Geometry duplicates (overlapping features), duplicates values within a field, or duplicate rows comparing multiple fields? Commented Dec 5, 2012 at 16:23
  • 1
    Also, since you are working with a shapefile which uses a DBF to store its database info, you will be limited to the query capabilities of the DBF format. Commented Dec 5, 2012 at 16:37
  • Oh so conversion doesn't change the DBF? Thanks for the info. Sorry I forgot to explain what the duplicates are I will do so. Commented Dec 5, 2012 at 17:55
  • Yes, if you convert from a shapefile to another format (file gdb, personal gdb, arcsde gdb, postgis, etc) then of course the database specs will change and you will have different sql capabilities based upon whatever the native database format is. You said that your didn't want to convert the data, though... Commented Dec 5, 2012 at 18:12

3 Answers 3


(This uses neither python nor ArcGIS but it might be a useful approach.)

One alternative approach could be to read the dbf part of the shapfile into R (using the foreign package). Then install and load the package sqldf, which provides full SQL functionality. So long as you do not reorder or change the number of rows you can write out a new dbf file which will work^ with your shapefile.

^although I imagine it is possible to break the shapefile in certain circumstances I have not done this yet using this approach.

  • A creative sounding workaround. Kind of fakes out the original shapefile, and it's none the wiser.
    – Baltok
    Commented Dec 5, 2012 at 19:22

If the duplicates you wish to do something about are across rows I would look at , specifcally ogr sql.

If OGR doesn't do what you want and the data you wish to change are across columns (fields) and not rows (records) with suitable care you can use any tool which understands DBF, which should any db tool worth it's salt, including Access/Excel. Something to watch for, especially with Excel, are silent reformatting of date/time fields, and truncation of total records (Excel has max row limit, and may or may not say something about it). Always work on a copy, validate the results, and never delete, add or re-order rows when using a non-spatial dbf tool.

  • Thanks, I'll look into OGR, but I'm not very good at macros on Access and Excel and I wanted to concatenate string values in two different fields without merging based on data from another so I think this takes a more programmatic solution. Commented Dec 5, 2012 at 18:32
  • Do you know if the python version of ogr sql can be integrated seamlessly with arcpy? Commented Dec 5, 2012 at 20:18
  • 1
    @carl yes, with Arcgis 9.3, following Can ArcGIS and OSGeo4W share the same python install?. I'm not sure what would need to change for 10.x. Commented Dec 6, 2012 at 16:53

If you install pywin32, you can query your .dbf files with any Jet compatible SQL query and access the results in a RecordSet from Python. You can run an aggregate function like count, nested query, update query, etc or output to another dbf. In my experience, the dbf file name must be 8.3 compiant.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.