Clover ETL join multiple Excel spreadsheets

CloverDX Platform
1

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! :slight_smile:

2

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

3

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.