Getting the value of specific cells in an Excel file

One of the good things about Excel files is that they give you freedom to write anywhere in the sheets, which sometimes is good if you want to prioritize the look and feel. However, that could cause you troubles when it's time to automatically process the data in those files. Suppose that you have an Excel file with values for a couple of variables you'd like to set, as shown in the following screenshot:

In this example, you want to set values for three variables: Year, ProductLine, and Origin. The problem is that you don't know where in the sheet that table is. It can be anywhere, near the upper left corner of the sheet. As you cannot ask Kettle to scan somewhere near the upper next corner, you will learn in this recipe how to get that data with a simple transformation.

Getting ready

Create an Excel file with the preceding table. Feel free to write the values anywhere within the first rows and columns, so long as the labels and values are...