Pentaho

 View Only

 Write multiple tables to separate Excel sheets

  • Pentaho
  • Kettle
  • Pentaho
  • Pentaho Data Integration PDI
Phil DeGuzman's profile image
Phil DeGuzman posted 02-06-2019 22:25

I have several tables in a Postgres database (Order Data, Duty Rates, VAT Rates, Organization Lookup, etc.). I want to export each of these to an Excel file which I will use later for analysis. I have been trying all day to do this in one transform, but there have been all kinds of Java errors. Do I need to have a separate transform for each table? and can I write multiple sheets to one excel file? I know the Excel Writer configuration and help files "look" like this can be done, but I am losing confidence in this Pentaho platform.

My colleagues are recommending I use Tableau Prep instead. They finished in less than an hour what has taken me a week and still cannot do with Pentaho.

The workflow goes:

- extract data from EDW

- combine EDW data with lookup tables from a few different databases

- export those data to Excel file for Pivots, charts etc.

Pretty simple stuff but very messy in Pentaho


#PentahoDataIntegrationPDI
#Kettle
#Pentaho
Ana Gonzalez's profile image
Ana Gonzalez posted 02-07-2019 10:24

I haven't tried writing all the information into one Excel at the time, but I expect to have problems with that given that each step writing to the excel will be locking up the file, and transformations in PDI run the steps in parallel, so you have different steps accessing at the same file at the same time.

So yes, I would write one transformation for each sheet you mean to write into the Excel file, and execute a transformation at a time.

Regards

's profile image
posted 06-18-2024 04:16

See the image attached for a quick sketch of what to do  - basically put a blocking step (widget 'Block this step until steps finish ' ) before writing to each sheet and trigger them after each other (ie only write to sheet 2 when sheet 1 is completed - then write to sheet 3 as sheet 2 completes... )

Related Content