topic Re: Export Output of Collect Values Tool in Model Builder in Geoprocessing Questions

Export Output of Collect Values Tool in Model Builder

ShinoRah — Sat, 23 Mar 2013 18:48:17 GMT

Hi There,

I am new to model builder, I have built a model which calculates mean and minimum distance between features. It also counts how many of those near features fall within a given 'buffer' around a feature.

The user can select upto 6 buffer (/radius) lengths. The tool will run and the final result is an excel file. The excel file includes how many features fell within that given buffer but does not include the buffer distances that the user manually input in the beginning.

So for those forgetful users, I'd like to add a column in the excel file that shows the buffer length. I have added an image to this post, which shows visually what I want, because I'm not sure how coherent this explanation is.

Hopefully there is someone out there who can help me. I've tried the merge, union and calculate values tool to somehow bring it all together, but no luck.

Thanks in advance!!

Re: Export Output of Collect Values Tool in Model Builder

curtvprice — Fri, 10 Dec 2021 23:04:07 GMT

I've tried the merge, union and calculate values tool to somehow bring it all together, but no luck.

I think Calculate Value is the tool you need to use.

Make the output for your merge (right before you export to Excel, renamed "OutputTable" below) a precondition to Calculate Value - then have your calculate value tool look something like this:

Expression:

AddRadiusField(r"%OutputTable%","%RadiusColumns%")

Code block:

import os
import arcpy
def RadiusTable(rc,tbl):
  arcpy.AddField(tbl,"RADIUS","FLOAT")
  rc = ";".split(rc)
  Rows = arcpy.UpdateCursor(tbl)
  k = 0
  for Row in Rows:
    Row.setValue("RADIUS",rc)
    k += 1
  del Row, Row
  return tbl

Data Type:

table

Then connect its output to your export to Excel tool.

Re: Export Output of Collect Values Tool in Model Builder

ShinoRah — Mon, 25 Mar 2013 13:50:47 GMT

Expression:

AddRadiusField(r"%OutputTable%","%RadiusColumns%")

Hi Curtis,

Thank you for your response. I have included another photo to show how I am trying to incorporate your great advice.

After reading up on how inline model variables and iterators work, I am thinking I need to change all the blue ovals ("Seach Radius") to "RadiusColumns" as defined in your expression.

But I am unable to duplicate this label since model builder only allows unique names.

Also, Following your suggestions would allow me to delete the 'collect values' tool at the top, yes?

Thanks again Curtis,

Shehin

Re: Export Output of Collect Values Tool in Model Builder

curtvprice — Mon, 25 Mar 2013 15:24:16 GMT

After reading up on how inline model variables and iterators work, I am thinking I need to change all the blue ovals ("Seach Radius") to "RadiusColumns" as defined in your expression.

But I am unable to duplicate this label since model builder only allows unique names.

Also, Following your suggestions would allow me to delete the 'collect values' tool at the top, yes?

No, you need to set the Collect Values output (named RadiusColumn) as a precondition to the Calculate Value tool. It is read by Calculate Value as a ";" delimited string, hence the "split" to convert it into a list lf values. The Collect Values is not an iterator, it will just collect those values in one step.

The output of the Calculate value is a table, it should be the input for your Export Table To MS Excel tool.

The first input should be the table that comes out of your other processing (the one with the automatic name that I suggested renaming). I'm assuming in my calc value code (you should check) that the table rows are coming out in the correct order to match the order of the values in RadiusColumns.

Thanks again Curtis,

Hope this helps!

Re: Export Output of Collect Values Tool in Model Builder

ShinoRah — Fri, 10 Dec 2021 23:04:10 GMT

Expression:

AddRadiusField(r"%OutputTable%","%RadiusColumns%")

Code block:


Code:
import os
import arcpy
def RadiusTable(rc,tbl):
  arcpy.AddField(tbl,"RADIUS","FLOAT")
  rc = ";".split(rc)
  Rows = arcpy.UpdateCursor(tbl)
  k = 0
  for Row in Rows:
    Row.setValue("RADIUS",rc)
    k += 1
  del Row, Row
  return tbl

Data Type:

table

Hi again Curtis,

The code block you so graciously provided for me,

I'm just wondering if I need to define 'AddRadiusField' somewhere in the model?

Reason being, I get this error when trying to run:

ERROR 000539: Error running expression:
AddRadiusField(r"Z:\Scratch\1303\130114_temp1.dbf","%RadiusColumns%")
<type 'exceptions.NameError'>: name 'AddRadiusField' is not definedFailed to execute (Calculate Value).
Failed at Mon Mar 25 12:07:18 2013 (Elapsed Time: 8.00 seconds)
Failed to execute (130117).
Failed at Mon Mar 25 12:07:24 2013 (Elapsed Time: 5 minutes 30 seconds)

Thanks again!!

Re: Export Output of Collect Values Tool in Model Builder

curtvprice — Fri, 10 Dec 2021 23:04:13 GMT

I get this error when trying to run:

ERROR 000539: Error running expression:
AddRadiusField(r"Z:\Scratch\1303\130114_temp1.dbf","%RadiusColumns%")
<type 'exceptions.NameError'>: name 'AddRadiusField' is not definedFailed to execute (Calculate Value).

I'm sorry, I made a mistake in my post - the function names need to match.

One more thing, those two inputs should be preconditions to the Calculate Value tool so you are sure they have been calculated before the Calculate Value tool runs.

Calculate Value parameters:

Expression:

RadiusTable(r"%OutputTable%","%RadiusColumns%")

Code block:

import os
import arcpy
def RadiusTable(rc,tbl):
  arcpy.AddField_management(tbl,"RADIUS","FLOAT")
  rc = ";".split(rc)
  Rows = arcpy.UpdateCursor(tbl)
  k = 0
  for Row in Rows:
    Row.setValue("RADIUS",rc)
    k += 1
  del Row, Row
  return tbl

Data Type:

Table

Re: Export Output of Collect Values Tool in Model Builder

ShinoRah — Tue, 26 Mar 2013 19:48:35 GMT

Hi again Curtis,

Thanks for the corrected code,

I have a python specific error when I incorporate your suggestions, I am not at all familiar with python and so a little overwhelmed with this attached error message,

Perhaps you can point in me in the direction I would take to begin solving this error?

Thanks!

Re: Export Output of Collect Values Tool in Model Builder

curtvprice — Fri, 10 Dec 2021 23:04:16 GMT

I had the parameters in the wrong order, sorry. The parameters are passed in the order they are listed.

Here's my fix:

Calculate Value parameters:

Expression:

RadiusTable(r"%OutputTable%","%RadiusColumns%")

Code block:

import os
import arcpy
def RadiusTable(tbl,rc):
  arcpy.AddField_management(tbl,"RADIUS","FLOAT")
  rc = ";".split(rc)
  Rows = arcpy.UpdateCursor(tbl)
  k = 0
  for Row in Rows:
    Row.setValue("RADIUS",rc)
    k += 1
  del Row, Row
  return tbl

Data Type:

Table

Re: Export Output of Collect Values Tool in Model Builder

ShinoRah — Thu, 28 Mar 2013 14:52:47 GMT

Hi Curtis,

Thank you for your fix, I apologizefor continuing to bother you,

I am getting an 'invalid parameters' error again. I've tried to investigate what python is doing here but I'm not sure why this error is occuring.

I've attached the error and the model image once again.

Perhaps you could point me in the right direction?

Thanks again,

Re: Export Output of Collect Values Tool in Model Builder

curtvprice — Thu, 28 Mar 2013 15:37:14 GMT

You need to use preconditions to control the order of processing. Your merge must run first before the Calculate Value so the "Output Table" dbf will have been created before the CV starts.

The output of your Calculate Value is a table -- which you then want to provide as input to your export to Excel function.

Hope this helps.

[ATTACH=CONFIG]23043[/ATTACH]

Re: Export Output of Collect Values Tool in Model Builder

ShinoRah — Thu, 28 Mar 2013 20:55:40 GMT

Hi Curtis,

Is it possible to have many input tables for the "Export table to MS Excel" tool?

From what I've read, I don't think this is possible, and when I tried to connect the Radius output to it, the connection with the 'output table' automatically disappears and many of the tools go blank.

Perhaps there is a similair tool to 'export table to MS excel' that allows multiple input?

Or maybe there is a way I could do this where I continue trying to connect the radius output to the merge tool, as I had initially?

Any thoughts you have on this will be very much appreciated!!

Thanks!

Re: Export Output of Collect Values Tool in Model Builder

curtvprice — Fri, 29 Mar 2013 01:44:16 GMT

I think you're missing something.

The Calculate Value tool I wrote for you takes the output of the Merge (the precondition connect just makes the merge happens first) and modifies that table. The CV output should then go to the your export tool (make sure it is set to type TABLE so it will be accepted by the tool!) The merge output should not be connected to your export tool.

Re: Export Output of Collect Values Tool in Model Builder

ShinoRah — Mon, 01 Apr 2013 16:43:10 GMT

Hi Curtis,

Thanks again for your response. I have made the following changes:

I have diconnected the merge output from the export to excel tool and replaced it with the output of the CV tool. Now the output of the CV tool is the input to the Export to Excel.

Under the 'Fields to Export' (in the export to excel tool), I have no options as I did when the merge tool was connected.

Not having a selection for this field is not allowed by the tool and so the tool remains blank.

Just wondering how I can fix that? Am I supposed to have fields in this 'Radius' table?

Thanks agian,

Re: Export Output of Collect Values Tool in Model Builder

curtvprice — Mon, 01 Apr 2013 23:26:07 GMT

It appears the export to excel tool needs validation that Calculate Value can't provide.

For the validation to work, connect the Merge output to Add Field and add the RADIUS field. Connect the output of Add Field to your export tool. This way the export tool will be aware of your new field.

But you still want to populate that RADIUS field with values, so:

Connect the output of Add Field to the Calculate Value tool as a precondition, and the CV's output to the export tool as a precondition.

I'm not sure this will all work, but it's worth a try.

Re: Export Output of Collect Values Tool in Model Builder

ShinoRah — Mon, 08 Apr 2013 19:35:54 GMT

Hi again,

Ok I think I am getting close thanks to all of your help!

Any clue what this error means ..

ERROR 000539: Error running expression: RadiusTable(r"Z:\Scratch\srah\130114_temp1.dbf","'600 Meters';'750 Meters';'1000 Meters';'1500 Meters';'2000 Meters';'2500 Meters'") <class 'arcgisscripting.ExecuteError'>: Failed to execute. Parameters are not valid.

Thanks!