Hi,
Is there a way that I can consolidate multiple excel files (getting only the 1st tab) and create a new column on the consolidated report that uses the first word of the file name as a reference on where I get the data?
Here's a sample of all excel file in one folder:
Solved! Go to Solution.
Hi @JenMBRamos
Here is how you can do it. Assuming all your files are in the same folder and have similar structure.
Workflow:
1. In Input tool you can configure filename as regex pattern so that all xlsx files ending with Payroll Register will be read in. And select the desired sheet which needs to be read from all.
2. Configure Input tool to output filename as a field.
3. Using regex in formula tool extracting the first word in the filename.
This resource will be helpful for the usecase https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...
Hope this helps : )
Please see below how you can do it : (I created a sample data as an example)
(in this example all sheet name have to be the same )
1-Input directory to read the folder with the excels
2- Batch macro to read the files with output file name as option
Attached the workflow.
Regards,
Thanks for your response.. I was able to create the report I want using your suggestions.
Happy to help : ) @JenMBRamos
If the response helps please don't forget to mark it as solution.
Cheers and have a nice day!