Export-CSV losing data

This topic contains 4 replies, has 4 voices, and was last updated by Profile photo of Simeon Iksanov Simeon Iksanov 10 months, 1 week ago.

  • Author
  • #33072
    Profile photo of John Mooper
    John Mooper

    Hi. I have a script that's collecting data and storing them in an array of PSObjects. There's variable count of NoteProperty members being added for each PSObject, ie. one object can have 3 NoteProperties, another can have 5 etc.

    I want to store the output in a CSV file, but when I use $output | Export-CSV file.csv -NoTypeInformation, where $output is the array, it will only ouput as many properties as the first object in the array has. So for the above example if first object has 3 noteproperties, then only 3 noteproperties are exported for all objects (even if they have more).
    I presume the reason is (in my layman terms) that the CSV file header is formed when first object to be exported is read and it's not "updated" when an object with more noteproperties is encountered?

    How can I solve this?

    Thanks in advance

  • #33073
    Profile photo of Noam lesnik
    Noam lesnik


    the behavior you described is listed in the notes section of the export-csv documentation:
    When you submit multiple objects to Export-CSV, Export-CSV organizes the file based on the properties of the first object that you submit. If the remaining objects do not have one of the specified properties, the property value of that object is null, as represented by two consecutive commas. If the remaining objects have additional properties, those property values are not included in the file.

    You can create all your objects with all the noteproperties and set the value to $null if there is no value.

  • #33079
    Profile photo of John Mooper
    John Mooper

    Thanks Noam, that confirmed my suspicion.
    The workaround you suggested is not ideal since I don't know the noteproperties names and their values ahead (as they're generated dynamically), but it's acceptable in this case – I populate those dynamically generated and add more with null values until a certain count that should never be reached over.

    I guess more elegant option would be to sort the array based on the number of noteproperties in each object (if that can be done).

  • #33172
    Profile photo of Max Kozlov
    Max Kozlov

    If you don't know the names of your properties what column names you want ?
    for example
    $a = [pscustomobject]@{ PropertyA = 'aaa' }
    $b = [pscustomobject]@{ PropertyB = 'bbb' }

    what column count you want ? one ? what its name ? PropertyA or PropertyB ?

    if you want two columns (PropertyA and PropertyB) with $null in unused columns you can maintain all column names in array and use it in export-csv this way

    # add column names in data generation cycle
    $columns = 'PropertyA', 'PropertyB'
    # export data with selected set of columns
    $data | Select-Object $columns | Export-Csv [....]
  • #33205
    Profile photo of Simeon Iksanov
    Simeon Iksanov

    You can try to store all your objects in XML

You must be logged in to reply to this topic.