Adding Identifier from field when loading multiple excel files

[SB555]

Dear Community, 

I have the following problem/challenge.

I have multiple excel files with the exact same structure (see sample attached): A header area with one field containing a Customer Number, and a range below with the table including the data that I want to use in the workflow.

My goal would be to add the customer number to each record created from the range in order to identify the entries in the overall databased later on.

I was able to get it to work with only one file by using 2 inputs for the same file, separating the information and using append fields. Unfortunately this does not work when loading multiple files for different customers.

Thank you in advance for any ideas on how to achieve this.

Scheme

[binuacs]

@SB555 Try batch macro

[SB555]

@binuacs Thank your super fast response and the proposed solution.

It looked very promising and I like the method. However, it didn't work with multiple files with different customer as a source (could also be an operator error, as I am quite a novice in the Alteryx world.). It looks like it is only repeating the entries of the first file even if it lists all 3 files in the first step (I used your example and created copies from the original file with different customer numbers).

[SB555]

Thank your super fast response and the proposed solution.

It looked very promising and I like the method. However, it didn't work with multiple files with different customer as a source (could also be an operator error, as I am quite a novice in the Alteryx world.). It looks like it is only repeating the entries of the first file even if it lists all 3 files in the first step (I used your example and created copies from the original file with different customer numbers).

[binuacs]

@SB555 just wanted to check are those files are having different sheet names?

[SB555]

The Sheets do all have the same name. Only the files have different names but share a common prefix.

[binuacs]

@SB555 what is the name of the sheets you are using in our original file

[SB555]

Thanks for investing your time helping me!

It's one sheet per file named "CS_TOTAL".

[binuacs · Accepted solution]

@SB555 I updated the sheet names in the macro, can you try now the attached workflow

[SB555]

unfortunately same result. I have used the 3 attached files for a test. This is the result:

It looks like it is continuously repeating the entries of the first file.