This tutorial shows how to rename all Excel sheets in a directory automatically in Advanced ETL Processor. Use it when incoming workbooks have inconsistent sheet names and the downstream process expects one fixed name.
What You Will Learn
- Loop through Excel files in a directory.
- Find the current sheet name in each workbook.
- Rename the sheet to the expected target name.
- Use related Excel actions when the workflow needs extra checks.
Before You Start
Use a test repository or safe sample data while following the tutorial. Production jobs are a poor place to experiment.
Rename Inconsistent Excel Sheets
This video was created to help one customer deal with inconsistent Excel sheet names. The file format was almost standard, which is another way of saying it was not standard at all.
Problem Description
- Excel files are received via email.
- Each file contains only one sheet.
- Sheet names vary.
- The sheet must be renamed to "DATA".
Solution
Since each file has only one sheet, the simplest approach is to save list of sheets into a file, store it in a variable, and use this variable as Old Sheet parameter.
Related Excel Actions
The related package actions documented in the wiki include Check Excel File, Read Excel Cell Value, Add Sheet, Delete Sheet, Keep Sheet, Rename Sheet, Set Active Sheet, Check if Sheet Exists, and List Sheets.
Need Advanced ETL Processor First?
Download the Advanced ETL Processor trial before following the tutorial, or review Enterprise if you need scheduled unattended execution.