Locate and extract table from Excel

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!!!!

Book1 update.xlsx

Datasets

Input

Tips and Tricks

Accepted answers

Jonathan-Sherman

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

20191201 - ExtractTable.yxzp

All comments

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

20191130 - ExcelTable.yxmd

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......

Book1 update.xlsx

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335