Unable to append to existing CSV

[ganapa2000]

Hi,

I am unable to append the output to existing .csv file.

Please help

$code = @'

"### Output ###"

"" | Select @{n='OS_Sockets';e={(gwmi Win32_Processor | measure -Property NumberOfCores).count}}, @{n='OS_Cores';e={(gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors).count}}, @{n='OS_Total_vCPU'; e={ (gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors -sum).sum}}| Select CSName,'OS_Sockets','OS_Cores','OS_Total_vCPU' | ConvertTo-Csv -UseCulture -NoTypeInformation

'@

$report1 = @()

Import-Csv -Path $reportlocation1 -UseCulture -PipelineVariable row |

ForEach-Object -Process {

    $sInvoke = @{

        VM              = $_.Name

        GuestCredential = $Creds

        ScriptTYpe      = 'powershell'

        ScriptText      = $code

    }

    $result = Invoke-VMScript @sInvoke

    $dummy, $out1 = $result.ScriptOutput -split '### Output ###'

    $out1 = $out1.TrimStart("`n`r") -split "`n`r"

    $report1 += $out1 | ConvertFrom-Csv -UseCulture | Select-Object -Property 'OS_Sockets', 'OS_Cores', 'OS_Total_vCPU'

}

$report1 | Export-Csv -Path $reportlocation1 -UseCulture -NoTypeInformation

[CRad14]

The export-csv cmdlet has an -append parameter. Have you tried that?

[ganapa2000]

yes, I tried with -Append option and it did not work.

$code = @'

"### Output ###"

"" | Select @{n='OS_Sockets';e={(gwmi Win32_Processor | measure -Property NumberOfCores).count}}, @{n='OS_Cores';e={(gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors).count}}, @{n='OS_Total_vCPU'; e={ (gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors -sum).sum}}| Select CSName,'OS_Sockets','OS_Cores','OS_Total_vCPU' | ConvertTo-Csv -UseCulture -NoTypeInformation

'@

$report1 = @()

Import-Csv -Path $reportlocation1 -UseCulture -PipelineVariable row |

ForEach-Object -Process {

    $sInvoke = @{

        VM              = $_.Name

        GuestCredential = $Creds

        ScriptTYpe      = 'powershell'

        ScriptText      = $code

    }

    $result = Invoke-VMScript @sInvoke

    $dummy, $out1 = $result.ScriptOutput -split '### Output ###'

    $out1 = $out1.TrimStart("`n`r") -split "`n`r"

    $report1 += $out1 | ConvertFrom-Csv -UseCulture | Select-Object -Property 'OS_Sockets', 'OS_Cores', 'OS_Total_vCPU'

}

$report1 | Export-Csv -Append -Force -Path $reportlocation1 -UseCulture -NoTypeInformation

[LucD]

Are you sure there is data in $report1?
Do you get any errors?
Does it say anything more when you add the -Verbose switch on the Export-Csv?

[ganapa2000]

LucD,

I can see the output on the screen when I use the below

$report1 | ft -auto

But when I use the below, the output is not getting exported and appended to existing file.

$report1 | Export-Csv -Append -Force -Path $reportlocation1 -UseCulture -NoTypeInformation

[LucD]

What is in your original CSV, besides the Name column?

You can't 'append' new columns to an existing CSV.

If you want to add columns, you must overwrite the CSV, and add the required columns from the original CSB with Add-Member.

For example

$code = @'

"### Output ###"

"" | Select @{n='OS_Sockets';e={(gwmi Win32_Processor | measure -Property NumberOfCores).count}}, @{n='OS_Cores';e={(gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors).count}}, @{n='OS_Total_vCPU'; e={ (gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors -sum).sum}}| Select CSName,'OS_Sockets','OS_Cores','OS_Total_vCPU' | ConvertTo-Csv -UseCulture -NoTypeInformation

'@

$report1 = @()

Import-Csv -Path $reportlocation1 -UseCulture -PipelineVariable row |

ForEach-Object -Process {

    $sInvoke = @{

        VM              = $_.Name

        GuestCredential = $Creds

        ScriptTYpe      = 'powershell'

        ScriptText      = $code

    }

    $result = Invoke-VMScript @sInvoke

    $dummy, $out1 = $result.ScriptOutput -split '### Output ###'

    $out1 = $out1.TrimStart("`n`r") -split "`n`r"

    $report1 += $out1 | ConvertFrom-Csv -UseCulture | Select-Object -Property 'OS_Sockets', 'OS_Cores', 'OS_Total_vCPU' |

        Add-Member -MemberType NoteProperty -Name Name -Value $sInvoke.VM -PassThru

}

$report1 | Export-Csv -Force -Path $reportlocation1 -UseCulture -NoTypeInformation

[ganapa2000]

LucD,

I wanted to validate, if the CPU information is matching from both VMware end and OS end. My existing csv file has below columns

"Folder","Name","IP_Address","OS","Sockets","Cores","Total vCPU"

and I want to add additional columns to the existing file by grabbing from OS end

'OS_Sockets', 'OS_Cores', 'OS_Total_vCPU'

[LucD]

Then you have to add additional Add-Member lines (like I did for Name in the previous reply).

[ganapa2000]

LucD,

How can I break this and export by individual variable ? Do I need to run multiple times ?

"" | Select @{n='OS_Sockets';e={(gwmi Win32_Processor | measure -Property NumberOfCores).count}}, @{n='OS_Cores';e={(gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors).count}}, @{n='OS_Total_vCPU'; e={ (gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors -sum).sum}}| Select CSName,'OS_Sockets','OS_Cores','OS_Total_vCPU' | ConvertTo-Csv -UseCulture -NoTypeInformation

[LucD]

Sorry, but I don't understand why you would want to do that.
Just add additional Add-Member statements (like I did for the Name)

[ganapa2000]

LucD,

you meant like this ?

$code = @'

"### Output ###"

"" | Select @{n='OS_Sockets';e={(gwmi Win32_Processor | measure -Property NumberOfCores).count}} | Select CSName,'OS_Sockets' | ConvertTo-Csv -UseCulture -NoTypeInformation

"### Output ###"

"" | Select @{n='OS_Cores';e={(gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors).count}} | Select CSName,'OS_Cores' | ConvertTo-Csv -UseCulture -NoTypeInformation

"### Output ###"

"" | Select @{n='OS_Total_vCPU';e={ (gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors -sum).sum}}| Select CSName,'OS_Total_vCPU' | ConvertTo-Csv -UseCulture -NoTypeInformation

'@

$report1 = @()

Import-Csv -Path $reportlocation1 -UseCulture -PipelineVariable row |

ForEach-Object -Process {

    $sInvoke = @{

        VM              = $_.Name

        GuestCredential = $Creds

        ScriptTYpe      = 'powershell'

        ScriptText      = $code

    }

    $result = Invoke-VMScript @sInvoke

    $dummy, $out1, $out2, $out3 = $result.ScriptOutput -split '### Output ###'

    $out1 = $out1.TrimStart("`n`r") -split "`n`r"

    $out2 = $out2.TrimStart("`n`r")

    $out3 = $out3.TrimStart("`n`r")

    $report1 += $row | Add-Member -MemberType NoteProperty -Name 'OS_Sockets' -Value ([int]$out1) -PassThru | Add-Member -MemberType NoteProperty -Name 'OS_Cores' -Value ([int]$out2) -PassThru |

    Add-Member -MemberType NoteProperty -Name 'OS_Total_vCPU' -Value ([int]$out3) -PassThru

}

$report1 | ft -auto

$report1 | Export-Csv -Force -Path $reportlocation1 -UseCulture -NoTypeInformation

[LucD]

No, add additional Add-Member lines here

[ganapa2000]

LucD,

I tried as below but still the columns still shows blank

$code = @'

"### Output ###"

"" | Select @{n='OS_Sockets';e={(gwmi Win32_Processor | measure -Property NumberOfCores).count}}, @{n='OS_Cores';e={(gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors).count}}, @{n='OS_Total_vCPU'; e={ (gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors -sum).sum}}| Select CSName,'OS_Sockets','OS_Cores','OS_Total_vCPU' | ConvertTo-Csv -UseCulture -NoTypeInformation

'@

$report1 = @()

Import-Csv -Path $reportlocation1 -UseCulture -PipelineVariable row |

ForEach-Object -Process {

    $sInvoke = @{

        VM              = $_.Name

        GuestCredential = $Creds

        ScriptTYpe      = 'powershell'

        ScriptText      = $code

    }

    $result = Invoke-VMScript @sInvoke

    $dummy, $out1 = $result.ScriptOutput -split '### Output ###'

    $out1 = $out1.TrimStart("`n`r") -split "`n`r"

    $report1 += $out1 | ConvertFrom-Csv -UseCulture | Select-Object -Property 'OS_Sockets', 'OS_Cores', 'OS_Total_vCPU' | Add-Member -MemberType NoteProperty -Name 'OS_Sockets' -Value 'OS_Sockets' -PassThru | Add-Member -MemberType NoteProperty -Name 'OS_Cores' -Value 'OS_Cores' -PassThru |

    Add-Member -MemberType NoteProperty -Name 'OS_Total_vCPU' -Value 'OS_Total_vCPU' -PassThru

}

$report1 | Export-Csv -Force -Path $reportlocation1 -UseCulture -NoTypeInformation

[LucD · Accepted solution]

Try like this

$code = @'

"### Output ###"

"" | Select @{n='OS_Sockets';e={(gwmi Win32_Processor | measure -Property NumberOfCores).count}},

    @{n='OS_Cores';e={(gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors).count}},

    @{n='OS_Total_vCPU'; e={ (gwmi Win32_Processor | measure -Property NumberOfLogicalProcessors -sum).sum}}|

    ConvertTo-Csv -UseCulture -NoTypeInformation

'@

$report1 = @()

Import-Csv -Path $reportlocation1 -UseCulture -PipelineVariable row |

ForEach-Object -Process {

    $sInvoke = @{

        VM              = $_.Name

        GuestCredential = $Creds

        ScriptTYpe      = 'powershell'

        ScriptText      = $code

    }

    $result = Invoke-VMScript @sInvoke

    $dummy, $out1 = $result.ScriptOutput -split '### Output ###'

    $out1 = $out1.TrimStart("`n`r") -split "`n`r"

    $newProp = $out1 | ConvertFrom-Csv -UseCulture

    $report1 += $row | Add-Member -MemberType NoteProperty -Name OS_Sockets -Value $newProp.OS_Sockets -PassThru |

        Add-Member -MemberType NoteProperty -Name OS_Cores -Value $newProp.OS_Cores -PassThru |

        Add-Member -MemberType NoteProperty -Name OS_Total_vCPU -Value $newProp.OS_Total_vCPU -PassThru

}

$report1 | Export-Csv -Force -Path $reportlocation1 -UseCulture -NoTypeInformation

[ganapa2000]

Thank you very LucD. That worked