topic Re: Code Help in Geodatabase Questions

Code Help

JonPeroff — Fri, 21 Aug 2015 21:32:58 GMT

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

Re: Code Help

DanPatterson_Retired — Sat, 22 Aug 2015 00:00:29 GMT

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.

Re: Code Help

XanderBakker — Sun, 12 Dec 2021 09:49:09 GMT

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:

the number is <10 and > 99
if it isn't a number (should check to see if it is numeric)
Code wil not find 'SB' or 'sB' or 'sb', it is case sensitive
etc...

Re: Code Help

JonPeroff — Mon, 24 Aug 2015 16:06:52 GMT

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=)

I posted my .gdb here as a zip file.

https://drive.google.com/file/d/0B3tBBFkFiMrIMktNaThKQ1ZkdEk/view?usp=sharing

Re: Code Help

WesMiller — Mon, 24 Aug 2015 17:30:34 GMT

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

Re: Code Help

XanderBakker — Mon, 24 Aug 2015 17:37:55 GMT

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:

Re: Code Help

JonPeroff — Mon, 24 Aug 2015 19:23:41 GMT

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

Re: Code Help

XanderBakker — Mon, 24 Aug 2015 22:14:58 GMT

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"

Re: Code Help

JonPeroff — Mon, 24 Aug 2015 22:59:14 GMT

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.

Re: Code Help

XanderBakker — Sun, 12 Dec 2021 09:49:11 GMT

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:

field names that need to be updated are two characters long and correspond to the specie
a species inside the field SPCOMP always occupies 5 characters.