0023 Filling the gaps in Excel 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.
Question from the customer
We are testing Advanced ETL Processor and working through a specific Excel loading case.
Sometimes we need to fill columns in Excel only when a certain value, for example the order number, is present in the destination file.
Can AETLP check whether the order number in the destination file matches an order number in the source file, then fill the empty gaps for fields such as name and description only when those fields are empty?
Data example
Answer
Yes. Use the Lookup transformation function to find the matching order number and return the missing values only when the destination fields are empty.
Transformation
How it works
- Lookup loads data from the Excel file.
- When a matching value is found, Lookup returns the Name and Description field values. Otherwise, it returns nulls.
- The "If Null or Empty string" transformation function fills the gaps. When Name is missing, it takes the value from the lookup. Otherwise, it keeps the original value. Boring, reliable, and much better than manual spreadsheet archaeology.
Lookup mapping: input fields
Lookup mapping: output fields
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.
Note: The reader and writer point to the same file.
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.