Manipulation of an complex excel file

Hi @delgadojeaneth,

Could you share us a sample you excel?

Thanks,

Danilo

Checking out the FME Desktop training for Excel recorded training session could give you some additional ideas.

You can use variable setters to do that.

At the reader parameter set has filed name row to "no".

Test and set variable

if column A= sub area name then value = column B

Same for row total cost.

Then come the costs which u can use for instance stringsearcher regexp [A-Z]{1} and then set variable to column A and v

If all sub areas are stacked in same column range, then your done. If not then you must adres the columns with mod (nr-of-columns-per_subarea). Depending on how many you have you might want to turn the column letters to a value by concatenating the character codes.

Hi @delgadojeaneth, I would add appropriate Sub-Area Name to every data row and then create desired table with an InlineQuerier (SQL query statement).

Then, add an InlineQuerier and set SQL statement according to the requirement.

SQL Query Example

select * from (    select        "Sub-Area",        'Total Cost' as "Item",        "Cost",        "Labour Cost",        "Total",        "Volume",        "Total Cost"    from source where "Item" = '_total'    union all select        "Sub-Area",        'A + C',        sum("Cost"),        sum("Labour Cost"),        sum("Total"),        sum("Volume"),        sum("Total Cost")    from source where "Item" in ('A', 'C') group by "Sub-Area"    union all select        "Sub-Area",        'Q + S',        sum("Cost"),        sum("Labour Cost"),        sum("Total"),        sum("Volume"),        sum("Total Cost")    from source where "Item" in ('Q', 'S') group by "Sub-Area") order by "Sub-Area"

See also the attached demo: manipulate-complex-excel-table-example.fmwt (FME 2017.0)

Hope this helps.