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?
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)
If I understand this correctly:
- First you should convert your excel table to .csv, .txt or .dbf format
- Join the shapefile and tabular data using Join Field tool with "Name" as the common field
- Calculate Field tool to compute "area * costpersquare metre"
- Finally, you can either manually export the data as .dbf or use Copy Feature tool.