0025 Merge Header and Lines file
A practical Advanced ETL Processor example for building, testing, and adapting one automation workflow.
Use this example to build one working automation package, test it safely, and then adapt it to your own files, folders, or data rules. Start small. Automation is helpful; automated mistakes are just faster mistakes.
Customer question
Question from the customer
I have two data files: one header file and one lines file.
Headers file
| Header row |
|---|
| HEAD1;01 |
| HEAD2;02 |
| HEAD3;03 |
Lines file
| Line row |
|---|
| Line1;01 |
| Line2;01 |
| Line3;03 |
| Line4;02 |
| Line5;02 |
Can I merge them so the output writes each header first, followed by its matching lines?
Answer
Yes. Use two readers, add row-type fields, merge the streams, then sort the result so each header appears before its matching lines.
Transformation
How it works
- Transformations add RN fields to the data: 1 for headers and 2 for lines.
- The header and line datasets are merged using the Union transformation.
- The merged data is sorted by F2 and RN fields.
- The result is written into the target file, with the headers and lines in the correct order. Less glamorous than a dashboard, but much more useful when the receiving system is picky.
To view the example follow the steps below
- 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 Transformer object
- Double-click the Lookup object and amend the source file path
- Double-click the Writer object and amend the target file path
- Press the green arrow to run the transformation.
Related Advanced ETL Processor resources
For more automation examples, review the Advanced ETL Processor tutorials, read the WIKI, or download the Advanced ETL Processor Enterprise trial.