CODEWITHSUNDEEP
powershelliis
Richard Dakin
Richard Dakin

Reputation: 443

Foreach to CSV PowerShell

Used Exportto-CSV for the first time today in anger, got it working elsewhere but when i do the below, comes out with some random numbers.

Can someone advise what I am doing wrong?

    # List all sites, applications and appPools
dir IIS:\AppPools | ForEach-Object {
    # Site's app pool
    $_.name
    $_.managedRuntimeVersion
    $_.enable32BitAppOnWin64 
    $_.managedPipelineMode 
    $_.processModel.username
    $_.processModel.password
    $_.processModel.identityType
    # Any web applications on the site + their app pools
} | exportto-csv C:\bob.csv

Upvotes: 0

Views: 102

Answers (2)

veefu
veefu

Reputation: 2890

Export-CSV expects structured data as object properties. Your current code, however, pipes an array of values to Export-CSV.

Try using Select-Object to create objects 'on-the-fly' which contain properties you want in your csv, then pipe those to Export-CSV

Something like this should get the job done:

Get-ChildItem -path 'IIS:\AppPools' | 
    Select-Object -Property Name, `
        managedRuntimeVersion, `
        enabled32BitAppOnWin64, `
        managedPipelineMode, `
        @{label='processModel_username';     expression={$_.processModel.username}}, `
        @{label='processModel_password';     expression={$_.processModel.password}}, `
        @{label='processModel_identityType'; expression={$_.processModel.identityType}} | 
    Export-CSV -path 'C:\bob.csv'

Upvotes: 1

Vivek Kumar Singh
Vivek Kumar Singh

Reputation: 3350

Have a look at get-help Export-Csv description. It says - 

DESCRIPTION
    The Export-CSV cmdlet creates a CSV file of the objects that you submit. Each object is represented as a line or
    row of the CSV. The row consists of a comma-separated list of the values of object properties. You can use this
    cmdlet to create spreadsheets and share data with programs that take CSV files as input.

    Do not format objects before sending them to the Export-CSV cmdlet. If you do, the format properties are
    represented in the CSV file, instead of the properties of the original objects. To export only selected properties
    of an object, use the Select-Object cmdlet.

Read the line which says - The row consists of a comma-separated list of the values of object properties. The random numbers which you are seeing is the length of the properties which you have exported to CSV.

To overcome that, you can use a PSCustomObject like this - 

$array= @()
dir IIS:\AppPools | ForEach-Object {
    $obj = New-Object PSObject

    $Name = Add-Member -MemberType NoteProperty -Name "Name" $_.Name
    $managedRuntimeVersion = Add-Member -MemberType NoteProperty -Name "managedRuntimeVersion" $_.managedRuntimeVersion
    .
    .
    #Rest of your properties
    $array += $obj
    }
$array | Export-Csv C:\bob.csv -NoTypeInformation

Again to your question, what are you doing wrong -

  1. Not understanding the correct input type of Export-Csv cmdlet.
  2. Using Exportto-Csv instead of Export-Csv. I highly doubt if a cmdlet by Exportto-Csv name exists. Wondering how you got the results.
  3. Coding when you are angry!

Upvotes: 1

Related Questions