Append fields of the table and Also Add a Column with Table Name

[prakhar021]

Table 1 -:

Employee Name, Salary    

Aayush, 40000

Rajiv,30000

Table 2 -: 

Employee Name1, Salary1    

Mohit,70000

Aman,80000

Output -:

Source, Employee Name, Salary, Employee Name1, Salary1
Table1, Aayush, 40000, Null, Null
Table1,Rajiv, 30000, Null, Null
Table2,Null, Null, Mohit, 70000
Table2, Null, Null, Aman, 80000

Is there any tool which can help me get the desired results.

[OTrieger]

Hi @prakhar021 

Right now all your data is comma delaminated.
Use Text To Column Tool to get the data into Columns, each of the files, Add a Dynamic renaming from first row and then use a union tool. With Multi Field formula add text Null if field is null. Then you can use Transpose and Cross Tab it back. 

[TUSHAR050392]

Hey @prakhar021 Is that how your data looks - separated by comma? If yes then you need to get it into columns as mentioned by @OTrieger else directly use below solution. Once you have it into columns you can use another approach. 

Use formula tool in both tables and name a new column called source and just mention 'Table1' and 'Table2'. After that Union both the datasets by name and you will get null wherever there is no match. After that if you want in that comma separated format just use a formula tool and create a new column with formula - Source + ',',+ Employee Name + ',' + Salary + ',' +  Employee Name1 + ',' + Salary1 and drop other columns using select.