Concatenate Multi-Values from Single Field and Insert to Single Cell

1452
22
01-28-2021 02:28 PM
EricMahaffey1
Occasional Contributor

I'm building a geoprocessing model that iterates through features in one f-class and spatially selects features from another f-class that are within a certain distance of each feature.  I want to pull values from one f-class to the other.  The model essentially does a select features by location, and from that selection I would like to pull values within a single field, concatenate them together as a string and insert that value into the other f-class field in a single cell. I believe that I need to use some cursors within ArcPy to do this (SearchCursor & InsertCursor) and embed the Python script within the model, but I'm not entirely sure. So basically Table1 FIELD_A has two values "1234" and "5678". I want to take those two values and insert them into a single cell within Table2 FIELD_B so that the value looks something like "Value 1: 1234 to Value 2: 5678". Any assistance would be appreciated.

0 Kudos
22 Replies
JoeBorgione
MVP Esteemed Contributor

Maybe you can clarify what the objective is as well as you data structure.  Are you randomly inserting the values from Table1 into Table2 or is there some sort of correlation between the two?  Does Table2 already exist (update cursor) or are you truly inserting new records (insert cursor).

You mention that Table1 FieldA has two values " 1234" and "5678" ; if both of those strings of characters are within a single field, that field has one value: "1234 5678".   A few more details would really help to provide a solution.

That should just about do it....
EricMahaffey1
Occasional Contributor

HI Joe.  The values that I'm trying to take from Table1 are the results of a spatial query.  In this case two separate values ("1234" and "5678") from two cells in the same field of the selected features.  I'm trying to insert both of those values into a single cell within Table2 ("Value 1: 1234 to Value 2: 5678").  Table2 already exists and the target field is there.  I hope this answers your questions.  Thanks.

0 Kudos
JoeBorgione
MVP Esteemed Contributor

Okay; I think it's a nomenclature issue for me.  You have two records in Table1, and the respective values in the Field need to be returned to one field in Table2.  I'm not much of a model builder, but in a straight python script I can see how you would do that.

That should just about do it....
0 Kudos
JeffK
by MVP Regular Contributor
MVP Regular Contributor

Your current process is passing a featureclass with a selected feature to the script.  using the selected feature as input, you can use a search cursor to access the features data. 

inTable = input from your model, which will be Table1
destTable = input from your model, which will be Table2

with arcpy.da.SearchCursor(inTable , Field_A) as cur:
    for row in cur:
        resString = 'Value 1: {} Value 2: {}'.format(row[0].split(' ')[0], row[0].split(' ')[1])
        # insert into target FC
        arcpy.da.InsertCursor(Table2, Field_B) as cur:
             cur.insertRow(resString)

 

You will need to set this script up as a script tool, then drag and drop that tool it into your model and wire up the input parameters.

EricMahaffey1
Occasional Contributor

Thanks for the quick reply Jeff.  I'll give this a shot and let you know how it turns out.  Appreciate the help!

0 Kudos
EricMahaffey1
Occasional Contributor

Jeff,

I setup two stand alone f-classes to emulate the selection results from within the model. I figured if I can get the script to function with those, then I can focus on embedding it within the model next. When I first started running the code I was getting an invalid syntax error within

arcpy.da.InsertCursor(Table2, Field_B) as cur:

I moved things around to appear like this:

EricMahaffey1_1-1611951722545.png

, but now I'm getting a "IndexError: list index out of range" error. Any ideas?

0 Kudos
JeffK
by MVP Regular Contributor
MVP Regular Contributor

Insert cursor uses a tuple of values for the insertRow so you will need to wrap that resString string with (), I often forget that aspect of insertcursor's and I see that I missed it in the previous example. 

Looking at your image, printing the cursor will just print the object so its not really doing anything.  Try this:

 

 

      print row[0]  #should be the '1234 5678'?
      resString = 'Value 1: {} Value 2: {}'.format(row[0].split(' ')[0], row[0].split(' ')[1])
      print resString
      insertcursor.insertRow((resString))

 

 

If this doesn't work, share the print results with us so we get an idea of the data value/format.

EricMahaffey1
Occasional Contributor

Thanks Jeff.  I changed the code per your last suggestion.  I'm still getting the "list index out of range".  The print results only show one value, but I think it should show two.  Which from I remember might be causing the index to be out of range in "reString"

EricMahaffey1_0-1611954208680.png

 

0 Kudos
JeffK
by MVP Regular Contributor
MVP Regular Contributor

It would have failed on the insertRow eventually...  It failing on the reString tells me that the data in 'MILE_TEXT' field is not "1234 5678" and it is not splitting into two ('1234', '5678'). 

From the image, it printed 337.  Do you have the right cell for the cursor, or are there mixed values like 337 and 333 789?  If it is mixed like that, you will need a conditional to skip the split if there is only one.

if ' ' in row[0]:
   resString = split code
else:
   resString = 'Value 1: {}'.format(row[0])

insertCursor.insertRow((resString))