0016 Dynamically create excel file based on field value

Create separate Excel files from a field value

This example shows how to use a field value as part of the Excel file name. It is useful when one source file needs to be split into separate output files by customer, country, department, or any other grouping field. One file per group is tidy. One file per accidental typo is less tidy, so check the source values first.

Data flow

The transformation reads the source data, sorts it by the grouping field, and writes records into dynamically named Excel files.

Run the example

• Download and install Advanced ETL Processor [Link]
• Download and unzip the example [Link]
• Create a new transformation and open the .ats file.
• Double-click the Reader object and amend the source file path.
• Double-click the Writer object and amend the target file path, leaving the file-name calculation intact.
• Double-click the Sorter object and amend the buffer path.
• Run the transformation by pressing the green arrow.

Transformation logic

The transformation keeps the output grouped so the writer can detect when the source value changes and create the next file.

File name calculation

The file name contains a calculation that inserts the selected field value into the output path. Keep the calculation intact when changing the folder or base file name.

Note: The Source Changed function forces the writer to create a new file when the grouped field value changes.

Calculation inside the file name

The dynamic part of the file name is stored directly inside the writer path, so each grouped value produces a separate Excel file.