Tome's Land of IT

IT Notes from the Powertoe – Tome Tanasovski

How to Get Data into an Excel Spreadsheet Very Quickly with PowerShell

Make sure you also read part II of this article that shows another technique to satisfy the same requirement, i.e., getting data into an Excel spreadsheet very quickly with PowerShell.

Having spent two years of my life developing and supporting a couple of successful Outlook add-ins back in 2003 I felt at ease taking on the burden of writing a chapter in the PowerShell Bible (due in Summer 2011) on the Office COM objects.  Prior to this I had only dabbled in the Excel.Application for simple things here and there; I had never really hit the Excel objects hard.  One thing I noticed very quickly is that to go cell by cell to insert data is extremely slow.  To be honest, it’s kind of fun to watch the cells populate one at a time the first time you do it.  It’s like a little mini stop motion Excel movie where color and formatting turn your little project into a Sesame Street number counting cut scene.  A fun little movie, but it becomes clear that it’s not practical to create an enterprise-class document.  There are methods to tackle this problem, but I wanted to take a moment to shine a light on a very elegant solution I thought of over dinner with my family while pondering this question that was posed in the Windows PowerShell forum.

Copy/Paste

The process is simple.  Take an object and use convertto-csv to turn it into tab delimited text, put that text into your clipboard, open Excel, create a new worksheet (or open an existing one), and paste the data into the cells you want to put the data into.  It’s really quick, and it allows you to maintain your formatting for the cells.  Here’s some code that does this.  It pastes the output from a Get-ChildItem (dir) of the c-drive into a new xlsx document into B5 down to B5 + the number of rows in my data:

$dir = dir c:\
$dir |ConvertTo-Csv -Delimiter "`t" -NoTypeInformation|out-clipboard
$excel = New-Object -ComObject Excel.Application
$excel.visible = $true
$workbook = $excel.Workbooks.Add()
$range = $workbook.ActiveSheet.Range("b5","b$($dir.count + 5)")
$workbook.ActiveSheet.Paste($range, $false)
$workbook.SaveAs("c:\output.xlsx")

The following example is the one used to anwer the forum post.  It’s nearly identical, but it uses Get-Process, and it opens an existing Excel document:

$procs = Get-Process
$procs |ConvertTo-Csv -Delimiter "`t" -NoTypeInformation |out-clipboard
$excel = New-Object -ComObject Excel.Application
$excel.visible = $true
$workbook = $excel.Workbooks.Open("C:\Dropbox\My Dropbox\scripts\test.xlsx")
$range = $workbook.ActiveSheet.Range("b5","b$($procs.count + 5)")
$workbook.ActiveSheet.Paste($range, $false)

There’s one small problem with the technique outlined in these samples.  It relies on you having an Out-Clipboard cmdlet that you can pipe data into.  Fortunately there are two places you can get such a cmdlet.

Out-Clipboard

The PowerShell Community Extensions have this cmdlet in their module.  My only problem with PSCX is that it’s very bulky, and requires a lot of customization to strip out the things you don’t want.  I personally prefer to maintain code in my own modules where possible.  You can grab the code out of PSCX if you desire – it is open source after all.

My recent preference is to use the Set-Clipboard script that Lee Holmes uses in his “PowerShell Cookbook, version 2“.  You can download the code directly from poshcode and create your own clipboard module by wrapping the text of the script into a function like this:

function out-clipboard {
    #############################################################################
    ##
    ## Set-Clipboard
    ##
    ## From Windows PowerShell Cookbook (O'Reilly)
    ## by Lee Holmes (http://www.leeholmes.com/guide)
    ##
    ##############################################################################

    <#
    .SYNOPSIS

    Sends the given input to the Windows clipboard.

    .EXAMPLE

    dir | Set-Clipboard
    This example sends the view of a directory listing to the clipboard

    .EXAMPLE

    Set-Clipboard "Hello World"
    This example sets the clipboard to the string, "Hello World".

    #>

    param(
        ## The input to send to the clipboard
        [Parameter(ValueFromPipeline = $true)]
        [object[]] $InputObject
    )

    begin
    {
        Set-StrictMode -Version Latest
        $objectsToProcess = @()
    }

    process
    {
        ## Collect everything sent to the script either through
        ## pipeline input, or direct input.
        $objectsToProcess += $inputObject
    }

    end
    {
        ## Launch a new instance of PowerShell in STA mode.
        ## This lets us interact with the Windows clipboard.
        $objectsToProcess | PowerShell -NoProfile -STA -Command {
            Add-Type -Assembly PresentationCore

            ## Convert the input objects to a string representation
            $clipText = ($input | Out-String -Stream) -join "`r`n"

            ## And finally set the clipboard text
            [Windows.Clipboard]::SetText($clipText)
        }
    }
}

Note: I name my function Out-Clipboard even though I use Lee’s Set-Clipboard script because I relied on the PSCX cmdlet so heavily before I received the snippit from Lee that I couldn’t think of calling it anything else.

ZAP!  Lightning fast data transfer directly into an Excel Spreadsheet!

Check out the follow up article that keeps the speed going

Advertisements

4 responses to “How to Get Data into an Excel Spreadsheet Very Quickly with PowerShell

  1. LucD November 5, 2010 at 4:12 am

    You can do this without starting a PowerShell subprocess in STA mode.
    See my recently updated http://www.lucd.info/2010/05/29/beyond-export-csv-export-xls/ post.
    Luc

  2. Lincoln Atkinson November 5, 2010 at 12:24 pm

    Cool trick. I agree, way too slow doing cell-by-cell looping if you have any significant amount of data.

    Tip: You don’t need an Out\Set-Clipboard cmdlet. Just use Windows standard clip.exe, which is in the %path%, and is way less heavyweight than spooling a whole new powershell.exe process.
    PS > dir | clip

    Sadly, there is no equivalent to read FROM the clipboard, so Get-Clipboard is still needed…

  3. Tome November 5, 2010 at 5:00 pm

    hmm… clip.exe is neat. I’m going to have to play with that. I’ve noticed that Lee’s set-clipboard occasionally gives a random error, but I’m committed not to go back to pscx for this.

    LucD, I swore I tried that method a while ago and it required sta. Regardless, check out today’s post (coming in a few minutes). I’m not claiming it’s anything unique, but it’s another method to insert a ton of data really quickly into Excel without using the clipboard. It might be more suitable for your function since it will not overwrite your clipboard while using the function. Then again, you could handle this by storing the clipboard in a variable prior to using your function and then setting the clipboard back when you’re done.

  4. Pingback: How to Get Data into an Excel Spreadsheet Very Quickly with PowerShell – Cont’d « Tome's Land of IT

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

%d bloggers like this: