Skip to main content
Solved

Translating Excel table structure to layered kind of structure in Excel

jeroen
Contributor
Forum|alt.badge.img+8

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.

 

 

View original
1 Attachments
Did this help you find an answer to your question?

4 replies

ebygomm
Influencer
Forum|alt.badge.img+31
  • ebygommInfluencer
  • Influencer
  • 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+28
  • jdhContributor
  • Contributor
  • 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+28
  • jdhContributor
  • Contributor
  • July 8, 2020
jdh wrote:

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+8
  • jeroenContributor
  • Author
  • Contributor
  • July 15, 2020
jdh wrote:

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!

Reply


Helpful Members This Week

Community Stats

30,910
Posts
117,117
Replies
38,714
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.