Creating efficient ArcGIS workflows using Excel and the command line

I have a confession to make: I never really used ArcGIS until 9.0 came out. To finish my Master's thesis in 2003-2005 (a bunch of modeling stuff), I relied on a combination of ArcInfo workstation scripts, ArcView 3.3 Avenue scripts, R scripts, and SAS scripts. At the time, I considered the 8.x series of ArcGIS to be a nice tool for making pretty maps, but since ESRI removed the ability to create simple geoprocessing scripts (except with VB...booo!), I felt crippled.

When I was hired as a GIS analyst for the Arizona Missing Linkages project in 2005, I had the rather daunting task of designing 8 linkages and writing 8 reports in one year. Conveniently enough, ESRI released ArcGIS 9.0 about a year prior, and with it created an amazing geoprocessing framework that allows you to go between Arctoolbox tools, Python scripts, Model Builder tools, and the command line.

What's so nice about the geoprocessing framework is that you don't have to be a scripting nerd to perform batch processing. When I'm too lazy to write a Python script, I have found that I can set up batches of ArcToolbox tools I want to run in Excel, copy and paste them into ArcGIS's command line, and let the tools run while I sleep, eat lunch, or do something else more productive.

If you will be conducting repeated analyses throughout a region, you might also find it helpful to look at the Arizona CorridorDesigner Toolbox to see one way of streamline the number of required parameters for each tool. Without too much fuss, this toolbox could be adapted for other species or regions.

Step 1: Set up one model run in ArcGIS's command line

In order to make sure I entered all of the correct parameters, I usually set up my first model run within the command line. Once you set up the parameters, copy and paste them into a text file. Note: I like to do most of my processing in ArcCatalog, because if I need to do GIS work while processing a bunch of data, I can still use ArcMap. If using the CorridorDesigner toolbox, I recommend always using ArcCatalog, because a couple of the tools crash in ArcMap.

Step 2: Set up model runs in Excel

Now that you have one model set up, open up Excel and paste the parameters so there is one parameter per cell across a row. If I am creating a batch of many models to run, I usually like to label the parameters in the first row so I can remember what each parameter is in each column.

While this step could be performed in a text editor like Notepad, there are several advantages to using Excel. Once you have one model set up, you can drag the values for each cell downward to easily fill in parameters for subsequent model runs. In the case above, I have a batch of ten habitat suitability model runs. All input and output data is stored in a similar folder structure, so I just copied the first model's parameters into the other nine models. To change the species name for these models, I just selected the row, and did a search-and-replace to replace tortoise with the new species name.

Once I changed the species name for each model run, I just had to enter the habitat factor weights for each species. You can download the spreadsheet to see how it looks after doing this.

Step 3: Copy and paste stuff from Excel into text editor

To use the command line in ArcGIS, each parameter must be separated by a space. Unfortunately, you can only save comma-separated or tab-delineated text files from Excel. One quick-and-dirty solution to this is to select all of the cells from Excel and paste them into a text editor such as Notepad (Note: I wouldn't recommend using Word for this.). You'll notice that if you copy and paste from Excel to Notepad, your parameters will automatically be separated by tabs.

To change the tabs to spaces, insert a tab at the bottom of your model runs in your text editor. Then select the tab, open up the replace tool, and paste the tab into the "Find what" box. Type in a single space in the "Replace what" box, and then click Replace All.

Now you have a set of 10 model runs which can be pasted into the command line. Here is a text file with example model runs to download.

Step 4: Copy and paste stuff into command line

Almost done. Now we just need to copy and paste the model runs from the text file back into the command line.

Step 5: Eat lunch while ArcGIS crunches data

As a final step, scroll to to the bottom of the model runs you just pasted into the command line and press enter. ArcGIS will run through all of the models consecutively, and if there is a problem with one, it will report any errors and move on to the next model. If running a large group of models, it is important to scroll through the output to make sure there were no problems.