CODEWITHSUNDEEP
excelpentahopentaho-spoon
ajburnett34
ajburnett34

Reputation: 37

Pentaho - Put Data in Any Excel Field

Excel Screenshot

I a new to Pentaho and i am trying to put data into an excel sheet in any specific cell i need to. I currently have table input but the data is not exactly aligned with my output excel template (Excel Writer). How can i choose where Pentaho inserts my data into? I have tried to use multiple transformations to write into each column but i do not know if there is a better way.

In the attached image you see several columns. I need to insert a count query into each column titled "Count", "Completed", "Pending".

Thank you!

Upvotes: 0

Views: 1445

Answers (2)

Cristian Curti
Cristian Curti

Reputation: 1054

Your problem is in iterating through the Columns, writing the data on the desired cells. You'll need to create a flow before the queries where you pass the column (A, B, C ..) as a variable, which you will use on the Excel Writer step.

You'll have to configure the Excel Write step to "overwrite existing cells" instead of "shift existing cells down" otherwise the step will just keep writing new rows on the table. Your trouble seems to be on pinpointing where the Excel writer will output the data. The content tab of the step should look something like this.. enter image description here

With this configuration, the excel writer will start at ${column} Variable (C, followed by whatever you need, this is where you iterate) and overwrite cell ${column}4, ${column}5 and so on.

Upvotes: 0

AlainD
AlainD

Reputation: 6356

The trick with PDI (and all ETL tools ion general) is to produce the output as you want and, when this is done, you drop the result in the Excel file. That way you avoid to target each data value to a specific cell.

In you case you need to produce a 16 column flow, with column containing the date, the ABP Enrolement Count, the ABP Enrolement Completed, the ABP Enrolement Pending, the APB reject Count,... And write this result to the Excel Writer.

In order to achieve this flow, you'll probably need to denormalize and group your input, but it is difficult to answer not knowing the format of your input data.

Upvotes: 0

Related Questions