Identify Pivot data from excel tabs

Hi Team,

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.

Thanks in advance!

Hi Team,

Appreciate your quick insights or any references to follow. Thanks!

Hi @akhil_dhir_14,

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.

Best,
Keerthan

3 Likes

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 ?

Hello @akhil_dhir_14,

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:

  1. Use the Extract Table Spec Node : This will show details about your table’s structure with column names, possible value of a column etc.
  2. Check the First Row: See if it contains relevant row labels.
  3. Verify “Column Name” column: Look for column labels in the ‘Column Name’ column.
  4. Create Conditional Logic: Use a Case Switch node to handle different scenarios.

I hope this helps!

Best,
Keerthan

1 Like

@akhil_dhir_14 I have tried a few things with Python in order to finde Pivot structures inside the XML/Excel file but no success yet.

Edit: I tried again and it is possible to come up with something that would open Excel in the back and check for the tables using Python - “xlwings”:

2 Likes

@k10shetty1 . Thank you for your suggestions. Let me try and get back if it works this way.

@mlauber71 Possible to share the python code which worked for you?

1 Like

The complete example and code is in the workflow on the knime hub. You can download and test it yourself.

2 Likes