Skip to main content
Solved

Translating Excel table structure to layered kind of structure in Excel

  • July 8, 2020
  • 4 replies
  • 30 views
jeroen
Contributor
Forum|alt.badge.img+15

I'm having some difficulties with an translation of an Excel file that contains a normal table kind of structure and needs to be transformed to another more layered kind of structure (Kind of reminds me of a xml) in Excel. The input is as followed:

And needs to be transformed to this output:

I managed to get a semi result using the AttributeSplitter and work my way from there but it is way to hardcoded to be useful. If a Sub folder 2 or another task is added it needs extra work to correct it. When done i would like to add some extra tasks later so best to prepare for it now. How can i loop through the folder and name attribute and build up the structure so i can add the description and result in?

Any ideas on how to tackle this problem?

And is there a way to use the indentation option in Excel like the result above?

Best answer by jdh

I used the sampler and attribute splitter to get the relevant rows. Unfortunately bulk mode interferes with feature ordering, so they all need to be sorted at the end.

 

 

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

4 replies

ebygomm
Influencer
Forum|alt.badge.img+44
  • ebygommInfluencer
  • Influencer
  • 3427 replies
  • July 8, 2020

I'm not sure whether this would be dynamic enough for your purposes as I'm not quite sure how your data will change. It's using the attributesplitter, and using the count of the list elements to work out the line numbering

layered_xlsx.fmwt

jdh
Contributor
Forum|alt.badge.img+37
  • jdhContributor
  • Contributor
  • 2002 replies
  • Best Answer
  • July 8, 2020

I used the sampler and attribute splitter to get the relevant rows. Unfortunately bulk mode interferes with feature ordering, so they all need to be sorted at the end.

 

 

jdh
Contributor
Forum|alt.badge.img+37
  • jdhContributor
  • Contributor
  • 2002 replies
  • July 8, 2020

I used the sampler and attribute splitter to get the relevant rows. Unfortunately bulk mode interferes with feature ordering, so they all need to be sorted at the end.

 

 

@ebygomm's is a little quicker on the upload than I am. The two workspaces are substantially the same, Elizabeth's runs a smidge faster, but as currently set up doesn't allow for different folders with repeated task names - though that's a simple change.

jeroen
Contributor
Forum|alt.badge.img+15
  • jeroenContributor
  • Author
  • Contributor
  • 97 replies
  • July 15, 2020

I used the sampler and attribute splitter to get the relevant rows. Unfortunately bulk mode interferes with feature ordering, so they all need to be sorted at the end.

 

 

The indentation does not work like expected but the rest does, it shows spaces in a cell instead of nothing but with the indentation. I can search replace them in Excel to get it working, not automated but just 4 simple replacements. Thanks!

Community Stats

32,070
Posts
121,785
Replies
39,700
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.