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 |
Solved! Go to Solution.
Hi,
is it also possible to define the input of the Join Multiple operator as the various sheets of a excel file?
Regards
Daniel
Yes, this is very much possible. We can join data from multiple excel sheets using Join multiple tool.