Clover ETL join multiple Excel spreadsheets

durdu

• January 27, 2014 00:00

• Answered

Follow

Hi all,

Currently I am working with an Excel file including multiple spreadsheets.

I want to use the data('long' datatype) of a cell which I can find on the first sheet. This (business) key has to come as an key for the information which is stored in the next sheets. The result has to be a row including a ID column (the key on the first sheet) and the information of the sheets as the other columns.

I am using Clover ETL 3.1.1 and the excel files are .xlsx

Thanks in advance! :)

• 

  novakj

  • January 28, 2014 15:00

  0

  Comment actions Permalink

  Hello,
  
  If I understand well your question, you want to use Excel formula (to use value of cell from first sheet at the next sheet).
  
  Generating formulas is not currently supported in CloverETL, but you can simply do what you need by using writing into the template XLSX file. The SpreadsheetDataWriter component can take a previously designed template Excel file, make a copy of it into the designated output file and write data to it, retaining the rest of the template including your prepared formulas. Please note, if you write data into cell with formula, the content of cell is replaced by the data value.
  
  For more details, please see documentation: http://doc.cloveretl.com/documentation/UserGuide/index.jsp?topic=/com.cloveretl.gui.docs/docs/spreadsheetwriter.html
• 

  durdu

  • January 29, 2014 13:16

  0

  Comment actions Permalink

  Hello novakj,
  
  Thank you for your response.
  
  I'm sorry for the indistinctness.
  My purpose was to join 2 different sheets, whereby one sheet had 1 value, which I had to multiple the amount as much as the records of the 2nd sheet.
  
  I found my solution by using the 'RelationalJoin' component. The join relation should be master =! slave.

Please sign in to leave a comment.