Hi Team, I have a requirement like I need to read multiple excel files (similar names with different dates) which may present in sub-directories as well. I should be doing a pattern matching to get all the files. Example : DIR_1\Business_Info_Jun10.xlsx DIR_1\DIR_2\Business_Info_Jun05.xlsx DIR_1\DIR_3\Business_Info_Jun10.xlsx DIR_1\DIR_2\DIR_4\Business_Info_Jun11.xlsx After reading all the necessary files, I need to fetch all the data in the respective files along with the sheet names. Example : DIR_1\Business_Info_Jun10.xlsx -> Contains sheets like (Tickets, Cases, Portfolio) DIR_1\DIR_2\Business_Info_Jun05.xlsx -> Contains sheets like (Tickets, Cases, Portfolio) DIR_1\DIR_3\Business_Info_Jun10.xlsx -> Contains sheets like (Tickets, Cases, Portfolio) DIR_1\DIR_2\DIR_4\Business_Info_Jun11.xlsx -> Contains sheets like (Tickets, Cases, Portfolio) In each sheet, I have few records Example : DIR_1\Business_Info_Jun10.xlsx -> Contains sheets like (Tickets, Cases, Portfolio) Tickets Column-AColumn-BColumn-C1234Test-1Test-24567 Test-3 Test-4 Cases Column-AColumn-BColumn-C1234Test-1Test-24567 Test-3 Test-4 Portfolio Column-AColumn-BColumn-C1234Test-1Test-24567 Test-3 Test-4 Similar sheets will be there in other files as well. Expected Output : FullpathFileNameSheetNameColumn-AColumn-BColumn-CDIR_1\Business_Info_Jun10.xlsxBusiness_Info_Jun10.xlsxTickets1234Test-1Test-2DIR_1\Business_Info_Jun10.xlsxBusiness_Info_Jun10.xlsxTickets4567 Test-3 Test-4DIR_1\Business_Info_Jun10.xlsxBusiness_Info_Jun10.xlsxCases1234Test-1Test-2DIR_1\Business_Info_Jun10.xlsxBusiness_Info_Jun10.xlsxCases4567 Test-3 Test-4DIR_1\Business_Info_Jun10.xlsxBusiness_Info_Jun10.xlsxPortfolio1234Test-1Test-2DIR_1\Business_Info_Jun10.xlsxBusiness_Info_Jun10.xlsxPortfolio4567 Test-3 Test-4DIR_1\DIR_2\Business_Info_Jun05.xlsx Business_Info_Jun05.xlsx Tickets1234Test-1Test-2DIR_1\DIR_2\Business_Info_Jun05.xlsx Business_Info_Jun05.xlsx Tickets4567 Test-3 Test-4DIR_1\DIR_2\Business_Info_Jun05.xlsx Business_Info_Jun05.xlsx Cases1234Test-1Test-2DIR_1\DIR_2\Business_Info_Jun05.xlsx Business_Info_Jun05.xlsx Cases4567 Test-3 Test-4DIR_1\DIR_2\Business_Info_Jun05.xlsx Business_Info_Jun05.xlsx Portfolio1234Test-1Test-2DIR_1\DIR_2\Business_Info_Jun05.xlsx Business_Info_Jun05.xlsx Portfolio4567 Test-3 Test-4 Basically, what i need is sheet names as well along with the data. Any help is much appreciated. Thanks & Regards Dilver

Reading multiple excel files and respective sheets within subdirectories

Hi Team,

I have a requirement like I need to read multiple excel files (similar names with different dates) which may present in sub-directories as well. I should be doing a pattern matching to get all the files.

Example :

DIR_1\Business_Info_Jun10.xlsx

DIR_1\DIR_2\Business_Info_Jun05.xlsx

DIR_1\DIR_3\Business_Info_Jun10.xlsx

DIR_1\DIR_2\DIR_4\Business_Info_Jun11.xlsx

After reading all the necessary files, I need to fetch all the data in the respective files along with the sheet names.

Example :

DIR_1\Business_Info_Jun10.xlsx -> Contains sheets like (Tickets, Cases, Portfolio)

DIR_1\DIR_2\Business_Info_Jun05.xlsx -> Contains sheets like (Tickets, Cases, Portfolio)

DIR_1\DIR_3\Business_Info_Jun10.xlsx -> Contains sheets like (Tickets, Cases, Portfolio)

DIR_1\DIR_2\DIR_4\Business_Info_Jun11.xlsx -> Contains sheets like (Tickets, Cases, Portfolio)

In each sheet, I have few records

Example :

DIR_1\Business_Info_Jun10.xlsx -> Contains sheets like (Tickets, Cases, Portfolio)

Tickets

Column-A	Column-B	Column-C
1234	Test-1	Test-2
4567	Test-3	Test-4

Cases

Column-A	Column-B	Column-C
1234	Test-1	Test-2
4567	Test-3	Test-4

Portfolio

Column-A	Column-B	Column-C
1234	Test-1	Test-2
4567	Test-3	Test-4

Similar sheets will be there in other files as well.

Expected Output :

Fullpath	FileName	SheetName	Column-A	Column-B	Column-C
DIR_1\Business_Info_Jun10.xlsx	Business_Info_Jun10.xlsx	Tickets	1234	Test-1	Test-2
DIR_1\Business_Info_Jun10.xlsx	Business_Info_Jun10.xlsx	Tickets	4567	Test-3	Test-4
DIR_1\Business_Info_Jun10.xlsx	Business_Info_Jun10.xlsx	Cases	1234	Test-1	Test-2
DIR_1\Business_Info_Jun10.xlsx	Business_Info_Jun10.xlsx	Cases	4567	Test-3	Test-4
DIR_1\Business_Info_Jun10.xlsx	Business_Info_Jun10.xlsx	Portfolio	1234	Test-1	Test-2
DIR_1\Business_Info_Jun10.xlsx	Business_Info_Jun10.xlsx	Portfolio	4567	Test-3	Test-4
DIR_1\DIR_2\Business_Info_Jun05.xlsx	Business_Info_Jun05.xlsx	Tickets	1234	Test-1	Test-2
DIR_1\DIR_2\Business_Info_Jun05.xlsx	Business_Info_Jun05.xlsx	Tickets	4567	Test-3	Test-4
DIR_1\DIR_2\Business_Info_Jun05.xlsx	Business_Info_Jun05.xlsx	Cases	1234	Test-1	Test-2
DIR_1\DIR_2\Business_Info_Jun05.xlsx	Business_Info_Jun05.xlsx	Cases	4567	Test-3	Test-4
DIR_1\DIR_2\Business_Info_Jun05.xlsx	Business_Info_Jun05.xlsx	Portfolio	1234	Test-1	Test-2
DIR_1\DIR_2\Business_Info_Jun05.xlsx	Business_Info_Jun05.xlsx	Portfolio	4567	Test-3	Test-4

Basically, what i need is sheet names as well along with the data.

Any help is much appreciated.

Thanks & Regards

Dilver

Batch Macro

Dynamic Processing

Accepted answers

binu_acs

@Dilver_Shaik Can you please check the RegeEx formula reflects like below

Multiple_Sheets_09_06.yxzp

All comments

davidskaife

Hi @Dilver_Shaik

There is an excellent article here that will help you - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

It provides worked examples as well to help tailor it to your specific requirements

binu_acs

@Dilver_Shaik You need to have two batch macro for this one is for getting all the file names and sheet names and second is for getting the data

Multiple_Sheets_09_06.yxzp

Christine_Assaad

Hi @Dilver_Shaik

Please check the attached. It uses a simple macro to get you the info you are looking for.

Hope this helps. Cheers!

Reading Multiple files and sheets.yxzp

Business_Info_Jun05.xlsx

Business_Info_Jun10.xlsx

Dilver_Shaik

Hi @christine_assaad - Thanks for sharing that.

I am not much familiar to Macros. Correct me if I am wrong.

In the directory tool, I had given the path of the files. Do I need to give the same in the Input data tool inside the Macro?

Dilver_Shaik

Hi @binu_acs - Thanks for the solution. This works for me. But sheet name is not getting fully. Seems some problem in Regex_replace. Could you please help with that.

binu_acs

@Dilver_Shaik Can you please check the RegeEx formula reflects like below

Multiple_Sheets_09_06.yxzp

Dilver_Shaik

@binu_acs - It worked. Thank you so much.

Magda_Malka

Many thanks, very helpful!

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167