how do you properly append columns from multiple tables

[codyfawcett]

I'm having an issue with performing this action.  im using an rvtools report from vcenter, that exports a very similare excel file with multiple sheets as the tables.  I am fairly new to data manipulation like this, and normally just look at the sheets, but im looking to combine this data with other info from puppet, and cmdb, etc...

When i try this with unions or joins, etc... I usually end up with 42 rows with 8 columns.  What would be the proper way to actually append the columns to the existing data. without all the blank cells?

Any thoughts?

Table1
recordid	name	ip	cpu	memory
1	name1	192.168.1.2	4	8192
2	name2	192.168.1.3	8	4096
3	name3	192.168.1.4	4	8192
4	name4	192.168.1.5	4	4096
5	name5	192.168.1.6	6	8192
6	name6	192.168.1.7	4	4096
7	name7	192.168.1.8	4	8192
8	name8	192.168.1.9	4	4096
9	name9	192.168.1.10	12	8192
10	name10	192.168.1.11	4	4096
11	name11	192.168.1.12	16	8192
12	name12	192.168.1.13	4	4096
13	name13	192.168.1.14	4	8192
14	name14	192.168.1.15	4	4096

Table2
recordid	name	ip	nic	host
1	name1	192.168.1.2	1	host1
2	name2	192.168.1.3	1	host1
3	name3	192.168.1.4	1	host1
4	name4	192.168.1.5	1	host4
5	name5	192.168.1.6	1	host3
6	name6	192.168.1.7	2	host5
7	name7	192.168.1.8	1	host4
8	name8	192.168.1.9	1	host2
9	name9	192.168.1.10	1	host2
10	name10	192.168.1.11	1	host3
11	name11	192.168.1.12	4	host6
12	name12	192.168.1.13	1	host4
13	name13	192.168.1.14	1	host5
14	name14	192.168.1.15	1	host1

Table3
recordid	name	ip	os	powerstate
1	name1	192.168.1.2	windows 2008	poweredoff
2	name2	192.168.1.3	rhel 7.6	poweredon
3	name3	192.168.1.4	windows 2012	poweredon
4	name4	192.168.1.5	windows 2016
5	name5	192.168.1.6	windows 2012	poweredon
6	name6	192.168.1.7	windows 2012	poweredoff
7	name7	192.168.1.8		poweredoff
8	name8	192.168.1.9	windows 2016	poweredon
9	name9	192.168.1.10	windows 2016	poweredon
10	name10	192.168.1.11	windows 2016	poweredon
11	name11	192.168.1.12	rhel 7.4	poweredon
12	name12	192.168.1.13	rhel 7.4	poweredon
13	name13	192.168.1.14	windows 2016	poweredon
14	name14	192.168.1.15	windows 2016	poweredon

output table
recordid	name	ip	cpu	memory	nic	host	os	powerstate
1	name1	192.168.1.2	4	8192	1	host1	windows 2008	poweredoff
2	name2	192.168.1.3	8	4096	1	host1	rhel 7.6	poweredon
3	name3	192.168.1.4	4	8192	1	host1	windows 2012	poweredon
4	name4	192.168.1.5	4	4096	1	host4	windows 2016
5	name5	192.168.1.6	6	8192	1	host3	windows 2012	poweredon
6	name6	192.168.1.7	4	4096	2	host5	windows 2012	poweredoff
7	name7	192.168.1.8	4	8192	1	host4		poweredoff
8	name8	192.168.1.9	4	4096	1	host2	windows 2016	poweredon
9	name9	192.168.1.10	12	8192	1	host2	windows 2016	poweredon
10	name10	192.168.1.11	4	4096	1	host3	windows 2016	poweredon
11	name11	192.168.1.12	16	8192	4	host6	rhel 7.4	poweredon
12	name12	192.168.1.13	4	4096	1	host4	rhel 7.4	poweredon
13	name13	192.168.1.14	4	8192	1	host5	windows 2016	poweredon
14	name14	192.168.1.15	4	4096	1	host1	windows 2016	poweredon

[ponraj · Accepted solution]

Multijoin tool will help you to get the desired output. It has also select tool functionalities which will help you to remove the unwanted fields. 

[DanielDRU]

Hi,

is it also possible to define the input of the Join Multiple operator as the various sheets of a excel file?

Regards

Daniel

[ponraj]

Yes, this is very much possible. We can join data from multiple excel sheets using Join multiple tool.