Skip to main content
  • EN

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?

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

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.

 

 

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.

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


Community Stats

30,190
Posts
114,589
Replies
37,854
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.