This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Suppose that your spreadsheet has multiple sheets with the same structure and you would like to read several sheets into your module at once. Your first reaction might be to use one Input tool per sheet, followed by a Union tool to bring all of the data together. In this case, the preferred alternative is to use the Dynamic Input tool.
In this example, we have demographic data for 5 states, where data for one state is in each of five sheets inside of an Excel file. In this scenario, we would like to input the data from only 3 of the five sheets (Alaska, Arizona, and California).
We can use the Text Input tool to specify the names of the three sheets, see the following image. We then connect to a Dynamic Input tool, which we configure to use the Sheet names that we have specified in the Text Input tool, as the means of updating the “File/Table Name” that the Dynamic Input tool will be importing.
Dynamic Input Tool configuration
From the Input tool configuration, browse to the Excel file and select one of the sheets or tabs, this selection will serve as a placeholder. Click on the “Modify SQL Query” radio button for the Table or Query, and then on the right side of the configuration window click the drop-down button that says "Add" and select the option that says "Replace a Specific String".
The modular window that pops up should have the statement: SELECT * FROM `Alabama$` under Text to Replace: and the field from your Text Input tool should be populated under Replacement Field:
When the module runs, the ‘Alabama$’ text will be replaced with the table names that were specified in the Text Input tool.
Now, select the radio button for “Read a List of Files”; for the “Field” selection dropdown, select the field name that was created in the Text Input tool (in which the sheet names were typed), and set the “Action” to Change File/Table Name.
After running the module, you will notice that records from the three different sheets will be populated in the table view of your Browse. A great option to take advantage of in this process is Option #5 “Output File Name as Field”, which allows you to append the full file path or file name as a field for every single record. This is a great means of allowing you to quickly group-by and organize your data according to the sheet from which it was sourced.