The Iterate Tables tool iterates over tables in a workspace.
In this workflow, you'll create a model to iterate over each sheet in the Excel file (five sheets) and convert the sheets to file geodatabase tables with the same name as the Excel sheet. The iterator output parameter, Name, will be used as an inline variable in the Output Table parameter of the Export Table tool. The output of the Export Table tool is connected to the Collect Values tool, which collects the output of each iteration and creates a multivalue output.
Open the project
Download the project to use in this workflow. The project includes input data, an output geodatabase, and a completed model.
- Open the Work with Iterate Tables overview page.
- Click Download on the right side of the page.
- Right-click the downloaded IterateTables.zip file and extract it to a convenient location, such as C:\Temp.
- In the extracted folder, IterateTables, double-click the ArcGIS Pro project file, IterateTables.aprx.
- Explore the data in the Catalog pane, and click the Project tab if necessary. Browse to Folders > IterateTables.
The IterateTables folder contains an Excel file that will be used as the input for the model.
The outputs of the model will be written to the ScratchTable.gdb geodatabase.
To create a model and add tools to the model, complete the following steps:
- In the Catalog pane, browse to Folders > IterateTables > MyProjectTools.
- Right-click MyProjectTools and click New. Select Model to create and open a model.
- Add the Iterate Tables tool.
- Click the ModelBuilder tab.
- In the Insert group, click Iterators .
- In the Iterators list, click Iterate Tables.
The Iterate Tables iterator is added to the model. The iterator is gray since no parameter values have been provided.
- Add the Collect Values tool.
- Click the ModelBuilder tab.
- In the Insert group, click Utilities .
- In the Utilities list, click the Collect Values tool.
The Collect Values tool is added to the model.
- Add the Export Table tool.
- Click inside the model and type Export Table to open the Add Tools To Model dialog box.
- Search for the Export Table tool.
- Double-click the Export Table tool and add it to the model.
Specify the tool parameters and connect the tools
After adding tools to the model, you must specify the parameters for each tool.
- Double-click the Iterate Tables tool to open the iterator's dialog box.
- In the Catalog pane, browse to Folders > IterateTables.
- Drag the DataSheets.xlsx file to the Workspace parameter.
- Leave the remaining parameters blank and click OK.
The workspace is added as a model variable to the model (dark blue oval) and changes the color of the iterator, indicating that the required parameters have been supplied and it is ready to run.
- Right-click the DataSheets.xlsx variable (dark blue oval) and click Rename. Type Excel Sheets for a new name. Using the same steps, rename the iterator table output (green oval) to Table.
It is recommended that you rename the input and output variable names to more meaningful names instead of the default variable name.
- Double-click the Export Table tool to open the tool's dialog box. For the Input Table parameter, click the drop-down arrow and select the Table model variable. For the Output Table parameter, type %Name%. Leave the remaining parameter default settings and click OK to close the dialog box.
When the model is run, %Name%, which is the name of the output parameter of the iterator (light blue oval), is replaced by the value of the variable, for example, the sheet name Birds2007, Birds2008, and so on.
- Hover over the Export Table tool output variable (green oval). Click and drag to draw a connector line to the Collect Values tool. Release the mouse and click Input Value on the pop-up menu.
- Rename all the model variables as shown in the finished model.
- To arrange the model elements, click the Auto Layout button on the ModelBuilder tab and click the Fit To Window button to maximize the layout to the size of your model window.
Run the model
Complete the following steps to run the model:
- Right-click the output of the Collect Values tool, and click Add To Display.
The outputs will be added to the map after the model is run.
- Click the Save button on the ModelBuilder tab to save the model.
- Click the Run button on the ModelBuilder tab to run the model.
The model iterates over the five sheets in the Excel file and creates five tables in the ScratchTable.gdb geodatabase.
- Close the model and ArcGIS Pro.