0

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!

ajburnett34
  • 27
  • 1
  • 10

2 Answers2

0

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.

AlainD
  • 6,187
  • 3
  • 17
  • 31
  • Thank you so much for the reply alain. I basically have a count query for each column. I was seeing if maybe having a table input with each count query and merging them with an excel writer as output. Would that work? – ajburnett34 Jun 14 '18 at 13:53
0

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.

Cristian Curti
  • 1,004
  • 1
  • 7
  • 13
  • That should be the accepted answer. However, I am still wondering if the requester knows how to produce the flow, that is if he has to denormalize the data first. Maybe we get that 5 line example finally. – AlainD Jun 18 '18 at 06:52