copy data from multiple excel workbook into one

[pokhan27]

hi Expert

I am trying to combine with more than 5 different workbooks into one master file and publish it to sharepoint list. 

1. each workbook has single sheets

2. most of the column name  are common in the workbooks but there are other files where we have additional column 

3. create a macro which will read the file from the directory

4. add a column in master file which shows the source file name

folder with files: 

sample of column name in each file: 

desired output:

desired output
ID	name	Dept ID	Dept	region	comments	value	Filename
1	ABC			east			Avp
2	cba	fin12	finance	west			IMP
3	xyz		hr	north	tbd	100	IV

[binuacs]

@pokhan27 the below macro would be useful for your use case

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

[allwynthomas24]

Hey @pokhan27,

Please check the attached workflow and confirm whether it meets your stated requirements.

Thanks & Regards.

Spoiler

[pokhan27]

hi Thomas, 

folder browse option is not available in my server, now am thinking to union individual file as union

is there a way to get all the files without folder browse?

[soccertil1108]

If you already know the names of the files then you can put them into a Text Input and use Dynamic Input or a Macro. See my video that describes a generic solution to your problem.

https://youtu.be/GmfkB515Wec

[binuacs]

@pokhan27 use of wild characters should work if you have the same schema for all the input files. Instead of specify each file name you can use the wild card character *.

https://www.thedataschool.co.uk/borja-leiva/4840

[pokhan27]

Schema are  different