excelpentahopdispoon

Pentaho Excel file creation with file name from field or other variable

I was able to create csv files from table input. Here i was able to specify file name from field.

Now i need to change the file format to Excel. I don't see any option to use field as filename. I have some data with multiple fields - one field in that is a Location name. Previously i was creating the csv file with Table Input step. Named the csv file with Location field. Now they do not want a csv file, but an Excel file. Here is where the problem comes

Current flow: enter image description here

  1. I fetch unique locations from Raw data enter image description here

  2. I then pass on this location column to the next transformation for fetching records with that location and create a file for the records in each location enter image description here Here i use the locationfilename value as the filename for CSV file.

Solution

  • Not knowing what your existing solution does, or why, this is the best way I can think of solving your problem, given you want exactly the same data in a different file format:

    1. Create a new job. In that job start a new transformation called 'get locations' your 'main' job with the get locations, and set variable and make excel

    2. In this 'get locations' transformation as below, have two steps a) Table input, with a query like 'select distinct locations from data' and b) copy rows to result. This should give you a list of your unique locations

    Your first transformation, getting the locations with a select distinct

    1. Create another new job called 'set variable and make excel'. This job will be run after your 'get locations' transformation, tick the 'execute once for every row' checkbox

    2. In the new job, you'll have two transformations. set variable, and make excel. The second 'sub' job

    3. The set variable transformation has two steps, first 'gets rows from results' then 'set variables'. In set variables, set a new variable called 'out_filename' equal to the field 'Location'

    setting the filename variable steps

    1. In the 'make excel' transformation, you have two steps, table input, and microsoft excel output making the excel files
    2. In the table input, run a query that uses your location variable (use the checkbox replace variables in script) eg. select * from data where Location = ${out_filename} Example settings for table input
    3. In the 'microsoft excel output' make the filename c:\yourPath\${out_filename}

    Run the main job, this should work.