Append multiple excel files' data in a single file

[SD9126]

Hi everyone, I need one help. I have multiple excel data files in a folder. In each file there are 2 sheets: Sheet1 and Sheet2. I want to append all Sheet2 data in a new excel file and also in the output file I need 1 extra column which shows the source file's name. I tried to do this using Wildcard (*) in Input tool but it returned an error saying files' schema are different and ended up pulling only the 1st file's data from the folder. Also tried using Batch Macro but I guess there's some error I'm doing. Please let me know if there is any solution. Thanks in advance.

[atcodedog05]

Hi @SD9126 

Can you provide some sample input and expected output It will help us get a better understanding of the usecase.

We will be happy to help : )

[binuacs]

@SD9126 a batch macro would work for your use case

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...

[SD9126]

Sure, Here is the format:

File1 has 2 sheets: Sheet1, Sheet2.

File2 has 2 Sheets: Sheet1, Sheet2.

File3 has 2 Sheets: Sheet1. Sheet2.

Sheet2 format in all 3 files is as below with different data:

Name	Email	Dept
ABC	ABCGMAIL	ACC
DEF	DEFGMAIL	IT

I want to append all Shee2 data in a new excel file XYZ.xlsx as per the following format:

Name	Email	Dept	Source File Name
ABC	ABCGMAIL	ACC	File1
DEF	DEFGMAIL	IT	File2

[atcodedog05]

Hi @SD9126 

Methods in this post https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t... recommended by @binuacs will definitely help you in this usecase. Try each method.

Hope this helps : )

[SD9126]

Hi, I tried using the batch macro but it only giving the output of the file for which I'm selecting the sheet only. Not appending all files available in the directory.

[atcodedog05]

Hi @SD9126 

Try the first approach or please provide some sample files to work with.

Hope this helps : )

[SD9126]

Please refer to the attachments

[atcodedog05]

Hi @SD9126 

Here is how you can do it.

Workflow:

Hope this helps : )

[SD9126]

Hi, already tried this. however while using the actual data files, it's showing Schema error for multiple files.