Split Attribute field

2691
7
10-09-2014 02:19 AM
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!

Tags (4)
1 Solution

Accepted Solutions
MVP Esteemed Contributor

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

7 Replies
Regular Contributor
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
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..

MVP Esteemed Contributor

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

MVP Esteemed Contributor

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

by
New Contributor III

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

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

```