How to append data from one CSV to another using Export-CSV

Question

I have a script like the following:

$in_file = "C:\Data\Need-Info.csv"
$out_file = "C:\Data\Need-Info_Updated.csv"
$list = Import-Csv $in_file 
ForEach ( $user in $list ) {
    $zID = $user.zID
    ForEach-Object { 
        Get-QADUser -Service 'domain.local' -SearchRoot 'OU=Users,DC=domain,DC=local' -SizeLimit 75000 -LdapFilter "(&(objectCategory=person)(objectClass=user)(PersonzID=$zID))" | Select-Object DisplayName,samAccountName,@{Name="zID";expression={$zID}} | Export-Csv $out_file -NoTypeInformation -Force
    }
}

However, I am not able to get it to output all of the results to the $out_file since it does not seem to append the data to the csv file.

Is there a way to make this append the data to a file?

Withdrew

Answer 1

Convert the foreach loop into a foreach-object, and move the export-csv to outside the outer foreach object so that you can pipe all the objects to the export-csv.

Something like this (untested):

$list | ForEach {
    $zID = $_.zID
    ForEach-Object { 
        Get-QADUser -Service 'domain.local' -SearchRoot 'OU=Users,DC=domain,DC=local' -SizeLimit 75000 -LdapFilter "(&(objectCategory=person)(objectClass=user)(PersonzID=$zID))" | Select-Object DisplayName,samAccountName,@{Name="zID";expression={$zID}}
    }
} | Export-Csv $out_file -NoTypeInformation -Force

Nasty

Out of curiosity, how does this change the processing to allow this to work? Why would I want to use a ForEach-Object instead of a ForEach? – Withdrew

because you can pipe them all together rather than saving for each set of them – Nasty

Answer 2

-append is broken in PowerShell v2.0. You can use Dmitry Sotikovs workaround instead: http://dmitrysotnikov.wordpress.com/2010/01/19/export-csv-append/

I would however recommend manojlds excellent solution!

Jasper

There is no -Append for Export-CSV in PSv2. Though nice link. Good example of when to create a cmdlet proxy. – Engud

Answer 3

As Sune mentioned, PowerShell v3's Export-Csv has an Append flag but no character encoding protection. manojlds is correct, since your code is writing all new data to a new CSV file.

Meanwhile, you can append data to a CSV by:

1. Convert the objects to CSV with ConvertTo-Csv
2. Strip the header —and type information if necessary— and collect the CSV data only
3. Append the new CSV data to the CSV file through Add-Content or Out-File, be sure to use same character encoding

Here is a sample:

1..3 | ForEach-Object {
 New-Object PSObject -Property @{Number = $_; Cubed = $_ * $_ * $_}
} | Export-Csv -Path .\NumTest.csv -NoTypeInformation -Encoding UTF8

# create new data
$newData = 4..5 | ForEach-Object {
 New-Object PSObject -Property @{Number = $_; Cubed = $_ * $_ * $_}
} | ConvertTo-Csv -NoTypeInformation

# strip header (1st element) by assigning it to Null and collect new data
$null, $justData = $newData

# append just the new data
Add-Content -Path .\NumTest.csv -Value $justData -Encoding UTF8

# create more new data, strip header and collect just data
$null, $data = 6..9 | ForEach-Object {
 New-Object PSObject -Property @{Number = $_; Cubed = $_ * $_ * $_}
} | ConvertTo-Csv -NoTypeInformation

# append the new data
Add-Content -Path .\NumTest.csv -Value $data -Encoding UTF8

# verify
Import-Csv .\NumTest.csv

# clean up
Remove-Item .\NumTest.csv

Conchoid

Answer 4

After version 3 you can use:

Export-Csv $out_file -append -notypeinformation -encoding "unicode"

Phatic