Pasting multiple cells into Excel from PowerShell

Sometimes the learning point from working on a misbehaving Sitecore server isn’t related to the CMS. Recently I learned something useful about Excel while I was addressing some other issues. Not sure if this is “so trivially simple I’m just the last one to realise” or whether it’s a really useful bit of trivia – but just because someone else might benefit:

I was monitoring an issue with a Sitecore server recently, and to keep track of what had been going on, I’d knocked up a graph in Excel. Every so often I was looking at a certain data value on the server, and then copy/pasting that into the spreadsheet along with the date/time of when the measurement was taken.

Having done that a few times I was struck by the urge to make my life simpler. For… reasons… it wasn’t practical to have PowerShell writing out a CSV file to import into Excel in one go – so could I get PowerShell to give me the date and the value on my clipboard, so I’d just have to paste it into my spreadsheet?

Yes of course:

First up, getting the current date/time is handled by the “Get-Date” commandlet. It happened that the server I was using was configured for a US locale, and being a Brit that meant I wanted my date formatted differently to the default output. Not a problem – you just need to tell PowerShell how you’d like your date using standard .Net format string data:

$timeStamp = Get-Date -Format "dd/MM/yyyy hh:mm"

Grabbing the actual measurement isn’t relevant here, but I squirrelled that data away in a variable too:

$value = /\* someMagicToMeasureAValue \*/

And you can send stuff to the clipboard automatically with the “Set-Clipboard” commandlet.

But my spreadsheet was formatted with a column for the timestamp and a column for the value, so I could plot a graph from the data. So how do you get the data onto the clipboard in the right format so that pasting into Excel will make sure it goes into two cells?

Well after some fun with Google, it turns out this is kind of obvious in retrospect: You just tab-separate your data. In PowerShell “`t” (back-tick, followed by “t”) is a tab character. And that means I can send my two columns of data to the clipboard with:

"$timestamp `t $value" | Set-Clipboard

And if I hit paste into Excel, I get:

And you can put multiple tabs in if you want to paste more than one column…