Skip to main content
  • EN

I am currently looking for a solution to change my data set (Excel) into multiple Excel files using a template file. Each file(or tab) should contain information about one tree.

The data set contains data about 250 trees (ID, name, year planted, height, LAI and photolink number). I made an excel file with a nice format that looks something like this:

Tree research information fact sheet:

Name:Year:Age:an Excel formula generating the current ageHeight (m):LAI:

 

 

I'm currently using a template file and know how to format the data to the correct font type but can not find how you add the information for attribute to write to the the specific cell.

Tree 1

Tree research information fact sheet:

Name:Quercus roburYear:1942Age:an Excel formula generating the current ageHeight (m):15LAI:3

and so on for all the other 249 records.

Short question:

How can i write the information of one record to specific cells in Excel so that it fits in my format.

In the parameters of your excel writer, look for 'row number attribute' and for 'use attribute names for column positions'.

 

 

By playing with these two parameters I think you will be able to write specific information to specific cells in your excel sheet. So for the row number, you should create an attribute that contain row numbers. For example: for each name feature, the attribute would be row 2, 9, 16, 23, etc. You can create these numbers by combining a Counter transformer with ExpressionEvaluator or AttributeManager. As a result, at row 2, 9, 16, ... the name values will be written.

If you would like this name value to be written to column B, you could just rename your attribute (with an AttributeRenamer) 'Name' to 'B'.

Does that help?

example.fmw

Hi @JeroenR,

The solution is not that complex. Apparently there is only one column that you want to fill with data. Looking at the example I assume it is column B, thus you need to create an attribute by the name B.

I do not know what your source data looks like, but I assume that each feature has all attributes. You can use a Cloner transformer to clone each feature five times, so it can be written to each row in Excel, I chose to directly write the clone number to the xlsx_row_id attribute.

Within a AttributeManager you can adjust the xlsx_row_id by adding 1, since the Cloner will start on 0. In the same AttributeManager you can create the attribute B and set a Conditional Value.

You will have to check the value of xlsx_row_id and set the value of B for that specific row.

I also added an example workspace that does the steps described above.

You could also use an attributexploder followed by a conditional statement in an AttributeCreator to set the rownumber for the relevant attributes and then remove the remainder

 

 

example.fmw

Hi @JeroenR,

The solution is not that complex. Apparently there is only one column that you want to fill with data. Looking at the example I assume it is column B, thus you need to create an attribute by the name B.

I do not know what your source data looks like, but I assume that each feature has all attributes. You can use a Cloner transformer to clone each feature five times, so it can be written to each row in Excel, I chose to directly write the clone number to the xlsx_row_id attribute.

Within a AttributeManager you can adjust the xlsx_row_id by adding 1, since the Cloner will start on 0. In the same AttributeManager you can create the attribute B and set a Conditional Value.

You will have to check the value of xlsx_row_id and set the value of B for that specific row.

I also added an example workspace that does the steps described above.

You could also use an attributexploder followed by a conditional statement in an AttributeCreator to set the rownumber for the relevant attributes and then remove the remainder

 

 

@egomm

 

Using the AttributeExploder is also a nice suggestion. However, you do create unnecessary features that would slow down the workspace in case of many features, that's why I chose to use the Cloner method.

 

You could also to choose to already rename the _attr_value attribute to B. This way it is not necessary to set that specific attribute.

 

You could also use an attributexploder followed by a conditional statement in an AttributeCreator to set the rownumber for the relevant attributes and then remove the remainder

 

 

@egomm

 

Using the AttributeExploder is also a nice suggestion. However, you do create unnecessary features that would slow down the workspace in case of many features, that's why I chose to use the Cloner method.

 

You could also to choose to already rename the _attr_value attribute to B. This way it is not necessary to set that specific attribute.

 

Hi @JeroenR

Here is a way to do it.

No need for the row_id as I use a counter but use attribute name as column name is indeed correct.

Fanout on treename if it is unique else use an ID.

I assume tree's are record based (row) wich means your template is transposed.

AS my template is not in same order as the attributes in the data is use a mapper.

Greets 2 all.

 

My input

IDNameYearHeightLai234Quercus robur1942153453Oak20170,5412Sequoia200

100

My template:

@egomm

 

Using the AttributeExploder is also a nice suggestion. However, you do create unnecessary features that would slow down the workspace in case of many features, that's why I chose to use the Cloner method.

 

You could also to choose to already rename the _attr_value attribute to B. This way it is not necessary to set that specific attribute.

 

You can remove hidden attributes and unwanted attributes prior to the attributeexploder so unnecessary features are not created. I find this method better if not populating in every row

..yes you can indeed use conditional attribute creator to do the rownumber mapping (and do away with the counter, mapper and renamer.

(sorter was for my pleasure..)

Reply


Community Stats

30,193
Posts
114,600
Replies
37,855
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.