Pentaho Tools :

Pentaho C-Tools(CDE,CDF,CDA),Pentaho CE & EE Server,OLAP-Cubes,Analysis using Pivot4J, Saiku Analytics, Saiku Reporting, Ad-hoc Reporting using Interactive Reporting Tool,Dashboards,Reports using PRD, PDD,Data Integration using Kettle ETL,Data Mining usign WEKA,Integration of Servers with Databases,Mobile/iPad compatible Dashboards using Bootstrap Css,Drilldown dashboards,Interactive Dashboards

Tuesday, 19 May 2015

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

In this post we will see how we can make file path as variable in 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. 

Solution 2 : 
Another way with variable defined in file

1. Create a variable in file. 
    Location of the file :  C:\Users\sadakar.p\.kettle

2. Variable Name to give in file is (for eg) : PATH_FOR_DATA
    Value of the variable would be the path of the directory where files stores. 
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 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). 

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