How to combine multiple rows of one attribute into two attributes based on Excel indention

So you are trying to do the reverse of what was happening here?

https://community.safe.com/s/question/0D54Q000080hc7jSAA/translating-excel-table-structure-to-layered-kind-of-structure-in-excel

If you just need to get to a stage where you have the indents and can work from there, you can just use adjacent attribute handling to work out the indents and then a stringpadder to get something like this

Although not 100% sure this will work for all your data, although it works for the sample

Yes correct. But i can not find out how to show the indents. How did you manage to get the indent attribute there?

I used an attributecreator to work out the indents based on adjacent row types and then reinserted them. But i don't think this is the solution, it works for your sample data, but it wouldn't work if once you get onto Sub folder 2.

In the past when I've needed to read information in an excel file that is not brought through into FME I've had to use a process to unzip the excel file and read the information from the relevant xml file. The indents are show in the style.xml file, but it looks quite complex to match them up with the data.

I found the information in the Excel file with a bit of tweaking. The excel file can be turned into a zip by renaming it and then unpacked to show the real xml information of the document. In the subfolder "xl\worksheets" is a document named "sheet1.xml" that contains the values of the indentation in the following format. If i could merge this somehow it would be possible to get the structure of the B row back.

As a example is value from cell B15 below. It has a value 15 (linking it to the word "Task 1"), and s="4" is the indention. If there is a way to extract the B line and connect them to excel it could be workable.

      <c r="B15" s="4" t="s">
        <v>15</v>

The whole file:

      <c r="B15" s="4" t="s">
        <v>15</v>

If you read the xml with an xml reader with elements to match as worksheet/sheetData/row/c you should get some rows of data, you're only interested in the cells in column B so test that c.r begins with B, c.s contains the indent, use a stringsearcher and some regex [0-9]+ to extract the number from this cell which is the row number, you can then merge this onto the excel data by the rownumber, and use a stringpadder to apply the indents

Thanks for the help, you need a extra step to finish it. First you need to join the styles (left join c.s with _count) with the worksheet and than join the result (only the number of c.r with xlsx_row_id) with your Excel file. The end result looks like this:

Good stuff, I'd actually looked at the formatting options in the reader, but couldn't see the indent data. I don't know how I missed it, as it seems quite obvious when I've checked again