Posts: 1
Joined: Wed Jan 25, 2017 6:29 pm

SpreadsheetDataWriter: Need to write to sheet name not in metadata

Postby darenweimer » Wed Jan 25, 2017 6:39 pm

I have a SpreadsheetDataWriter component writing to an XLSX file. The incoming metadata contains a field called "Zone" and its value is a single alpha character, such as "A". I need to write the entire input record as a row in the Excel workbook, but in a sheet named "Zone A".

The problem is, I cannot specify anything other than a hard-coded sheet name/number or an input metadata field for the Sheet property. I cannot setup a dictionary value to use since the Sheet property will not accept it. The component has no input mapping. And I have tried to place it in a subgraph and put that subgraph in a loop to use a parameter, since the Sheet property will accept a parameter. But the problem there is that the loop still sends all records to the subgraph at once, even though its inside of a loop, so they all write to one sheet.

Because of the lack of input mapping and usability of dictionary values in the Sheet property, what I am trying to accomplish seems impossible. What am I missing? Is there a workaround?


Posts: 35
Joined: Fri Nov 04, 2016 8:51 am

Re: SpreadsheetDataWriter: Need to write to sheet name not in metadata

Postby jandikovae » Tue Jan 31, 2017 10:24 am

The point is that the SpreadSheetDataWriter has been primarily designed to be able to partition data into separate files with user-defined file names (like output_<fieldValue1>). However, there isn't an easy way how to do that inside one file for sheet names yet.

In order to have a field-related name of each sheet you just need to make sure that there is the exact field in your data (storing exactly the same value as is the desired name of the sheet). Then you can simply setup property "Sheet" to "Partition data into sheets by data fields" and select the right field from the list of available fields.

In your situation, let me suggest a little workaround. In order to name the sheets to be something like "Zone <fieldValue1>, you should add a Reformat component right before the SpreadsheetDataWriter. Setup the Reformat component to add an extra field to your data (please note that you have to also create appropriate metadata to it). You can define the field in the Transform attribute as follows:

Code: Select all

concat("Zone " , $in.0.Zone)

Then you can split data into sheets using the Sheet attribute and the new field (as described above). Also, as I understand that you don't want to have this extra field in your excel file, I would also recommend you to use "Exclude fields" property in SpreadsheetData Writer and set the newly created field to this attribute.

I am attaching an example graph so that you can review all setups that I've suggested.

I will also log a ticket to our development queue to revise the Sheet functionality for the future releases of CloverETL.

Best Regards,

Example Graph
(5.96 KiB) Downloaded 31 times
Eva Jandikova
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support