# Split Attribute field

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

```