Getting the First word on the file name and add to a new column

[JenMBRamos]

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:

[atcodedog05 · Accepted 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 : )

[messi007 · Accepted solution]

@JenMBRamos,

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,

[JenMBRamos]

Thanks for your response.. I was able to create the report I want using your suggestions. 

[atcodedog05]

Happy to help : ) @JenMBRamos 

If the response helps please don't forget to mark it as solution.

Cheers and have a nice day!