Data input

, I am looking for solution for below problem I have 5 excel files that are all different in structure/schema • 2 files have 10 column, • 1 file have 8 column • 1 have 16 column • 1 have 4 column and I need to merge in single excel with five different sheets could you please help me or share sample workflow it would be helpful

Datasets

Developer

Developer Tools

Macros

Accepted answers

binu_acs

@Bakiyaraja just checking whether the updated workflow working for you or not?

All comments

Christine_Assaad

Hi @Bakiyaraja

Please check this Community article. It has the solution.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets-or-a-Specific-Excel-Range/ta-p/398220

Cheers!

SPetrie

Look into a batch macro for importing the files.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Read-in-Multiple-Excel-Files-with-Multiple-Tabs-that-have/ta-p/51145

Once you have them import you should be able to alter them and then save them out to different tabs.

Bakiyaraja

HI all

The above solution is reading data from multiple Multiple excel (different schema )and writing output in to single sheet

but our requirement is to read and write output in different sheet.(Eg. output filename should have sheet1,sheet2,sheet3 so on)

INPUT:

2 excel sheets with different schema

INPUT 1 FROM EXCEL 1:

INPUT 2 FROM EXCEL 2:

output expectation:

one excel with 2 sheets

Attached is the macro that write output in single sheet:

could you please help with workflow/solution please

workflow(1).yxzp

binu_acs

@Bakiyaraja you can write into different sheets in the same excel. My question based on which field you need to write it into different sheets? I mean after combining all the fields into single sheets based on which field you are writing into different sheets?

binu_acs

@Bakiyaraja I updated your workflow to write into different sheets. Fir you need to add a formula tool and specify the output path where your output should save also you need to select a particular field which will be your sheet names.

In this example I used the temporary path, you can change to your original path

final output created based on the column1 in my sample file

workflow.yxzp

Bakiyaraja

HI binuacs ,

the above solution doesn't work for my requirement i need below format output in excel

input excel Files

ExcelA.xlsv(sheet1)

ID	Text
1	3
2	4

input excel ExcelB.xlsv(sheet1)

Customer	Name
ABC	ABCDEF
AB	ABC

Output i am geeting right now

Eg output excel name ExcelC.xlsv

ExcelC.xlsv--->sheet 1:

ID	Text	Customer	Name
1	3
2	4

ExcelC.xlsv--->sheet 2:

ID	Text	Customer	Name
		ABC	ABCDEF
		AB	ABC

Expected output:

ExcelC.xlsv--->sheet 1:

ID	Text
1	3
2	4

ExcelC.xlsv--->sheet 2:

Customer	Name
ABC	ABCDEF
AB	ABC

could you please help me with workflow solution

binu_acs

@Bakiyaraja Let me know the attached workflow works for you

Ran with the sample files and working fine

ExcelSheets_Combined.yxzp

Bakiyaraja

Hi Binuacs,

Thanks for your time and solution but i am getting same column header for both sheet in output and output is not as per the expected.

also workflow behavior is not expected if no of column in both sheet is different

output sheet 1:

output sheet 2:

could you please help me also do i need to make any configuration setup in Interface Designer/Workflow?

if NO let me what is default expected value

attached the same workflow

ExcelSheets_Combined.zip

binu_acs

@Bakiyaraja I made some changes to the attached workflow. Please let me know this time it is working for you or not?

ExcelSheets_Combined.yxzp

binu_acs

@Bakiyaraja wanted to check whether the given workflow working for you or not?

Bakiyaraja

HI Binuacs ,Thanks for follow up YOU REACHED ALMOST 90 % the but problem still exist in the workflow , if the number is column in the sheet is different /or when we gave 3 input with different schema.