Jump to content

 
Photo

ArcMap-VBA recursive Find/Replace

- - - - -

  • Please log in to reply
3 replies to this topic

#1
jbl

jbl

    Contributor

  • Validated Member
  • PipPip
  • 27 posts
  • Location:JoCo_KS
  • United States

It has been a while since I used VB script and I am stumbling around trying to find a recursive script to modify street names in field [FULLNAME]. Most all of the numerical suffixes (th, st, nd, rd, ie... 20th, 21st, 22nd, 23rd) are missing.

Since there are 200 = streets (1st St - 235th St) I would rather not do it one at a time using Find/Replace after selecting the field, but rather within some sort of loop.

This also works individually if the targets are already selected
(this looks for a leading and trailing space on the numeral)
Field Calculator (TARGET MUST BE ALREADY BE SELECTED)

Pre-Logic VBA Script Code Advanced

Result = REPLACE([FULLNAME]," 28 "," 28th ")

FULLNAME = Result

Data would be
1 ,
through
235 ,
jbl

#2
dsl

dsl

    Master Contributor

  • Validated Member
  • PipPipPipPip
  • 225 posts
  • Gender:Male
  • Location:Denver
  • United States

This might be a bit convoluted, but should be generic enough so that it works for everything.

First step would be to split the string by some character, my guess would be the number part of the string is separated by a space. This will give you an array of the string values.

http://www.w3schools.../func_split.asp

Next loop through the array to find the number portion of your string. You can use the IsNumeric function to identify the number portion.

http://www.w3schools...c_isnumeric.asp

Then you can get the last number character like this: last = Right(numberString, 1) (off the top of my head, might not work).

Use a Select Case to set up the different scenarios

Select Case Last
Case = "1"
ending = "ST"
Case = "2"
ending = "ND"
Case = "3"
ending = "RD"
Case else
ending = "TH"
End Select

Perhaps add a few if then statements to handle 11, 12, 13 since they would use "TH".

Recombine your string using the new ending string(0) & ending &" " & string(1) etc...

Hope that helps,
David

#3
jbl

jbl

    Contributor

  • Validated Member
  • PipPip
  • 27 posts
  • Location:JoCo_KS
  • United States

While still looking for a VB solution within ArcGis; (I am working with US Census Tiger 2009 data)
I decided for the time being to
Make a copy of the table (.shp file)
remove all fields except TLID and FULLNAME
Create a text file out of this dbf by opening in MS Excel & exporting as txt
Perform all of the necessary changes utilizing REGULAR EXPRESSIONS in my text editor (EditPad)
Create a MS Access mdb database of this text file, field names, TLID & NEWNAME
Create a join on TLID in ArcGIS my original table/layer/shp file joined by the newly created mdb
Export this joined table as a copy
Using Field Calculator on field FULLNAME in the copy, FULLNAME = NEWNAME
Delete fields TLID_1 and NEWNAME
Replace the original shp file with the new and edited one.

(Most of you probably already would know how to do this anyway. I guess until I come up with a VB solution within ArcGis this will have to do)

jbl

#4
graveldraw

graveldraw

    Contributor

  • Validated Member
  • PipPip
  • 23 posts
  • Gender:Male
  • United States

Which state-county? The 2009 shapefile I looked at doesn't have this problem. I am learning scripting and this sounds like a good practice. Will share results.

Dan

[quote name='jbl' date='Dec 17 2009, 04:12 AM' post='28731']
While still looking for a VB solution within ArcGis; (I am working with US Census Tiger 2009 data)
I decided for the time being to
Make a copy of the table (.shp file)
remove all fields except TLID and FULLNAME
Create a text file out of this dbf by opening in MS Excel & exporting as txt
Perform all of the necessary changes utilizing REGULAR EXPRESSIONS in my text editor (EditPad)
Create a MS Access mdb database of this text file, field names, TLID & NEWNAME
Create a join on TLID in ArcGIS my original table/layer/shp file joined by the newly created mdb
Export this joined table as a copy
Using Field Calculator on field FULLNAME in the copy, FULLNAME = NEWNAME
Delete fields TLID_1 and NEWNAME
Replace the original shp file with the new and edited one.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

-->