Combine excel sheets with two different schemas

[vkarthi8]

How to merge excel files with two different schemas? For example, one type of excel file has 12 columns, whereas another type of excel has 15 columns, where 3 columns are added in the 9th column. I need to combine all the data of excel files with these 2 different schemas. When these files are combined, the first type of file which does not have the 3 columns, should show the data as blank. Instead, currently my workings are either considering the first type of schema, or i should ignore all the files which has first type of schema, which make me to work with lesser data. Any thoughts??

[ShankerV · Accepted solution]

hi @vkarthi8 

If I understand your requirement correctly, you need to merge two excel sheets. 

You need the output for 15 columns where 3 columns as blank where there is no input from sheet 1 right.

This can be achieved using the Union Tool -> Select the option as Manually Configure Fields

Shanker V

[ShankerV · Accepted solution]

@vkarthi8 

Explaining with small example, 

Excel 1 contains 3 columns A, B and C

Excel 2 contains 5 columns A, B and D, E, F.

Use Union tool, - > In the Configuration Pane select Manually Configure Fields

In the Properties, you can see the Output Columns, 

Where you can use the Right arrow and Left arrow to get your desired output.

	Col1	Col2	Col3	Col4	Col5	Col6
#excel1	A	B	C
#excel2	A	B		D	E	F

 This will make sure, the values are null if there are no inputs from excel1 in Col4, Col5 and Col6.

Many thanks

Shanker V

[ShankerV]

@vkarthi8 

Hope this solves your issue. If not let me know what are you looking for in detail.

If it solves the problem, feel free to mark the solution of the post which helped to resolve the issue, so similar issues faced by other community members in future can be helpful to refer the solution.

[vkarthi8]

Thank you Shanker.. It really worked .. i just need to place these two type of schema files in separate folders.. to avoid complications.. IT WORKED.. THANK YOU AGAIN..

[ShankerV]

Welcome @vkarthi8