How to batch update model attributes using Excel?

  • You received some updates to the model as a GIS layer or a table.
  • Find these elements in the model to be updated.
  • Then update the attributes with the new values.

Measure twice, cut once

What I found works best in most situation is to use Excel as the main tool, to interactively copy and paste the values from the Excel table to the model table. The major benefits of this approach is that it is a more visual and interactive process, I have more opportunities to identify errors and therefore feel more confident about the final results. This is a step you don’t want to go fast.

The Excel Approach

This approach centers around Excel as the place where most of the actions happen.

  • create a list of elements to be updated in the Model
  • rebuild the list in Excel with before and after values
  • review the updates
  • copy and paste the new values back to the model

Build a list

In XPSWMM, you can build a full list of model elements using the XPTables.

Compare before vs after

Once the model data is in Excel, we can match the model element to the external data using the vlookup function. If you are not an expert of this function yet, become one.

  • the first column and the 2nd column were copied from the model table
  • the 3rd and 4th columns were values extracted from the external data table

Copy and paste

The last step is to paste the updated values back to the model. To avoid introducing errors, we would like to keep the changes to only the records with changes. And this will require filtering the elements in the model before pasting the values.

  1. select the description column
  2. sort it
  3. select the rows with “update”
  4. select the objects in the map
  5. filter only the rows selected

Other common issues

When matching the data, there are a few common issues.

  • Excel might automatically format data. 3–14 for mar-14, 1235–1235.0, etc. In such situations, you need to convert the value to a text with the correct format so that vlookup can correctly match the record. See this link for some tips.
  • Date can be tricky to handle in Excel. Use custom format for better control.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store