Read excel Files and sheets from folder

PB41091

Hi,

I have around 15 files with dates in one folder. In each excel file I have around 50 sheets.

I have to read 1 excel file and its particular 30 sheets using sheets name.

I want to repeat this process for all 15 excels. After that, I want to append the sheets one below one with file name in 1 column.

Output I want --> only 1 excel file for all 30 sheets. Basically, appended data sheet wise for all 15 files along with one column to identify the source of data.

ChrisTX

Do all of the sheets in all of the files have exactly the same layout?

If yes, you can use a Directory tool that feeds into a Dynamic Input tool.

If not, you'll need a batch macro to bring in different layouts, then use a Union tool to combine the Sheets.

To get the source of data (file name and sheet name), whenever you use an Input Data tool, use the configuration option Output File Name as Field = Full Path

Check out this post:

The Ultimate Input Data Flowchart
Community > Designer > Designer Knowledge > The Ultimate Input Data Flowchart
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

And here are some posts about Dynamic Input, and reading multiple Excel files / sheets:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/SubDirectories-level-limitation/m-p/442662#M84361

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/No-sheet-Specified-You-must-Specify-a-Sheet/td-p/458104

this solution requires all input Sheets to have the same layout

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Using-input-Directory-and-Dynamic-input-tool-with-2-sheets-in/td-p/622984

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Get-worksheet-names-from-excel/td-p/344867

see response from LordNeilLord: Alteryx Certified Partner
I built you a little workflow to do this.....the first Macro reads all of the sheet names and the second macro imports those sheets into a single file

Directory.yxzp

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-create-multiple-workflows-from-a-single-input-pulling/td-p/303789

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Input-to-read-list-of-excel-files-from-Directory/td-p/305956

Input multiple xlsx files and union them

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Input-multiple-xlsx-files-and-union-them/td-p/306503

Read multiple sheets from multiple Excel files

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/using-dynamic-input-on-excel-files-with-different-tab-worksheet/td-p/3503

using a batch macro to import multiple sheets from excel

https://www.thedataschool.co.uk/nick-jastrzebski/beer-and-loathing-using-a-batch-macro-to-import-multiple-sheets-from-excel/

Community > Designer > Browse Knowledge > CS Macro Dev: Reading in Multiple Files with Different Field Schema - Batch Macro

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Files-with-Different-Field/ta-p/13370

How to Input Multiple Sheets from Multiple Excel Files

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-Input-Multiple-Sheets-from-Multiple-Excel-Files/m-p/20014

Chris