Input csv File to PowerCLI Script and Retain all Data

[TheVMinator]

I have a powerCLI script that does the following:

Imports a csv (vmlist.csv) that looks like this:

VMname	Datacenter	Owner
vm1	paris	john
vm2	london	jim
vm3	new york	joe

then I import the csv and add the CPU count as an additional row.  The problem is that multiple providers of input files provide info with different additional column names that all need to be preserved in the final output file.  The additional columns could be different based on the source and there could be any number of new columns with new data  in them in each new input file, all which need to be preserved in the output file.

How can I create a report that both adds a "NumCPU" column to the csv, and PRESERVES any other columns that are in the input file, and creates a new .csv with all the original data plus the numcpu count?

[LucD]

If I understand correctly, you want to merge multiple CSV files?

For that you would need in each file a kind of key.

Do your CSV files have such a 'key'?

The VMname column perhaps?

If that is the case, you could do

$csv1 = Import-Csv -Path '.\csv1.csv' -UseCulture

$csv2 = Import-Csv -Path '.\csv2.csv' -UseCulture

$newCsv = @()

foreach($row1 in $csv1){

   $row2 = $csv2 | where{$row1.VMName -eq $_.VMName}

   foreach($prop in ($row2 | Get-Member -MemberType NoteProperty | where{$_.Name -ne 'VMName'})){

   $row1 | Add-Member -Name $prop.Name -Value $row2."$($prop.Name)" -MemberType NoteProperty

   }

   $newCsv += $row1

}

$newCsv

[TheVMinator]

This is great - I don't quite start off with two csv's though that I'm merging - what I've got is this:

### I start with one input file like this:

$report = @()

import-csv vmlist.csv -useculture | foreach{

$vmname = $_.vmname

$datacenter = $_.datacenter

$owner = $_.owner

$numcpu = (get-vm $vmname).numcpu

$properties = @{'vmname' = $vmname;

'datacenter' = $datacenter;

'owner' = $owner;

'numcpu' = $numcpu;

}

  $object = New-Object -TypeName PSObject -Property $properties

  $report += $object

}

$report | select vmname,datacenter,owner,numcpu | export-csv report.csv -useculture -notypeinformation

##so the goal is that if vmlist.csv has ANY other columns beside vmname, datacenter and owner, that the report.csv file that this report creates includes all those extra columns, whatever they might ##be.  Each new vmlist.csv file always has the "vmname" column, so that I can count on - but they may have 5 - 40 other columns also, which I can't predict that need to be preserved.

##  THanks!

[LucD · Accepted solution]

So you are adding properties to an existing CSV.

Then the script is even easier, just use the Add-Member cmdlet.

Something like this

$report = @()

foreach($row in (Import-Csv vmlist.csv -UseCulture)){

   $row | Add-Member -Name 'numcpu' -Value (Get-VM $vmname).numcpu -MemberType NoteProperty

   $report += $row

}

$report |

Export-Csv report.csv -useculture -notypeinformation

[TheVMinator]

OK great - thanks again