0026 Insert records into the 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 the following file.
Source file
| Source row |
|---|
| HEAD01,Line1 |
| HEAD01,Line2 |
| HEAD02,Line4 |
| HEAD02,Line5 |
| HEAD03,Line3 |
I want to create a file with each header inserted before its matching lines.
Desired output
| Output row |
|---|
| HEAD01 |
| Line1 |
| Line2 |
| HEAD02 |
| Line4 |
| Line5 |
| HEAD03 |
| Line3 |
Answer
Yes. This example is based on 0025 Merge Header and Lines file, then adds the missing header rows into the output flow.
How it works
- The first transformation adds the header number field.
- The next transformations add RN fields to the data: 1 for headers and 2 for lines.
- The data is merged using the Union transformation.
- The data is sorted using F2 and RN fields.
- The result is written into the target file. It is the kind of small formatting job that looks harmless until someone has to do it by hand every week.
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.