Tip : File path as variable in kettle.properties file to read excel files in Pentaho Kettle

In this post we will see how we can make file path as variable in kettle.properties file. 

Environment : 

OS Windows 7, Kettle ETL(PDI) CE 5.3, Excel files. 

Problem Statement : 

How to give file directory name as variable (PATH for files)

Solution  1 : By selecting the predefined variables from the drop down. 

${Internal.Transformation.Filename.Directory} 

Solution 2 : 

Another way with variable defined in kettle.properties file

1. Create a variable in kettle.properties file. 

    Location of the file :  C:\Users\sadakar.p\.kettle

2. Variable Name to give in kettle.properties file is (for eg) : PATH_FOR_DATA

    Value of the variable would be the path of the directory where files stores. 

  i.e., 

PATH_FOR_DATA=C:/Users/sadakar.p/sadakar drive/BI/Pentaho/1_Kettle/DWH_INHOUSE/Data/Education/

3. Now design transformation with Microsoft Excel input and table(or dummy step) step as shown in below image and press Control+ Space to get the available variables list in selected files tab(File/Directory). 

 

In this example we have to chose the PATH_FOR_DATA variable created in kettle.properties file. 

4. Test whether the data from all excel sheets is populating or not by clicking on preview button(one should get the field names for the first time with hard located path). 

NOTE : 

1) A restart of Kettle(spoon) is required once the variable is defined in kettle.properties file other wise the in the list of variables it will not populate. 

:-)