Reading multiple excel files and respective sheets within subdirectories

[Dilver_Shaik]

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

[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/t...

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

[binuacs]

@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

[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!

[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?

[christine_assaad]

Hi @Dilver_Shaik 

You don't need to change the macro. This is why it's a powerful tool. It's dynamic and can be used with other data sets.

This link https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t... will give you more info on how to build macros. 

Edit: This is a better link that's more relevant to your use case https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets...

Cheers!

[Dilver_Shaik]

Hi @binuacs  - 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.

[binuacs · Accepted solution]

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

[Dilver_Shaik]

@binuacs  - It worked. Thank you so much.

[Magda_Malka]

Many thanks, very helpful!