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,

Open 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,

Open 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???!!!

Open 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 :)
LVL 3
Tommy_CooperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeremy WeisingerSenior Network Consultant / EngineerCommented:
You could use  a table object. That would lend itself well to exporting to a CSV.

First, construct the table with its columns
#Create the table
$table = New-Object system.Data.DataTable "TableName" 
$col1 = New-Object system.Data.DataColumn ComputerName,([string]) 
$table.columns.add($col1) 
$col2 = New-Object system.Data.DataColumn OS,([string]) 
$table.columns.add($col2) 
$col3 = New-Object system.Data.DataColumn Model,([string]) 
$table.columns.add($col3) 
#... etc, etc, etc.
# and for the Date fields you can specify the [datetime] data type.
$col6 = New-Object System.Data.DataColumn DeployDate,([datetime])
$table.Columns.Add($col6)

Open in new window


Then, as you loop through your CSVs, you create a new row, add data to it, and then add the row to the table:
#Create a new row in the table   
$row = $table.NewRow() 

#Add data to row
$row.ComputerName = #specify the data you want to add to the column
$row.OS = #specify the data you want to add to the column
#... etc, etc, etc.

#Add the row to the table
$table.Rows.Add($row) 

Open in new window

Then finally, export the table to a CSV file:
$table | Export-Csv C:\output.csv -NoTypeInformation

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tommy_CooperAuthor Commented:
Hi Jeremy,

Many thanks for this. Interesting concept!

Let me have a play. It does look like it'll do what I need.

Do you know if this scales well? I imagine memory use is not massive as it is just text values stored in the table, right?

Cheers
0
Jeremy WeisingerSenior Network Consultant / EngineerCommented:
It depends on how much text your taking in. If you grab a 3Mb CSV then you'll use up to 6Mb if you write all your data to the table. If you work with your CSVs one at a time, add the data to the table, then remove the CSV object (or overwrite it) then you can keep the memory usage down.

If you do start to hit a memory limit, you could work with one CSV, write it to the table, then export to CSV immediately, then overwrite the table with the next CSV, export table to the same CSV with the -Append parameter, so on and so forth.

But you only need to worry about that if you're dealing with more text than the system can hold in RAM.
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Jeremy WeisingerSenior Network Consultant / EngineerCommented:
If you're finding memory is not being freed up, you could add a check in the loop to see how much memory powershell is using and if it's above x, run garbage collection.
[System.GC]::Collect()

Open in new window

1
Tommy_CooperAuthor Commented:
Awesome Jeremy - Many thanks for your time and help.
Kind regards
Tom
0
Jeremy WeisingerSenior Network Consultant / EngineerCommented:
Glad to help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.