Dynamic Table Extraction from Unstructured Excel Sheets in Alteryx

Question

I have excel which contains multiple sheets, each sheet can contain multiple tables. There is no defined boundaries for tables. Subsequent files may have tables with different columns at different places. I am not looking for a solution that is static (file to file). I am  looking for something which can identify the boundaries and extract the tables. Can this ETL pipeline be created in Alteryx?

below i have attached a sheet.

test.xlsx

OTrieger · Answer

@Anasalter 
When filtering it will give you all the lines that are not empty to potentially if you let Alteryx logic how to identify the headers you can open the files several time each time opening it from a different line. But in this scenario you will need to find also the last row of each table.
If you can define the first row and last row you can solve your issue.

Anasalter · Answer

@OTrieger But this will only solve the Header shifting problem in sheets 
what if there are multiple tables in a sheet like the one i have shared at different places how this macro will identify those tables?

OTrieger · Answer

@Anasalter

It is possible. You will input the excel file as a default to input all the excel rows. After it add a RecordID tool, now create a filter to find the first line with data. Now you know which first row with data. You will need a batch macro now to change the XML code. What you will need to do is using the RecordID as the line first line of data in the input tool using it to open the same excel file while defining the first line of data. You will need to nest several batch macros to do it, but it is possible.