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 Name parameter of the Table To Table tool. The output of the Table To Table tool is connected to the Collect Values tool, which collects the output of each iteration and outputs 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 Table To Table tool.
- Click inside the model and type Table To Table to open the Add Tools To Model dialog box.
- Search for the Table To Table tool.
- Double-click the Table To Table tool and add it to the model.
Specify the tool parameters and connect the tools
After adding tools to the model, you need to 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.
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 Table To Table tool to open the tool's dialog box. For the Input Rows parameter, click the drop-down arrow and select Table. For the Output Name 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 Table To 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
- 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.