Export to export-xls function or export-csv

[uswbnc53]

I am wanting to use the export-xls function or export-csv cmdlet to export the output of a script to a spreadsheet. However, I am having issues figuring out how to format the script for use with the function. I know part of my problem is that my output is a string. When I attempt to redirect to export-csv the output is a column of numbers.

$Num=1.5
$Date = get-date -format g

Get-VMHost | ForEach-Object {
$GetVMHost= $_

$DhcpEnabled = $GetVMHost | Get-VMHostNetworkAdapter -VMKernel | % { $_.DhcpEnabled }

if ($DhcpEnabled -eq "")
       { "$Date,$GetVMHost,$Num,DHCP = $DhcpEnabled,Compliant" }
else   { "$Date,$GetVMHost,$Num,DHCP = $DhcpEnabled,Not Compliant"}

}

I want to format to be something like this.

Date, Hostname, Number, Value, Status

2/11/2012 7:02 PM,hosstname.domain,1.5,DHCP = False False,Compliant
2/11/2012 7:02 PM,hosstname.domain,1.5,DHCP = False False,Compliant
2/11/2012 7:02 PM,hosstname.domain,1.5,DHCP = False False,Compliant

[LucD]

Instead of producing strings as result, produce objects.

They are easier to manipulate and convert to a CSV file.

Something like this for example

$Num=1.5
$Date = get-date -format g
$report = Get-VMHost | ForEach-Object {
    $GetVMHost= $_
    $DhcpEnabled = $GetVMHost | Get-VMHostNetworkAdapter -VMKernel | % { $_.DhcpEnabled }

    if ($DhcpEnabled -eq ""){
        $text = "Compliant"
    }
    else{ 
        $text = "Not Compliant"
    }
    New-Object PSObject -Property @{
        Date = $Date
        Host = $GetVMHost
        Num = $Num 
        DHCP = $DhcpEnabled
        Compliant = $text
    }
}

$report | Export-Csv "C:\report.csv" -NoTypeInformation -UseCulture 

[uswbnc53]

Hi Luc.

I really appreciate your reply! That worked pretty well.

Except, when the export occurs to the csv the output for the DHCP column isn't the value expected. It exports as System.Object[].

I noticed it was returning a value for both NICs attached to the management network and I fixed that hoping that was the issue, but it is still exporting as seen below.

DHCP	Host	Compliant	Date	Num

System.Object[]	hostname.domain	Compliant	2/11/2012 21:56	1.5
System.Object[]	hostname.domain	Compliant	2/11/2012 21:56	1.5
System.Object[]	hostname.domain	Compliant	2/11/2012 21:56	1.5
System.Object[]	hostname.domain	Compliant	2/11/2012 21:56	1.5
System.Object[]	hostname.domain	Compliant	2/11/2012 21:56	1.5
System.Object[]	hostname.domain	Compliant	2/11/2012 21:56	1.5

If I run $report without the export-csv I get the below output as expected.

DHCP      : False
Host      : hostname.domain
Compliant : Compliant
Date      : 2/12/2012 1:29 PM
Num       : 1.5

DHCP      : False
Host      : hostname.domain
Compliant : Compliant
Date      : 2/12/2012 1:29 PM
Num       : 1.5

[LucD]

My mistake, the $DhcpEnabled variable is an array and Export-Csv handles those as you saw in the output.

One solution is to put all array elements, seperated by a comma, in a string.

$Num=1.5
$Date = get-date -format g 
$report = Get-VMHost | ForEach-Object {
    $GetVMHost= $_
    $DhcpEnabled = $GetVMHost | Get-VMHostNetworkAdapter -VMKernel | % { $_.DhcpEnabled }

    if ($DhcpEnabled -eq ""){
        $text = "Compliant"
    }
    else{ 
        $text = "Not Compliant"
    }
    New-Object PSObject -Property @{
        Date = $Date
        Host = $GetVMHost
        Num = $Num 
        DHCP = [string]::Join(',',$DhcpEnabled)
        Compliant = $text
    }
}

$report | Export-Csv "C:\report.csv" -NoTypeInformation -UseCulture 

[uswbnc53]

That worked!

Just one more question, if you don't mind.

Why do the columns not match the order in the script when they are exported? I tried to re-arrange the values in the script and they still exported to the csv the same.

Date = $Date
Host = $GetVMHost
Num = $Num
Value = [string]::Join(',',$DhcpEnabled)
Compliant = $text

Value	Host	Compliant	Date	Num
FALSE	hostname.domain	Compliant	2/12/2012 14:35	1.5
FALSE	hostname.domain	Not Compliant	2/12/2012 14:35	1.5
FALSE	hostname.domain	Compliant	2/12/2012 14:35	1.5

[LucD]

The New-Object cmdlet doesn't garantue the order.

To have the properties in a specific order in the CSV file, you will have to throw in a Select-Object cmdlet before the export.

$report | Select Date,Host,Num,DHCP,Compliant |
Export-Csv "C:\report.csv" -NoTypeInformation -UseCulture 

In PowerShell v3 this will change, there you specify an order of the properties on the New-Object cmdlet.