Script required to find duplicates in a row and combine specific column data

[RobTheNewGuy]

Looking for a script to find duplicates in a row like below....

"VM1","VM","LDEV","LDEV1","LDEV2","LDEV3","LDEV4","LDEV5","LDEV6"
"VM","VI-SVCS-VM001","0000ef","","","","","",""
"VM","VI-SVCS-VM001","0000de","","","","","",""
"VM","VI-SVCS-VM002","0000df","","","","","",""
"VM","VI-SVCS-VM002","0000f0","","","","","",""

...and manipulate the data into the following:

"VM1",                    "VM",                              "LDEV",                "LDEV1",     "LDEV2",     "LDEV3","LDEV4","LDEV5","LDEV6"
"VM",                      "VI-SVCS-VM001",      "0000ef",               "0000de",    "",                  "","","",""
"VM",                      "VI-SVCS-VM002",      "0000df",               "0000f0",     "",                   "","","",""

(Note: VM object VI-SVCS-VM001 has two LDEVs attributed to it 0000ef and 0000de)

There may be two, three or n LDEVs attributed to each VM object. I'm guessing that I need to build an array and that the 'join-collections' script may not cut it on this one.

I have the process required, I just need to put it into a loop (note, data already sorted on VM object):

1. import-csv

2. read each row, on matched VM object, copy each LDEV found and place in next LDEV along

3. remove duplicate row

4. rinse, repeat until end

Thank you.

[RvdNieuwendijk]

The following PowerCLI code imports a file vms.csv and removes the duplicate rows:

Import-Csv -Path vms.csv |
Sort-Object -Property VM1,VM,LDEV,LDEV1,LDEV2,LDEV3,LDEV4,LDEV,LDEV6 -Unique

[RobTheNewGuy]

Hi Rvd,

I'm really looking for a loop. Something like:

For-Each unique VM, move different LDEV to new LDEV column.

For example:

"VM1","VM","LDEV","LDEV1","LDEV2","LDEV3","LDEV4","LDEV5","LDEV6"
"VM","VI-SVCS-VM001","0000ef","","","","","",""
"VM","VI-SVCS-VM001","0000de","","","","","",""

The above csv, shows that there are two VMs (VI-SVCS-VM001) with different LDEV values. I want the additional LDEVs moved into the next LDEVs along (and remove the redundant dupe). So:

"VM1","VM","LDEV","LDEV1","LDEV2","LDEV3","LDEV4","LDEV5","LDEV6"
"VM","VI-SVCS-VM001","0000ef","0000de","","","","",""

[LucD]

You mean something like this

Import-Csv duplicate.csv -UseCulture | 
Group-Object -Property VM1,VM | %{
    $temp = Select-Object -InputObject $_.Group[0]
    $i = 1
    $_.Group[1..($_.Group.Count - 1)] | %{
      $temp."LDEV$($i)" = $_.LDEV
      $i++
    }
    $temp
}

[RvdNieuwendijk]

Hi Rob,

we have the same name.

I now see what you want. However it is not so easy to implement.

For me it is time to get some sleep. I will try to solve your problem tomorrow.

Regards, Robert

[RvdNieuwendijk · Accepted solution]

That is a nice solution Luc. I didn't think of using the Group-Object cmdlet to solve this problem.

Now I can go to sleep without having to worry about this problem anymore.

[RobTheNewGuy]

Hi Rob,

Much appreciate the help! I'm going to post this bohemouth of a script up soon. I think it will help a great deal once it is done.

Best,

Rob.

[RvdNieuwendijk]

You should have given Luc the 10 points. He deserves all the credits for the best answer.

[RobTheNewGuy]

Hi Luc,

Sorry n00b mistake. How do I redeem/reissue points for an answer?

[LucD]

You can't I'm afraid.

But hey no problems, I answer questions because I love PowerShell and PowerCLI, not because of the points.