Discovering metadata and injecting it

Let's move to a use case a bit more elaborate than the previous one. We will continue working with sales data. In this case, we will work with an Excel file named sales_data.xls, which has a single sheet. There are several fields in this file, but we are only interested in the following: PRODUCTLINE, PRODUCTCODE, and QUANTITYORDERED. The problem is that the fields can be in any order in the Excel file. We will only know the order when we read the file.

In the same way as before, we need to create a template with missing data and then a Transformation that injects that data.

Let's start with the template. As we don't have the list of fields, we will fill the Fields grid with generic names—HEADER1, HEADER2, and so on. We have to select and keep only three fields. For this, we will use a Select Values step and leave the task of filling it to the Transformation that injects the missing data:

1. Create a Transformation.

2. Add a Microsoft Excel input step, and configure...