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

Question

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:

1. I fetch unique locations from Raw data

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 Here i use the locationfilename value as the filename for CSV file.

Answer 1

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'

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

3. 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

4. In the new job, you'll have two transformations. set variable, and make excel.

5. 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'

6. In the 'make excel' transformation, you have two steps, table input, and microsoft excel output
7. 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}
8. In the 'microsoft excel output' make the filename c:\yourPath\${out_filename}

Run the main job, this should work.