I'm in need of some help. I have a file with a attribute field named SPCOMP and I need to separate it out. The SPCOMP field is populated with a string such as "Sb 90Bf 10" or Pj 80Sb 20" or Pj 40Bw 40Sb 20" etc. I'm trying to write a code using the field calculator in VB
script that will search through the SPCOMP field for "Sb 90" and populate another field SB with 90 or search for "Sb 60" and put 60
in the SB field. My code is:
Dim SB
if [SPCOMP] = "%Sb 90%" Then
SB = 90
elseif [SPCOMP] = "%Sb 60%" Then
SB = 60
else
SB = 1
end if
When I run this it will populate the SB field with 1 so I think my mistake is with the wildcard search? Can anyone offer suggestions on this or where I could find answers? I'm using ArcGIS advanced 10.3.
Thanks
Like operator perhaps? from the 10.3 help files...but nothing has changed you can try "building a query expression" or "wildcard" as search terms in your help
Building a query expression—Help | ArcGIS for Desktop
Use the LIKE operator (instead of the = operator) to build a partial string search. For example, this expression would select Mississippi and Missouri among U.S. state names:
STATE_NAME LIKE 'Miss%'
% means that anything is acceptable in its place: one character, a hundred characters, or no character. Alternatively, if you want to search with a wildcard that represents one character, use _.
For example, this expression would find Catherine Smith and Katherine Smith:
OWNER_NAME LIKE '_atherine smith'
The wildcards above work for any file-based data or ArcSDE geodatabase. The wildcards you use to query personal geodatabases are * for any number of characters and ? for one character.
If you are interested in using the Python syntax you could use this:
def getSB(spcomb): if 'Sb ' in spcomb: n = spcomb.find('Sb ') return spcomb[n+4:n+6] else: return 0
getSB( !SPCOMP! )
and the result:
Before you try this, you ask this question: Is it always a number between 10 and 99 (two digits)?
... it will fail if:
I have tried the LIKE operator in VB but the error message is always 99999 (general error) I tried may different ways but the same result. If I type the SPCOMP code in exactly then it works.
(ie: if [SPCOMP] = 'Sb 90Bf 10')
I tried the python but it does not work either. I never used python before and don't understand it either. Maybe I should though.
And yes the string is exact (it's either SB or Sb but never both in the same file.)
in the VB calculator, this is my code
Dim SB
if [SPCOMP] LIKE '%Sb 100%' Then
SB = 100
elseif [SPCOMP] LIKE '%Sb 90%' Then
SB = 90
elseif [SPCOMP] LIKE '%Sb 80%' Then
SB = 80
elseif [SPCOMP] LIKE '%Sb 70%' Then
SB = 70
elseif [SPCOMP] LIKE '%Sb 60%' Then
SB = 60
elseif [SPCOMP] LIKE '%Sb 50%' Then
SB = 50
elseif [SPCOMP] LIKE '%Sb 40%' Then
SB = 40
elseif [SPCOMP] LIKE '%Sb 30%' Then
SB = 30
elseif [SPCOMP] LIKE '%Sb 20%' Then
SB = 20
elseif [SPCOMP] LIKE '%Sb 10%' Then
SB = 10
else
SB = 0
end if
in the lower box (SB=)
SB
I posted my .gdb here as a zip file.
https://drive.google.com/file/d/0B3tBBFkFiMrIMktNaThKQ1ZkdEk/view?usp=sharing
Use select by attributes to get a selection then use field calculator to populate the intended field
fieldname LIKE '%90%'
Then field calculate the selection set to 90
repeat for 60
Could you attach the ZIP to the thread (click on advanced editor upper right corner and then on Attach in lower right corner), since I wasn't able to download your file:
Thanks I didn't know how to attach a file.
Wes suggested select by attributes then using the calculator and I know that works but there are 14 species and 10 percentages each so that tends to become tedious after a while. The combination I'm looking for is Sb 90 or SB 80 or PO 20 or LA 70 etc Hopefully, the attached file will help. I can do this in MS excel but I would like to do it in ARCmap
The code I posted earlier seems to work on your data:
... however, looking at your data and the fact that you want to apply this to multiple fields, I think using Python code and an update cursor would be easier...
Edit: I see that the result is wrong for "Sb 100"
Yes, this does work, Thanks. I even modified it a bit for other species and it works.
def getSB(spcomb):
if 'Sb' in spcomb:
n = spcomb.find('Sb')
return spcomb[n+3:n+6]
else:
return 0
I don't understand what "return spcomb[n+3:n+6]" is but I changed the n+4 to n+3 and the 'Sb ' to 'Sb' and it worked for the Sb 100 so I'm assuming it relates to 6 spaces to the right of Sb and 3 back from that.
Thanks to everyone for helping me with this.
As mentioned before, I would probably use some Python code to do the job:
This table was updated using this code:
def main(): import arcpy fc = r"C:\GeoNet\SPCOMP\ARC.gdb\SPCOMP" fld_in = "SPCOMP" # create list of fields with length 2 flds = [fld.name for fld in arcpy.ListFields(fc) if len(fld.name) == 2] flds.insert(0, fld_in) # start update cursor with arcpy.da.UpdateCursor(fc, flds) as curs: for row in curs: spcomp = row[0].upper() for fldname in flds[1:]: if fldname.upper() in spcomp: perc = getPercentage(fldname.upper(), spcomp) row[flds.index(fldname)] = perc curs.updateRow(row) def getPercentage(fldname, spcomp): try: n = spcomp.find(fldname) part = spcomp[n+2:n+6] return int(part) except: return -1 if __name__ == '__main__': main()
Some assumptions that seem to be true for this table: