DEV Community

Jeremy Davis
Jeremy Davis

Posted on • Originally published at blog.jermdavis.dev on

2

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"
Enter fullscreen mode Exit fullscreen mode

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

$value = /\* someMagicToMeasureAValue \*/
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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…

Please leave your appreciation by commenting on this post!

It takes just one minute and is worth it for your career.

Get started

Top comments (1)

Collapse
 
ojarsbuddy profile image
John Bortins

Thanks! Inspired to try something similar with Notepad++ and LibreOffice Calc. So wonderfully simple!

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay