Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Extract Data from multiple excel files with multiple sheets into one master sheet

Noorakroush
7 - Meteor

Hello, 

I have the following ask at hand and would like some help tackling it

Description

  • I have excel files with performance metrics for each employee in a uniform template (the template is not a typical tabular format, it looks something like the attached)
  • Each department employees are stored in a single folder (i.e. Folder Name: Department 1, has say 20 files corresponding to 20 employees)
  • The employee excel files consists of multiple sheets and I need data from all sheets to be extracted.
  • I want to create a workflow that goes through all folders and all files and extracts the required data points about each employee and puts all data in one single master sheet
  • Output template should include all the fields in all the sheets of the excel files 

I really appreciate your help 

 

Thank you 

3 REPLIES 3
Dougyv21
5 - Atom

Greetings,

- I would reformat these sheets so you can actually import them correctly (VBA) or you could use alteryx.  Then strip out what you need on each sheet (transpose/summarize/append records) into a table.

- i would create a folder structure for each department and have the output tools respectively put the files there

- You can extract data from multiple sheets by loading the same sheet type, just select the different tabs.

- I would create some sort of wildcard input that will serve your needs to pull all of this in.  Or i would house the data on a server or in its own alteryx database and pull everything from there

 

Hope this helps,

 

DV

danilang
19 - Altair
19 - Altair

Hi @Noorakroush 

 

Start with a Directory tool to read in all the file names in the entire Assessment tree.  use a "*.xlsx" wild card and start at the highest level with Include Subdirectories checked.  Feed this into a Dynamic Input configured to return List of Sheets.   This will give you a list of all the sheets in all the workbooks.

 

 

The way to proceed next depends on your version of Alteryx.  If it's 2020.3 or newer, you have the option to select a range of cells on Input.  Connect the output of the Directory tool to a set of Dynamic Input tools arranged in parallel to input each of the distinct range tables in your sheets.  i.e.  the first one inputs Name and Manager

danilang_0-1616934540972.png

 

The second Input tool would read the Roles range

danilang_1-1616934757686.png

Make sure to make the range wide enough to include the necessary row and column headers

 

After you perform whatever data gymnastics on each of the sections, use the FullPath from the Directory tool as a key to allow you to join all the range sections.

 

if your version of Alteryx is less than 2020.3, you'll have to read each of the sheets in their entirety and parse out the information that you need.  This generally involves breaking down the file sections using a multi row tools to mark out the various parts  Check out this post and this one for examples of how to do this   

 

Good luck

 

Dan

 

Noorakroush
7 - Meteor

Hi Dan, 

 

Thank you for your response. 

 

I have an earlier version of Alteryx. Im not sure I understand the sources you refer to on breaking down the file sections using a multi row tools to mark out the various parts. These also apply to excel files, not just text? 

Labels