powershell csv manipulation....

Hello Experts,

Please can anyone help with developing a solution to my problem?

I have several CV sheets from different sources that I need to consolidate into one master sheet.  I don't need every column from every sheet.

So, for example, I have the following headings:

SOURCE 

SHEET1
ComputerName,OS,Model,Colour,Location,

SHEET2
Computername,ApplicationID,ApplicationName,DeployDate,

SHEET3
Computername,ApplicationID,RetirementDate,Environment,DC,

Select allOpen in new window

As you can see, all sheets will have a Computername. So I think I need as much info about a computer in one row. One problem is that each computer might host multiple applications. There will be an unknown number of applications so I can't add a column for each one (Application1, Application2 etc.) So I will want one row for each application with duplicate computer information (I think).

So the result will look something like this:

RESULT

MASTER SHEET
ComputerName,OS,Model,ApplicationID,ApplicationName,DeployDate,RetirementDate,Environment,DC,
Server1,W2K3,HP DL 380 G5,23456,DoSomething,12/03/2009,12/12/2015,Prod,Timbuktu,
Server1,W2K3,HP DL 380 G5,23987,DoSomethingDifferent,2/06/2010,12/12/2015,Prod,Timbuktu,
Server1,W2K3,HP DL 380 G5,17656,MakeitUp,23/08/2010,12/12/2015,Prod,Timbuktu,
Server2,W2K3,HP DL 380 G5,15894,MakeitUp,23/08/2010,12/12/2015,UAT,Tatooine,
Server3,W2K3,HP DL 385 G3,63636,CleverStuff,16/04/2010,12/12/2015,Prod,Tatooine,

Select allOpen in new window

I have started off by importing each csv and then have created a custom object with the column headings that I want. Like this:

#Import the sheet1 and modify the column headings removing all the spaces
$list1 = gc "C:\Users\thicko\Documents\Scripts\w2k3\Sheet1.csv" | Select -skip 1 | `
ConvertFrom-Csv -Header 'ComputerName','OS','Model','Colour','Location',

#Create the custom object
$Consol1 = @()
#Remove all non Windows hosts and add the columns required from this sheet
Foreach ($AllComp in $List1) {
    If ($Allcomp.os -notmatch 'Windows') {
       #verbose out for testing! 
       Write-Verbose "$($AllComp.ComputerName) is $($AllComp.OS)"
        }
    Else {
        
        $obj1 = New-Object -TypeName PSObject
        Add-Member -InputObject $obj1 -MemberType NoteProperty -Name 'Hostname' -Value $Allcomp.ComputerName
        Add-Member -InputObject $obj1 -MemberType NoteProperty -Name 'Model' -Value $Allcomp.Model
        Add-Member -InputObject $obj1 -MemberType NoteProperty -Name 'OS' -Value $Allcomp.OS
        $Consol1 += $obj1
        }
    }

#Import the sheet2 and modify the column headings removing all the spaces    
$list2 = gc "C:\Users\thicko\Documents\Scripts\w2k3\Sheet2.csv" | Select -skip 1 | `
ConvertFrom-Csv -Header 'Computername','ApplicationID','ApplicationName','OS'

#Create the custom object
$Consol2 = @()
#Remove all non Windows hosts and add the columns required from this sheet
Foreach ($AllComp in $List2) {
    If ($Allcomp.OS -notmatch 'Windows') {
        
          #verbose out for testing!
        Write-Verbose "$($AllComp.Computername) is $($AllComp.OS)"
        }
    Else {
        
        $obj2 = New-Object -TypeName PSObject
        Add-Member -InputObject $obj2 -MemberType NoteProperty -Name 'Computer name' -Value $Allcomp.Computername
        Add-Member -InputObject $obj2 -MemberType NoteProperty -Name 'Application ID' -Value $Allcomp.ApplicationID
        Add-Member -InputObject $obj2 -MemberType NoteProperty -Name 'Application Name' -Value $Allcomp.ApplicationName
        
        $Consol2 += $obj2
        }
    }

#Now what to do with $Consol1 + $Consol2 to consolidate the information???!!!

Select allOpen in new window

Obviously I need to add the third sheet too. But what do I need to do now to consolidate the objects to give me a csv like my result?

Any help, greatly appreciated!

Cheers :)