Jump to content

 
Photo

editing ArcMap attribute table

- - - - -

  • Please log in to reply
14 replies to this topic

#1
Dennis McClendon

Dennis McClendon

    Hall of Fame

  • Validated Member
  • PipPipPipPipPipPipPip
  • 1,083 posts
  • Gender:Male
  • Location:Chicago
  • Interests:map design, large-scale maps of cities
  • United States

I presume this is a fairly typical situation: the streetnames in the GIS data from the county are not in the form I want. They're all caps, use inconsistent suffixes, and need other kinds of cleanup. I'd like to edit them in the database before exporting a labeled map to Illustrator for further prettification.

Can I just export the DBF file, take it home and edit that in Excel? Or should I preserve the existing streetnames in one column, put my corrected streetnames in another, and then do some kind of joining and replacement in ArcMap? When I opened up the DBF file I exported, it didn't seem to have the FID and SHAPE fields seen in the attribute table. I would much prefer to do the editing at my Mac, where I can use various search and replace tools, rather than working directly on the table cells in ArcMap.

GIS is still quite unfamiliar to me and all advice is welcome.
Dennis McClendon, Chicago CartoGraphics
chicagocarto.com

#2
ELeFevre

ELeFevre

    Hall of Fame

  • Associate Admin
  • PipPipPipPipPipPipPip
  • 1,049 posts
  • Gender:Male
  • Location:Louisville, Colorado USA
  • Interests:Cartography, musical instruments, reading, hiking, craft beer
  • United States

Dennis,

I would export a DBF and open it in Excel and make your changes. You can also export a text file if you need to.

You can do this by opening the attribute table in ArcMap. Click on the "Options" menu located at the bottom of the attribute and you will see an "Export" option. You can also export a table in ArcCatalog. If your working from a personal geo-database, you can open and modify the table directly in Microsoft Access and save your changes. If you're working from shapefiles then you will need to export the table or edit in ArcMap. Make the appropriate changes to either the DBF or the text file in Excel and resave in the appropriate format and add the table back into ArcMap. I typically use DBF 4. You can then join the new table with the old and delete the old columns.

I've always found it easier to bring in a clean table from Excel rather than trying to clean-up a table in ArcMap. Erin



#3
supercooper

supercooper

    Contributor

  • Validated Member
  • PipPip
  • 45 posts
  • Location:Fayetteville, AR
  • United States

This is a VERY typical situation. I agree with Erin, export to dbf4, then do you edits, then rejoin in AM and label. And you wont find the FID and SHAPE fields in the export dbf either, they arent exported.

#4
David T

David T

    GIS Manager, USMC, MCIWEST-MCB Camp Pendleton

  • Validated Member
  • PipPipPipPip
  • 192 posts
  • Gender:Male
  • Location:San Diego, California
  • United States

Don't go through all that trouble! I was having the same issue a few days ago. Spent a few minutes on support.esri.com, and came up with the following code:

StrConv ( [YOUR FIELD],vbProperCase )

Replace [YOUR FIELD] with the field name that contains the information you need to change. Run this piece of code using the Calculation function in the attribute table.

Takes about 2 seconds to run, and works perfectly. I managed to change 6000 records in about 2-3 seconds the other day using this.

If you need to change to upper case, I believe you change vbProperCase to vbUCase. It might be vbLCase. I can't remember which. But, those are your three options.

Edit to add: Here's a link to the message on ESRI's website that helped me out.
David Toney, GISP
GIS Manager
United States Marine Corps
West Coast Installations

#5
Matthew Hampton

Matthew Hampton

    Hall of Fame

  • Moderator
  • PipPipPipPipPipPipPip
  • 1,306 posts
  • Gender:Male
  • Location:Portland, Oregon
  • Interests:Playing in the mountains and rivers.
  • United States

Hey David - thanks for the heads-up on the case change in Arc. I have done the Excel circle several times and although I was greatly pleased with the success and digital agility of the export/modify/import/join route, using that code is much simpler.

It sounds like Dennis's case might be a little different as he mentions inconsistent suffixes, etc. that might need more manual work.

co-cartographic creator of boringmaps.com


#6
ELeFevre

ELeFevre

    Hall of Fame

  • Associate Admin
  • PipPipPipPipPipPipPip
  • 1,049 posts
  • Gender:Male
  • Location:Louisville, Colorado USA
  • Interests:Cartography, musical instruments, reading, hiking, craft beer
  • United States

Excellent script! By the way, won't it be nice when we finally get native Excel support in ArcGIS 9.2!



#7
Dennis McClendon

Dennis McClendon

    Hall of Fame

  • Validated Member
  • PipPipPipPipPipPipPip
  • 1,083 posts
  • Gender:Male
  • Location:Chicago
  • Interests:map design, large-scale maps of cities
  • United States

I guess that answers the question I came up with yesterday of whether ArcMap offers any easy way to change case in labels.

In my case, I need to edit suffixes, diacriticals, and misspellings in addition to case, and it seems much easier to catch them in the table rather than on the finished map. If I understand correctly, because FID doesn't export into the DBF file, I need to keep the current STREET_NAME list in one column and my CORRECTED_NAME list in another (new) column, then join my revised table to the existing table using the [incorrect] STREET_NAME column. After that, I can label features using CORRECTED_NAME instead of STREET_NAME.
Dennis McClendon, Chicago CartoGraphics
chicagocarto.com

#8
supercooper

supercooper

    Contributor

  • Validated Member
  • PipPip
  • 45 posts
  • Location:Fayetteville, AR
  • United States

Using VB, there are many ways to deal with messy data in attribute tables....This first function replaces County Road with "CR" and makes state higghways label in a larger font...

Function FindLabel ( [FEDIRP], [FENAME], [FETYPE] )
If LEFT([FENAME],11) = "County Road" Then
FindLabel = [FEDIRP] & " " & REPLACE([FENAME],"County Road","CR") & " " & [FETYPE]
ElseIf LEFT([FENAME],13) = "State Highway" Then
FindLabel = "<fnt size='14'>" & [FEDIRP] & " " & [FENAME] & " " & [FETYPE] & "</fnt>"
Else
FindLabel = [FEDIRP] & " " & [FENAME] & " " & [FETYPE]
End If
End Function

This next one cleans up well labels by inserting the section number where not present and cleans up the operator name by getting rid of extraneous stuff like Incorporated or Company....

just some stuff I had that can give ideas of how to clean up messy stuff.....



Function FindLabel ( [operName], [leaseName], [wellNumber], [trs] ) ' this one used in altusProduction.mxd to label the Altus production wells

' get the section number from the end of the trs string for use below in wellNumber where
' Dwights data doesnt have the section number in the well number already
Dim sectNo
If Len([trs]) = 8 Then
sectNo = Right([trs],1)
Else
sectNo = Right([trs],2)
End If

' deal with well number
Dim fullWellNumber
If ((Mid([wellNumber],2,1) = "-") Or (Mid([wellNumber],3,1) = "-")) Then ' already have the section number in the dwights well number, so wellNumber is ok to use by itself
fullWellNumber = [wellNumber]
Else ' need to append "-" and section number to wellNumber
fullWellNumber = [wellNumber] & "-" & sectNo
End If

' get rid of incorporated or company at end of operName
If Mid([operName],len([operName])-11,12) = "INCORPORATED" Then
FindLabel = Mid([operName],1,len([operName])-13) & vbNewLine & [leaseName] & " " & fullWellNumber ' leave INCORPORATED off of the label
ElseIf Mid([operName],len([operName])-6,7) = "COMPANY" Then
FindLabel = Mid([operName],1,len([operName])-8) & vbNewLine & [leaseName] & " " & fullWellNumber ' leave COMPANY off of the label
Else
FindLabel = [operName] & vbNewLine & [leaseName] & " " & fullWellNumber ' otherwise, just label with operName
End If

End Function

#9
Dennis McClendon

Dennis McClendon

    Hall of Fame

  • Validated Member
  • PipPipPipPipPipPipPip
  • 1,083 posts
  • Gender:Male
  • Location:Chicago
  • Interests:map design, large-scale maps of cities
  • United States

Well, I'm not sure what I'm doing wrong. I worked on the streetnames in Excel and exported my corrected table as DBF. In ArcMap I added it as a table, and I can open and view it just fine. But when I join it to the existing attribute table for that layer, everything seems to work fine but the resulting table has no information below the new column headings. I've read and reread the instructions in my ArcMap book and it seems quite straightforward. What am I forgetting to do?
Dennis McClendon, Chicago CartoGraphics
chicagocarto.com

#10
peanut

peanut

    Master Contributor

  • Validated Member
  • PipPipPipPip
  • 138 posts
  • Location:Austin, TX
  • United States

It sounds like ArcMap is not finding any matches between the join field from your original attribute table and the table you created in Excel. I might try making sure the datatypes of the fields are the same in both tables. Also I would make sure the data in the fields don't have any leading spaces. Leading spaces have messed up joins for me before.

Hope this helps.

Rich

#11
Geographic Techniques

Geographic Techniques

    Contributor

  • Validated Member
  • PipPip
  • 46 posts
  • Location:Mount Horeb, WI
  • United States

Dennis,

Also make sure that the DBF file name you're joining to doesn't have any spaces or odd characters. This has caused a few headaches for me in the past.

Doug
Douglas Norgord, Geographic Techniques
www.geotechmap.com

#12
Dennis McClendon

Dennis McClendon

    Hall of Fame

  • Validated Member
  • PipPipPipPipPipPipPip
  • 1,083 posts
  • Gender:Male
  • Location:Chicago
  • Interests:map design, large-scale maps of cities
  • United States

Spaces in the file name! What a 20th century problem! I guess that was messing up the consolidated table headings, because that took care of it. Thanks!
Dennis McClendon, Chicago CartoGraphics
chicagocarto.com

#13
MapMedia

MapMedia

    Hall of Fame

  • Validated Member
  • PipPipPipPipPipPipPip
  • 1,029 posts
  • Gender:Male
  • Location:Davis, California
  • United States

Re-awaken this thread:

Anyone have a good bit of VBSCRIPT to share for labelling ESRI secondary roads?

#14
supercooper

supercooper

    Contributor

  • Validated Member
  • PipPip
  • 45 posts
  • Location:Fayetteville, AR
  • United States

Not sure if this will help you much or not, but its for labeling TIGER roads. Maybe it will get you started. It replaces "County Road" with "CR" and also labels state highways a certain way.


Function FindLabel ( [FEDIRP], [FENAME], [FETYPE] )
If LEFT([FENAME],11) = "County Road" Then
FindLabel = [FEDIRP] & " " & REPLACE([FENAME],"County Road","CR") & " " & [FETYPE]
ElseIf LEFT([FENAME],13) = "State Highway" Then
FindLabel = "<fnt size='14'>" & [FEDIRP] & " " & [FENAME] & " " & [FETYPE] & "</fnt>"
Else
FindLabel = [FEDIRP] & " " & [FENAME] & " " & [FETYPE]
End If
End Function

#15
MapMedia

MapMedia

    Hall of Fame

  • Validated Member
  • PipPipPipPipPipPipPip
  • 1,029 posts
  • Gender:Male
  • Location:Davis, California
  • United States

Thanks SC, thats the idea. I've been messing around with elseif statements. but there are a lot of
'do this if' rules and 'always do this' for about 10 issues and while the code I have is good (no errors), the labels skip half the rules. Just curious if anyone has paved this road already. I am using ESRI 2006 secondary roads (street2.rdc).




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

-->