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?

TylerH · Accepted Answer · 2021-12-18 00:14:07Z

11

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

edited Dec 18, 2021 at 0:14

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Feb 9, 2012 at 23:15

manojlds

303k66 gold badges482 silver badges426 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Over a year ago

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?

manojlds Over a year ago

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

zx38 · Accepted Answer · 2012-02-10 04:50:57Z

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:

Convert the objects to CSV with ConvertTo-Csv
Strip the header —and type information if necessary— and collect the CSV data only
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

TylerH · Accepted Answer · 2021-12-18 00:14:46Z

4

-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!

edited Dec 18, 2021 at 0:14

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Feb 9, 2012 at 23:41

Sune

3,32016 gold badges55 silver badges64 bronze badges

1 Comment

Andy Arismendi Over a year ago

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

Frank · Accepted Answer · 2017-11-13 17:26:54Z

2

After version 3 you can use:

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

answered Nov 13, 2017 at 17:26

Frank

1311 silver badge2 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related