Locate and extract table from Excel

[Jes_Li]

Hi,

I have an Excel file including a table like below. I want to locate and extract this table and do further analysis in Alteryx.

This table can be any place in the Excel. The title of the table are always the same. The rows of table may change. No content on left or right side of table. Also no content on pre/post 2 rows of the table. There are uncertain number of blank columns between fields just as between Dep and Product or Date and Currency

I highlighted the table to be extracted in green and in 2 sheets. In fact, I have 70+ sheets with similar tables....

Thanks for your help!!!!

[Jonathan-Sherman]

Hi @Jes_Li,

When you bring the file in Alteryx should recognize the table and be able to extract it automatically. Your attachment for example i brought straight in, removed blank columns with the dynamic select tool, filled down the Dep and Product fields and created the table headers with the dynamic select tool.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Jes_Li]

Hi Jonathan,

Thanks for your reply. Sorry my sample excel is too simple to reflect my question. I updated the Excel with the required tables highlighted in green. Tables are in 2 sheets. In fact, I need to extract the tables from 70+ sheets......

[Jonathan-Sherman · Accepted solution]

Hi @Jes_Li,

That does make things a little more difficult, but certainly still achievable!

Sheet 1 outcome:

Sheet 2 outcome:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[abhishekyx]

Hi @Jonathan-Sherman 

I have somewhat similar case but with more added fields and I want to extract the output from the unstructured excel template. 

Please see the attachment. I would really appreciate your help here. 

Regards,

ab