Split Attribute field

3575
7
Jump to solution
10-09-2014 02:19 AM
PV
by
New Contributor III

I have an Attribute table with several attributes

One of the attributes has the fields

K1-10-11

K1-10-46

K1-10-46c

K1-10-67d (street)

 

I Need all of the elements from the field as seperate attributes

eg an Attribute with

K1

K1
K1

 

another with

10

10

10

 

and a last one with

11

46

46c

67d (without text)

 

I cannot do this manually. Is there a way to do this in ArcGIS 10.1 ?

 

Furthermore I am wondering if it is possible to connect attribute tables with Excel tables without loading the Excel tables (csv) into ArcGIS?

 

Thanks!

0 Kudos
1 Solution

Accepted Solutions
DanPatterson_Retired
MVP Emeritus

lets have a look in slow motion....

>>> #lets have a look in verbose form

>>> a = "K1-10-67d (street)"

>>> c0 = a.split("-")[0]

>>> c1 = a.split("-")[1]

>>> c2all = a.split("-")

>>> c2a, c2b, c2c = c2all

>>> print "c0: ", c0

c0:  K1

>>> print "c1: ", c1

c1:  10

>>> print "c2all", c2all

c2all ['K1', '10', '67d (street)']

>>> print "c2c: ", c2c.split(" ")[0]

c2c:  67d

>>> # hmmmmm works lets try it all at once

>>> a = "K1-10-67d (street)"

>>> c0, c1, c2_a_b = a.split("-")

>>> c2_a, c2_b = c2_a_b.split(" ")

>>> print (" c0: {0}, c1: {1},  c2_a: {2}  c2_b: {3}".format(c0, c1, c2_a, c2_b))

c0: K1, c1: 10,  c2_a: 67d  c2_b: (street)

So my original stuff seems to be correct, check your syntax

View solution in original post

0 Kudos
7 Replies
RiyasDeen
Occasional Contributor III
  1. Add your new fields using Add Field ArcGIS Help 10.1
  2. Use Calculate field to calculate values ArcGIS Help 10.1
  3. VBScript Expressions for each field in order  Split(<<Your sourceField>>, "-")(0), Split(<<Your sourceField>>, "-")(1) and Trim(Mid(Split( [SURVEY_ID] , "-")(2), 1, InStr(Split( [SURVEY_ID] , "-")(2), "(") - 1))
  4. Use Join Field to join with excel sheets ArcGIS Help 10.1
0 Kudos
PV
by
New Contributor III

Thank you!
I am not quite sure how to handle the second and third field

Trim(Mid(Split( [SURVEY_ID] , "-")(2), 1 resp. the 3rd field InStr(Split( [SURVEY_ID] , "-")(2), "(") - 1))
I guess SURVEY_ID has to be replaced with the original sourcefield Name? . I see what trim and mid do but not what the (2), 1 stands for? Also i get an error when I try to run it in the field calculator..

0 Kudos
DanPatterson_Retired
MVP Emeritus

and for the pythonistas


>>> a = ['K1-10-11','K1-10-46','K1-10-46c','K1-10-67d (street)']
>>> for aval in a:
...  c0 = aval.split("-")[0]
...  c1 = aval.split("-")[1]
...  c2 = (aval.split("-")[2]).split(" ")[0]
...  print(" first: {0}  second: {1}  third: {2}".format(c0,c1,c2))
... 


first: K1  second: 10  third: 11
 first: K1  second: 10  third: 46
 first: K1  second: 10  third: 46c
 first: K1  second: 10  third: 67d
>>> # in arcmap field calculator using Python interpretor
>>> #  !sourceFld!.split("-")[0]  #for 1st field
>>> #  !sourceFld!.split("-")[1]  #for 2nd field
>>> #  (!sourceFld!.split("-")[2]).split(" ")[0]  #for 3rd field

0 Kudos
PV
by
New Contributor III

Thank you it seems to work! Allthough not when I have

a case with text like this K1-10-67d (street)

For the last two fields I get a 0 instead of a 10 and a 67d

I ve no Phyton experience at all (yet)

0 Kudos
DanPatterson_Retired
MVP Emeritus

lets have a look in slow motion....

>>> #lets have a look in verbose form

>>> a = "K1-10-67d (street)"

>>> c0 = a.split("-")[0]

>>> c1 = a.split("-")[1]

>>> c2all = a.split("-")

>>> c2a, c2b, c2c = c2all

>>> print "c0: ", c0

c0:  K1

>>> print "c1: ", c1

c1:  10

>>> print "c2all", c2all

c2all ['K1', '10', '67d (street)']

>>> print "c2c: ", c2c.split(" ")[0]

c2c:  67d

>>> # hmmmmm works lets try it all at once

>>> a = "K1-10-67d (street)"

>>> c0, c1, c2_a_b = a.split("-")

>>> c2_a, c2_b = c2_a_b.split(" ")

>>> print (" c0: {0}, c1: {1},  c2_a: {2}  c2_b: {3}".format(c0, c1, c2_a, c2_b))

c0: K1, c1: 10,  c2_a: 67d  c2_b: (street)

So my original stuff seems to be correct, check your syntax

0 Kudos
PV
by
New Contributor III

Seems to work (for most of the cases) when I change my field type to string! thanks!

0 Kudos
curtvprice
MVP Esteemed Contributor

So, again, here's the input data (text field name: "DFIELD")

K1-10-11

K1-10-46

K1-10-46c

K1-10-67d (street)

Here's how to do it with with arcpy.da.UpdateCursor.

The try/except just skips the row and prints an error message if you have data issues.

for f in ["F1", "F2", "F3"]:

    arcpy.AddField_management("table.dbf", f, "TEXT", "", "", 10)

with arcpy.da.UpdateCursor("table.dbf", ["DFIELD", "F1", "F2", "F3"]) as rows:

    for row in rows:

        try:

            L = row[0].split("-")

            row[1], row[2], row[3] = L[:3]

            rows.updateRow(row)

        except Exception as msg:

            print("{} | {}".format(row[0], msg))

0 Kudos