Mutiple excel file read and create single output file with multiple columns

[bhaushedbale]

HI ALl,

We want to read n number of excel files from the provided folder.. All files have few columns same and once column content is different in each file.

Now we have to read all these files and create a single file with multiple columns.

e.g

File_1					File_2					File_n
	Column1	Column2	column3			Column1	Column2	column3			Column1	Column2	column3
	1	a	111			1	a	222			1	a	456
	2	b	112			2	b	223			2	b	457
	3	c	113			3	c	224			3	c	458
	4	d	114			4	d	225			4	d	459
Output
	Column1	Column2	column_3_1	column_3_2	column_3_3
	1	a	111	222	456
	2	b	112	223	457
	3	c	113	224	458
	4	d	114	225	459

[RishiK]

@bhaushedbale I created this sample workflow with your sample data. See if this helps.

[messi007]

@bhaushedbale,

Maybe this macro can help:

https://community.alteryx.com/t5/Engine-Works/The-Ultimate-Alteryx-Holiday-gift-of-2015-Read-All-Exc... 

it has been built to accept many different file types and automatically union them

Hope that helps!

Regards

[T_Willins]

Hi @bhaushedbale,

Attached is a dynamic solution that should work for n number of files.  It uses the filenames in a MultiRow Formula tool to determine each set of data then a Formula tool to create the Column names.  Finally, a Cross Tab tool to put the fields in the right format.  The Excel files used for the inputs are just the sample files you provided.  A single Input Data tool with a wildcard allows all files to be brought in at once.  Let me know if you have questions.

[bhaushedbale]

Hi Rishi,

This will work if you know the number of files input.. we need solutions more n files (maybe in 100 or 1000 file volume).

For a small file set, this is perfect.

Regards,

Bhausaheb

[bhaushedbale]

Looks good but not solving my problem