Select Table and information from Several Tables in Excel

Question

Hello Alteryx,

I recently started using Alteryx and honestly it has been a great learning experience. I am currently working on a file and need some help with selecting some data. The attached data file represents a structure of the file which I will be using. The spreadsheet has several tables in it and I want to select the information listed under the transaction summary table.

Transaction SummaryTrans TypeTrans CodeIDCSANew CSADep SDep NameEEF1123450613145610104474-010-00000   DS1123450613145610104474-010-00000 1Jane DoeDS1123450613145610104474-010-00000 2John DoeTermT316554748642980686745-010-00000

The attached file is just a sample file and I will have to select this data from over 100 files which has same structure. My goal is to develop a script which will just extract the information under "Transaction Summary" Table. Since I am new to using Alteryx, I am hoping someone on the forum might be able to help me. Looking forward to your responses.

Data File.xlsx

Rahul · Accepted Answer

Hello Nicole - Thanks for the prompt response. Unfortunately the cell range is variable and will change in other spreadsheets. I am thinking that there might be a way to use "transaction Summary" header as one of the unique elements and fetch the data under that header.

NicoleJohnson · Accepted Answer

Ah yes, that does make it a bit trickier :)  But not impossible! (Few things are!)

See attached/image below. You can use some creative filtering & appending to figure out which line the table starts on (based on your comment about starting with Transaction Summary line), and use that to figure out which data to eliminate to isolate just the table you need.

Give it a shot, let me know if it works for you!

NJ

Joe_Mako · Accepted Answer

There are course many ways to approach this. The first step is to identify the logic you want to use to identify a table, this can be as complex as you can imagine.

The logic I went with for the attached is: A row that is fully blank denotes a change in table.

I commented the attached workflow (opens in v11+)

The first section is loading the data, all sheets from all Excel files based on the Directory tool settings, in flexible baseline structure that enables lots of different logic to be built upon, and it uses the attached macro. For more details, on this see my presentation titled "Techniques for Handling Data from Excel and Other Messy Unpredictable Files" from https://community.alteryx.com/t5/Inspire-2017-Buzz/Inspire-2017-sessions-available-on-demand/m-p/71154#M299

This next section generate the Table ID based on logic, and a subsection to pull out the Top Left Cell Value:

This final section may need to be copy-pasted for each table you want to pull out, since each table may have different header logic.