How can I get all property fields exported to a CSV file?

This topic contains 5 replies, has 3 voices, and was last updated by  Mark Hammonds 1 year, 10 months ago.

  • Author
    Posts
  • #32736

    Igway Jacobs
    Participant

    Trying to pull an array into a CSV file. However, it is only pulling the fields from the first object of the array into the CSV file.

    What I am actually doing: I am querying the registry but each item returned could have different property fields from its predecessor. The easiest example of this is the following script.

    $someVar = @()
    $someVar += Get-ChildItem C:\Windows\explorer.exe | Select-Object Name, LastWriteTime
    $list = Get-ChildItem C:\Windows\System32\*.exe | Get-Random -Count 2
    foreach ($item in $list) {
    $someVar += Get-Item $item
    }
    # Starting Output
    $someVar | Select-Object -Property * | Format-List
    $someVar | Select-Object -Property * | Format-Table -AutoSize
    $someVar | Select-Object -Property * | Export-Csv -Path C:\Reports\test1.csv -NoTypeInformation
    Write-Host "`nCSV file`n" -ForegroundColor Yellow
    Get-Content C:\Reports\test1.csv

    So in my example, the first item added to the array only gets the properties, Name and LastWriteTime. However, the next two items added to the array have all the properties added. When I start the output section, the Format-List shows the values from all objects. That is what I would expect to see when I use the Select-Object -Property *. That is what I expected to see.

    However, next line where I pipe to Format-Table, it is only showing the fields from the first object added to the array. Same for the Export-Csv, only showing the fields from the first object added to the array.

    Note, I understand that in this example, the property VersionInfo would add the System.String[] into the CSV, and I can fix that, however, that is not the point of this post.

    So, the questions I have are, how can I get all property fields exported to a CSV file? And why is this happening?

  • #32737

    Mark Hammonds
    Participant

    im confused do you want to get a list of file names from the file system or registry keys from the registry?

    what is the exact key you want?

  • #32738

    Dave Wyatt
    Moderator

    Most of these commands (Format-Table, Export-Csv, etc) expect that all of the input objects will have the same set of properties, and they get that information from the first object sent in via the pipeline. So in this case, you've trimmed off a ton of the properties of the first object, and that causes both Format-Table and Export-Csv to only care about those two properties, even for all of the other objects in the pipeline.

    Format-List, on the other hand, doesn't have to worry about a header, so it just processes each item individually.

    Personally, I don't see the value in your call to Select-Object on the first item in the list. Just let explorer.exe dump out all of the same information as the rest of the items in the list, and you won't have a problem.

  • #32739

    Igway Jacobs
    Participant

    I'm actually querying installed programs from registry keys and not all the values for each installed app use the same properties. That's why it's only showing results in the CSV that come from the first object returned. Some programs use different fields but I wanted to collect all the information in a single CSV file.

  • #32740

    Dave Wyatt
    Moderator

    Well, you can't change the behavior of Export-Csv, but you can make sure that the objects you're piping in all have the same list of properties (even if some of them are blank). For example, here's a tweak to your demo code that can accomplish this:

    $someVar = @()
    $someVar += Get-ChildItem C:\Windows\explorer.exe | Select-Object Name, LastWriteTime
    $list = Get-ChildItem C:\Windows\System32\*.exe | Get-Random -Count 2
    foreach ($item in $list) {
        $someVar += Get-Item $item
    }
    
    $properties = $someVar | Get-Member -MemberType Properties | Select-Object -ExpandProperty Name | Select-Object -Unique
    
    # Starting Output
    $someVar | Select-Object -Property $properties | Format-List
    $someVar | Select-Object -Property $properties | Format-Table -AutoSize
    $someVar | Select-Object -Property $properties | Export-Csv -Path C:\Reports\test1.csv -NoTypeInformation
    Write-Host "`nCSV file`n" -ForegroundColor Yellow
    Get-Content C:\Reports\test1.csv
    
  • #32742

    Mark Hammonds
    Participant

    This might get you started

    $data = @()
    $keys = Get-Item 'HKLM:\Software\Microsoft\Internet Explorer\CommandBar' 
    Foreach($key in $keys.Property){
    $data += New-Object PSObject -Property([ordered]@{RegKey = $($keys.Name);Name = $($key)})
    }
    

    Produces this

    RegKey                                                                  Name
    ------                                                                  ----
    HKEY_LOCAL_MACHINE\Software\Microsoft\Internet Explorer\CommandBar      Enabled
    HKEY_LOCAL_MACHINE\Software\Microsoft\Internet Explorer\CommandBar      ShowLeftAddressToolbar
    HKEY_LOCAL_MACHINE\Software\Microsoft\Internet Explorer\CommandBar      TextOption
    HKEY_LOCAL_MACHINE\Software\Microsoft\Internet Explorer\CommandBar      SmallIcons  
    

You must be logged in to reply to this topic.