Export-CSV -Append

Here’s the solution for those who need to append rows to existing CSV files (and cannot do that): I have just used PowerShell 2.0 code snippets to create a proxy cmdlet – function which wraps standard Export-CSV cmdlet but adds handling of the -Append parameter.

So you can do something like:

Get-Process | Export-Csv -Path 'c:\Temp\processes.csv' -Append -Delimiter ';'

As you can see, other parameters – such as Delimiter – still function as well. If the file does not exist – the cmdlet will essentially ignore -Append and create the file as normal. If you specify -Append and the file is present, the function will turn the objects into CSV strings, remove the first row with the property names and append to the existing file.

For your convenience, I have posted the source code to PoshCode. Here’s it is as well for those interested:

#Requires -Version 2.0

<#
  This Export-CSV behaves exactly like native Export-CSV
  However it has one optional switch -Append
  Which lets you append new data to existing CSV file: e.g.
  Get-Process | Select ProcessName, CPU | Export-CSV processes.csv -Append
  
  For details, see

http://dmitrysotnikov.wordpress.com/2010/01/19/export-csv-append/

  (c) Dmitry Sotnikov  
#>

function Export-CSV {
[CmdletBinding(DefaultParameterSetName='Delimiter',
  SupportsShouldProcess=$true, ConfirmImpact='Medium')]
param(
 [Parameter(Mandatory=$true, ValueFromPipeline=$true,
           ValueFromPipelineByPropertyName=$true)]
 [System.Management.Automation.PSObject]
 ${InputObject},

 [Parameter(Mandatory=$true, Position=0)]
 [Alias('PSPath')]
 [System.String]
 ${Path},
 
 #region -Append (added by Dmitry Sotnikov)
 [Switch]
 ${Append},
 #endregion 

 [Switch]
 ${Force},

 [Switch]
 ${NoClobber},

 [ValidateSet('Unicode','UTF7','UTF8','ASCII','UTF32',
                  'BigEndianUnicode','Default','OEM')]
 [System.String]
 ${Encoding},

 [Parameter(ParameterSetName='Delimiter', Position=1)]
 [ValidateNotNull()]
 [System.Char]
 ${Delimiter},

 [Parameter(ParameterSetName='UseCulture')]
 [Switch]
 ${UseCulture},

 [Alias('NTI')]
 [Switch]
 ${NoTypeInformation})

begin
{
 # This variable will tell us whether we actually need to append
 # to existing file
 $AppendMode = $false
 
 try {
  $outBuffer = $null
  if ($PSBoundParameters.TryGetValue('OutBuffer', [ref]$outBuffer))
  {
      $PSBoundParameters['OutBuffer'] = 1
  }
  $wrappedCmd = $ExecutionContext.InvokeCommand.GetCommand('Export-Csv',
    [System.Management.Automation.CommandTypes]::Cmdlet)
        
        
 #String variable to become the target command line
 $scriptCmdPipeline = ''

 # Add new parameter handling
 #region Dmitry: Process and remove the Append parameter if it is present
 if ($Append) {
  
  $PSBoundParameters.Remove('Append') | Out-Null
    
  if ($Path) {
   if (Test-Path $Path) {        
    # Need to construct new command line
    $AppendMode = $true
    
    if ($Encoding.Length -eq 0) {
     # ASCII is default encoding for Export-CSV
     $Encoding = 'ASCII'
    }
    
    # For Append we use ConvertTo-CSV instead of Export
    $scriptCmdPipeline += 'ConvertTo-Csv -NoTypeInformation '
    
    # Inherit other CSV convertion parameters
    if ( $UseCulture ) {
     $scriptCmdPipeline += ' -UseCulture '
    }
    if ( $Delimiter ) {
     $scriptCmdPipeline += " -Delimiter '$Delimiter' "
    } 
    
    # Skip the first line (the one with the property names) 
    $scriptCmdPipeline += ' | Foreach-Object {$start=$true}'
    $scriptCmdPipeline += '{if ($start) {$start=$false} else {$_}} '
    
    # Add file output
    $scriptCmdPipeline += " | Out-File -FilePath '$Path'"
    $scriptCmdPipeline += " -Encoding '$Encoding' -Append "
    
    if ($Force) {
     $scriptCmdPipeline += ' -Force'
    }

    if ($NoClobber) {
     $scriptCmdPipeline += ' -NoClobber'
    }   
   }
  }
 } 
  

  
 $scriptCmd = {& $wrappedCmd @PSBoundParameters }
 
 if ( $AppendMode ) {
  # redefine command line
  $scriptCmd = $ExecutionContext.InvokeCommand.NewScriptBlock(
      $scriptCmdPipeline
    )
 } else {
  # execute Export-CSV as we got it because
  # either -Append is missing or file does not exist
  $scriptCmd = $ExecutionContext.InvokeCommand.NewScriptBlock(
      [string]$scriptCmd
    )
 }

 # standard pipeline initialization
 $steppablePipeline = $scriptCmd.GetSteppablePipeline(
        $myInvocation.CommandOrigin)
 $steppablePipeline.Begin($PSCmdlet)
 
 } catch {
   throw
 }
    
}

process
{
  try {
      $steppablePipeline.Process($_)
  } catch {
      throw
  }
}

end
{
  try {
      $steppablePipeline.End()
  } catch {
      throw
  }
}
<#

.ForwardHelpTargetName Export-Csv
.ForwardHelpCategory Cmdlet

#>

}

Hope this helps! ;)

About these ads

31 Responses to “Export-CSV -Append”


  1. 1 Jason Archer January 19, 2010 at 10:25 pm

    Reader beware, you can have some problems if you append a different kind of object (or number of columns) than what is already in the file.

    • 2 Dmitry Sotnikov January 20, 2010 at 7:02 am

      Yes, good point Jason! In my code I am NOT checking whether the property names are the same. Or that delimiter is the same. Or that encoding is the same. And so on. It is up to user to make sure that appended data matches the original one.

      • 3 Tim August 4, 2011 at 2:50 pm

        How exactly would one go about formatting data to match the original? For example:

        Data in csv is from SMO dataFile object containing server, dbname, size, usedSpace, freespace.

        I want to append a TOTAL row for each instance after all of the databases have been appended. I have tried building my own object/string and appending it to the csv, but I can’t seem to get it right. Any help would be much appreciated.

        Here is what I have tried:
        $totalString = “@{Server=$servername; dbname=$total; Size=$totalsize; UsedSpace=$totalUsed; FreeSpace=$totalFree}”;
        (above, with and without, @, {}, and quotes)

        $totalString = @”
        Server : $servername
        dbname : $total
        Size : $totalsize
        UsedSpace : $totalUsed
        FreeSpace : $totalFree
        “@

        $totalstring | export-csv c:\test.csv -append;

        Thanks,
        TIm

  2. 4 John Huber February 4, 2011 at 4:49 pm

    Thanks, I am an admin, who is not a good coder. This is a great help for me and makes my life a lot easier.

    Many Thanks

  3. 6 Alvin Magpayo April 7, 2011 at 11:07 pm

    Hi,
    Im quite new with powershell and this post regarding Export-CSV with append is very helpful to complete my automated script reports.

    I just don’t know how to use it. What do you mean with “Add this function to your profile to make Export-CSV cmdlet handle -Append parameter” in the poshcode.org?

    I just need to make it work and use the -Append of Export-CSV and time is killing me due to my deadline.

    Thanks in advance.

  4. 8 Alvin Magpayo April 9, 2011 at 8:09 am

    Thanks. It worked! I was able to do it and had the -Append on Export-CSV.

    One more question though, What if I want to make sure that this profile will be used all the time? My script will run as a scheduled job and placed on different server.

    Is there a way that I can call the same profile just to make sure that the code will be executed, or is it possible that I can include that at the top of the code as one of the validation process?

    Thanks for the help. :D

    Alvin.

  5. 9 Alvin Magpayo April 9, 2011 at 12:20 pm

    ei,
    I got it working. Thanks. Appreciate the help. :)

    Alvin

  6. 11 Jamie May 5, 2011 at 9:33 pm

    I am having trouble using this script. When I use the command, I get an error saying “The varialbe ‘$UseCulture’ cannot be retrieved because it has not been set”. The error references line 96 and char 21. Please help.

    Thanks,
    Jamie

    • 12 Dmitry Sotnikov May 5, 2011 at 11:02 pm

      Jamie,

      Looks like you have strict mode on, and this somehow does not like the parameter declarations of the function.

      One easy workaround that I see is to simply add Set-StrictMode -Off to the biginning of the script… If this is unacceptable, you’ll need to figure out how to make the PowerShell parser consider the parameters ok to use in the script – let me know if you manage to do that.

      Dmitry

  7. 13 Chad Miller May 21, 2011 at 2:10 am

    Dimitry

    I’m wondering if you can help me understand this piece of code in from your script (modified slightly to illustrate an example):

    get-psdrive | foreach-object {$start=$true} {if ($start) {$start=$false} else {$_}}

    I understand the point–skip first row and it works perfectly. I’ve seen scriptblocks before, but this one looks odd.

    The first block is the foreach-object block and the second block, well I don’t understand how the pipeline is able to continue to pipe objects.

  8. 14 Chad Miller May 21, 2011 at 8:11 pm

    My previous question was answered via Twitter by Bartek

    “Looks like -begin and -process blocks. So the {$start = $true} is run only once, before obj arrive”

    Sure enough get-help foreach-object mentions these params, but the documentation incorrectly states they are named params.

  9. 15 JCochran December 8, 2011 at 9:12 pm

    Am I missing something? While I greatly appreciate this code, and I could not code myself out of a brown paper bag, isn’t this an excessive amount of code to simply append a file? I mean that with all due respect and I am more than likely missing something, but this seems counter to the simplicity of the intended nature of PowerShell. I am one person that is finding PowerShell to be powerful, but not all that simple. I rarely have time to code and read and it seems like a significant amount of time is required to learn PowerShell to this degree. Maybe I’m dumb? I dunno… :p

  10. 16 Daniel Rymer January 25, 2012 at 2:27 pm

    Code works great. I tried writing my own using 2 additional temp files, which was far shorter, but kept ending up with blank lines I couldn’t seem to remove. I used this code and the file came out perfect! Thanks!

  11. 18 Joe July 27, 2012 at 12:36 pm

    Ever seen this cmdlet put asian characters in the resulting file and if so, how to fix?

    • 19 Joe July 28, 2012 at 2:50 pm

      More information.

      It only does this when I use out-file to same .csv file prior to using export.csv in same script run. If I run it stand alone, all is well.

  12. 20 Claxtonator December 21, 2012 at 4:09 pm

    Dude, I have been fighting this for quite a while (weeks) and finally ran across your solution. It’s BEAUTIFUL! Thank you.

  13. 21 Prince January 18, 2013 at 2:36 pm

    this is excellent Script

    How do i output Horizontal instead of Vertical, like this
    ProcessName AppStorWin32Agent ccSvcHst ccSvcHst cissesrv clussvc cmd cmd cmd cmd cmd coda conhost
    CPU 3.1980205 29238.84623 0.2652017 1.2636081 31936.86792 0 0.0156001 0 0.0156001 0.0156001 27.0193732 0.0624004

  14. 22 Denis April 18, 2013 at 10:29 pm

    Hey, Dmitry. Thanks a lot! Very helpful and handy script.

  15. 23 Chaitanyakumar G June 3, 2013 at 9:45 am

    awesome script dude, works like charm.. thanks for sharing this..

  16. 24 Jeremy Green June 21, 2013 at 9:40 pm

    Worked like a charm! Simply added it to my Powershell profile using PowerGUI, restarted Powershell, and worked right out of the box (PS 2.0 on Win2K8). Thanks!

  17. 25 ukiguy May 15, 2014 at 10:47 am

    This worked great. Thank you.


  1. 1 Appending to a CSV file - Richard Siddaway's Blog Trackback on January 19, 2010 at 8:32 pm
  2. 2 PowerShell Week at SQL University Post 6 - Formatting Results | SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Trackback on January 23, 2011 at 6:08 pm
  3. 3 Ingmar Verheij – The dutch IT guy » PowerShell: Query Cisco (Catalyst 4500) Flow Control statistics » Ingmar Verheij - The dutch IT guy Trackback on June 6, 2012 at 7:21 am
  4. 4 Export-CSV with append | SuneWorld Trackback on June 11, 2012 at 11:24 am
  5. 5 Export-CSV -Append #powershell « VM.Blog. Trackback on June 23, 2012 at 8:01 pm
  6. 6 Notes on getting Event Log information with powershell « Charlie Slack's Web Site Trackback on August 13, 2012 at 12:14 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




My Recent Tweets

Legal

The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer - WSO2 or anyone else for that matter. All trademarks acknowledged.

© 2007-2014 Dmitry Sotnikov

January 2010
M T W T F S S
« Dec   Feb »
 123
45678910
11121314151617
18192021222324
25262728293031

Follow

Get every new post delivered to your Inbox.

Join 2,329 other followers

%d bloggers like this: