Filling the Gaps in Excel file

This example demonstrates how to fill data gaps in an Excel file.

Question from the customer:

Currently, we are testing the Advanced ETL Processor tool and evaluating how we can use it best for our specific cases. Recently we have come across a problem:

Sometimes it is necessary to fill columns in Excel, only if a certain value, for example, the order number is given in the destination file.
In this very example, which you can see in the attachments, I would like to have AETLP check, if the order number in the destination file meets an order number in the source file.
And if so, I would like to have AETLP to fill the empty gaps, in this case, name and description for example, but only if they're empty.

Data example

Original forum post

The solution

The simplest way to transform the data is to use the Lookup transformation function. 

 Transformation

How it works

1. Lookup loads data from excel file
2. When the matched value is found lookup returns Name and Description fields values otherwise it returns nulls
3. "If Null or Empty string" transformation function fills the gaps: EG when Name is missing it takes value from the lookup otherwise it uses the original value 

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 example[Link]
• Create a new transformation and open the .ats file
• Double click on the Reader object and amend the source file path
• Double click on the Transformer object
• Double click on the Lookup object and amend the source file path
• Double click on the Writer object and amend the target file path
• Press the green arrow to run the transformation 

Note: That reader and writer point to the same file

Please contact us if you need help with transforming the data