Calculating new field values in modelbuilder using values in an excel file

Question

I have a shapefile with polygons with fields: "Name" and "Area". I have an excel file with fields "Name" and "CostPerSquareMetre". I would like to be able to output a new excel file with fields: "Name","Area" and "Cost". Cost would be calculated by multiplying the "Area" field by "CostPerSquareMetre" field where the "Name" fields match. Is this possible using the ModelBuilder?

• Mark Ireland: I imagine it is. Have you tried it? I think you'll get a better response if you show what you have tried and have a specific issue that we can help with. Or maybe if you can phrase the question better, like "what tool in ModelBuilder can I use to merge the data?"
• George of all trades: I can get excel data in and out using tools in "Conversion Tools > Excel" but I don't know which tool to use once the data is in ArcMap.

Answer 1

You can use the Add Join tool in the toolbox to join the excel table to the shapefile using the "Name" fields as the join fields.

Once you have the join, you'll need to make it permanent by using exporting the joined layer either by the Copy Features tool, or by right clicking on the shapefile layer> export.

Then you add a field called "cost" to the newly created dataset and calculate field using an expression:

!Area! * !CostPerSquareMetre!

You can do all of this in model builder using the following tools:

Make Table View (to make the excel table a layer)

Add Join (join table layer to Shapefile using "Name" as join field)

Copy Features (to make join permanent)

Add Field (add new "Cost" field)

Calculate Field (to calculate the cost per square metre)

Answer 2

If I understand this correctly:

1. First you should convert your excel table to .csv, .txt or .dbf format
2. Join the shapefile and tabular data using Join Field tool with "Name" as the common field
3. Calculate Field tool to compute "area * costpersquare metre"
4. Finally, you can either manually export the data as .dbf or use Copy Feature tool.