Joining attribute table to excel sheet

Question

Hello guys,

I want to add a column in my dbase file which will have geometry data (Area)  by joining the dbase file with attribute table.

I have tried to use "FARMER_UNI" from my dbase file to join with "FARMER_UNI" from my attribute table, but the result were not what i wanted.below is an attribute table,

And here is the results i get,

So what i want is the "name" field to be the same as "GPS_Coordi" field, but i cant use those field to do the joining without doing some changes because there is some spelling error such as removing the middle values(12345/6578/ST/1), adding "/" after ST, changing the lower case to upper case(st to ST), and removing spaces between values.

With thanks!

Kelvin.

WesMiller · Answer

Is that all you have to worry about(because I'm guessing there's more)

Open your python interpretor and play with your various values till you start getting the desired result

>>> a = '181073/23792/ST/1'

>>> b = a.split('/')

>>> b[1]

'23792'

Below is a list of your examples and they all produce the same result.

a = ['181073/23792/ST/1',"181073/23792/st1","181073/23792/st","1,181073/23792/ST/01"]

#I'm walking through the list using the for statement.

#In field calculator you will not need to use for, you'll need each = str(!yourfield!)

for each in a:

#The if statement is testing to see if there is a comma in your each variable

if ',' in each:

#So if there is a comma we will split the each variable into pieces.

#The number of pieces are based on the number of commas

each = each.split(',')[-1]

#Now we'll split either what's left from the if statement or the original each value

each = each.split('/')[0]

#Now we'll add the proper ending back

each = each + '/ST/1'

#Finally we will print the value so we can see it.

#In the field calculator you'll use the return statement

#return each

print each

DanPatterson_Retired · Answer

So if this is a different question you should mark the answer that provide a solution correct.. then start a new question, otherwise the thread will become a long thread of needs that bear no relationship to the initial thread title.

KelvinMwakomo · Answer

Thanks Dan,

But what i need now is to create a field both in a Table and dbase file which can have the values of "name" field,without the middle number eg.(181073/ST/1) instead of (181073/23792/ST/1), Also for the cases like 181073/23792/st1,181073/23792/st 1,181073/23792/ST/01 to become 181073/ST/1.

Kelvin.