I have an requirement to perform certain operations only to selected tabs within excel wherein i get Pivot tables defined by source. Consider an input excel with 10 tabs out of which few can have Pivot data. Ask is to identify such sheets >> Unpivot >> do certain transformation and write back to excel.
Transformations can be like metadata comparison viz. Extract Column Header or Table Difference Finder etc. followed by order check etc. Whilst i am working upon this logic, struggling to identify or separate out sheets with Pivot data sent by source.
Can you please suggest a logic to separate out sheets with Pivoting data , assuming Unpivoting followed by my transformation logic can be applied on top of it.
I do not think it is possible to directly identify a sheet containing a pivot table. However, do you have a particular way of identifying these sheets? For example, do the sheet names follow a specific pattern, or do pivot tables within these sheets have columns with names like ‘Sum’ or ‘Count’? Any such reference or pattern can help in setting up the logic to identify and process these sheets.
Unfortunately there are no patterns being specified from data source.
Having said that, I tried inserting few Pivot tables/sheets and observed excel default adds keywords viz. Row Labels, Sum of column name (for decimals) etc. I am sure, you must be having list of all labels which excel depicts dynamically.
Can these be helpful to come up with a solution, so we can improvise henceforth ?
Unfortunately, I don’t have a list of all the default labels that Excel dynamically generates for pivot tables. However, I can suggest a few steps that might help you address the issue:
Use the Extract Table Spec Node : This will show details about your table’s structure with column names, possible value of a column etc.
Check the First Row: See if it contains relevant row labels.
Verify “Column Name” column: Look for column labels in the ‘Column Name’ column.
Create Conditional Logic: Use a Case Switch node to handle different scenarios.
