Credit for this article is given to Tome Tanasovsk for this post regarding the same topic – https://powertoe.wordpress.com/2012/01/23/powerbits-7-copying-and-pasting-a-list-of-computers-into-a-powershell-script-as-a-collection/

Every now and then I will need to copy and paste data from Excel or a textfile straight into a PowerShell session in order to process it in some way. For instance, retrieving the office for a list of users from Active Directory. I used to convert the Excel data in a CSV file and then import that into Powershell with Import-CSV. While that is certainly an option, it also creates more work. I don’t like more work. I like less work.

So in this example, I have an Excel file with a column that looks like this:

I want to take this data and copy it straight into a collection in my PowerShell session. So, I create a variable $list with the “@” character which indicates a multi-line string. I then press enter, which will allow me to paste in the contents of my Excel column:

Now I paste the contents of my Excel column straight in my shell:

In order to split this string to make it into a collection, I will use the -split operator using the escape character (“`n”) to split each line. Then, I pipe that to a foreach loop and trim each string.

Boom. I have a fully functional collection I can now process in whatever way I please.

 

 

Categories: Quick TipTechnical

1 Comment

Jay · February 9, 2018 at 2:17 pm

I wrote a couple of simple functions to handle this. I spend a lot of time copying and pasting from email or other places a list of things. Hey can you do x to all of these things? etc…. You can check out my functions here https://gist.github.com/jaywryan/a7329c66729c0d8ef358054bed74ff37

I’m not much of a blogger, so feel free to post an update or 2.0 using those functions or ideas from those to create your own.

Great Job!

Leave a Reply

Your email address will not be published. Required fields are marked *