Skip to main content

Hi, I have a large amount of structured survey data in the form of an Excel sheet (one row per survey, columns serve as the attribute values) which i need to transform into multiple Excel sheets (one per survey) based on a provided template.

The location for each value in the template, will depend on what the attribute value is in the source data table. I'm not quite sure where to start although i'm pretty sure i'll have to master the art of using xlsx_row_id... Any help would be hugely appreciated! Thanks

Does the template have Named ranges in it?

FME can write to named ranges - This can also be single cells I think. I think this should be pretty helpful in your case as you shouldn't need to worry about the row id when writing, although without seeing the data it's pretty hard to provide many tips.

https://community.safe.com/s/question/0D54Q000080hHoXSAU/excel-named-range-readwrite

It sounds like there are some issues with that method but it's probably the best option. You might have to experiment with various options in the writer and template to find something that works.

 

Good luck! Automation is fun


You can expose the xlsx_row_id attribute and use it as the Sheet name.xlsx_row_id to sheet

As for formatting the rest of your template, get familiarized with column and row id. I hope you have a header row in your original data.🙂


Does the template have Named ranges in it?

FME can write to named ranges - This can also be single cells I think. I think this should be pretty helpful in your case as you shouldn't need to worry about the row id when writing, although without seeing the data it's pretty hard to provide many tips.

https://community.safe.com/s/question/0D54Q000080hHoXSAU/excel-named-range-readwrite

It sounds like there are some issues with that method but it's probably the best option. You might have to experiment with various options in the writer and template to find something that works.

 

Good luck! Automation is fun

​Thanks @virtualcitymatt​ that's very helpful to know. The template does not have named ranges. But this is something that I could add...

Attached is an example of the source data. One row per survey and i've colour coded the attributes to match the required destination cells in the template (attached in the next comment). Depending on what the attribute value is, an 'x' is required to be put next to the matching value in the template. Does this help explain the issue?


​Thanks @virtualcitymatt​ that's very helpful to know. The template does not have named ranges. But this is something that I could add...

Attached is an example of the source data. One row per survey and i've colour coded the attributes to match the required destination cells in the template (attached in the next comment). Depending on what the attribute value is, an 'x' is required to be put next to the matching value in the template. Does this help explain the issue?

This a snapshot of the template i am trying to populate with the data in the source example above. Notice the 'x' against a value in each category (located in an attribute in the source data).


Reply