export array of PS object with different columns to csv

Question

I have a little issue with an array exported. I have an array that contains objects with different fields (a total of 12 fields) and I can't export all the fields to CSV using Export-CSV and Out-File.

For the purpose of the example:

$logs = @()
$logs | export-csv -path c:\test.csv --NoTypeInformation

This is the contents of the array ($ Logs):

$logs[0]
LogTime             : 2017-03-20 07:10:32.917
LogName             : Network connection detected (rule: NetworkConnect)
DeviceName          : K***M.w****l.local
ProcessGuid         : {297FCCB8-2783-58C8-0000-0010EB030000}
ProcessId           : 4
User                : NT AUTHORITY\SYSTEM
DestinationHostname : W***C
DestinationIp       : 10.**.***.6
DestinationIsIpv6   : false
DestinationPort     : 445
DestinationPortName : microsoft-ds
Image               : System
Initiated           : true
Protocol            : tcp
SourceHostname      : K**M.w***l.local
SourceIp            : 10.**.***.11
SourceIsIpv6        : false
SourcePort          : 53786

$logs[1]
LogTime           : 2017-03-20 11:14:05.553
LogName           : Process Create (rule: ProcessCreate)
DeviceName        : K***M.w***l.local
LogonId           : 0x1a84303
ParentCommandLine : C:\Windows\Explorer.EXE
ParentImage       : C:\Windows\explorer.exe
ParentProcessGuid : {297FCCB8-4358-58C9-0000-00103AB04800}
ParentProcessId   : 2884
ProcessGuid       : {297FCCB8-B97D-58CF-0000-00100D4EA801}
ProcessId         : 2440
TerminalSessionId : 2
User              : W***L\administrator
Image             : \\10.10.**0.6\share\Installs\AVG_Protection_Free_1606.exe
CommandLine       : "\\10.10.**0.6\share\Installs\AVG_Protection_Free_1606.exe" 
CurrentDirectory  : \\10.10.**0.6\share\Installs\
Hashes            : SHA1=E1CC52656F0AB86757E551C4181E4E11D6B2C811,MD5=8E1B5B613267120F1A6979021B0A1ED7,SHA256=6FE9ADD42C149CC4697124293A764B2CFD908F4A3CE6E88BAB35D5CF85620EC6,IMPHASH=D8843771C5D1046A951FECEB11DD00A8
IntegrityLevel    : High
LogonGuid         : {297FCCB8-B971-58CF-0000-00200343A801}

How can I export it to CSV so that columns are not missed?

Thanks!

Does this answer your question? [Not all properties displayed](https://stackoverflow.com/questions/44428189/not-all-properties-displayed), something like `$logs | Union-Object | export-csv ...` — iRon, Feb 24 '21 at 10:54
What is the purpose of gathering these different objects in one CSV file? You might be better off storing different kinds (different subclasses) in different files, and only forming the union later, when you intend to use the data in some combined way. The same discussion comes up i n relational databases, when there are subclasses with different attributes. — Walter Mitty, Feb 24 '21 at 11:16

score 0 · Answer 1 · answered Feb 24 '21 at 13:25

Export-Csv determines the columns from the property names of the first object passed, as noted in the documentation. To circumvent this, you can use -Append -Force to write to an existing CSV file with all column headers already specified in it. You can add these manually, or to extract each unique property name from all objects in $logs, you can use something like:

$OutColumns = $logs | ForEach-Object {$_.PSObject.Properties.Name} | Select-Object -Unique
$OutColumns -replace '"','""' -join '","' -replace '^|$','"' | Out-File C:\test.csv

If you're working with large amounts of data, the following may be a little faster:

$OutColumns = [System.Collections.Generic.List[string]]::new()
foreach ($obj in $logs) {
    foreach ($propName in $obj.PSObject.Properties.Name) {
        if ($OutColumns -notcontains $propName) {
            $OutColumns.Add($propName)
        }
    }
}
$OutColumns -replace '"','""' -join '","' -replace '^|$','"' | Out-File C:\test.csv

You can then append the $logs entries to this file with:

$logs | Export-Csv -Path C:\test.csv -NoTypeInformation -Append -Force

The -Force parameter is required with -Append here, else it will throw an error about mismatched property names.

Also note that when importing this again, every child object will have every property, but the missing ones will be empty strings. For this reason, as mentioned in the comments, it may be better to separate objects with different property sets.

export array of PS object with different columns to csv

1 Answers1