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
Solucionado! Ir para Solução.
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
@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
Please check the attached. It uses a simple macro to get you the info you are looking for.
Hope this helps. Cheers!
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?
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!
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 - It worked. Thank you so much.
Many thanks, very helpful!