A common scenario is generating a csv with some system or tool, then importing that data to process it with PowerShell. Many csv files will look relatively fine when opened in Excel.

Importing the csv with PowerShell seems to work fine, and Get-Member shows the expected property was generated from the header. However, when we try to access this property we’re not getting the expected results.

Taking a closer look at the csv shows that the header is actually padded with spaces.

This results in the property name of our file not being “RelativeUrl” but instead “RelativeUrl” plus a space. Trying to access our data by typing out “R-e-l-a-t-i-v-e-U-r-l” doesn’t return anything, but if we use tab completion and type “r-e-l”+Tab the autocomplete feature automatically wraps the property in quotes because of the space.

At this point, we can manually remove the spaces from the file (yuck), remember to wrap the property with quotes and add the space every time with access it (meh), or utilize the header parameter of Import-Csv to specify our own header.

Now the supplied header name becomes the property name, and we can access our data as expected. The header parameter takes an array of strings, so if the csv has multiple columns of data, multiple header names can be specified.

But now we have another problem. Our header might be correct, but now Import-Csv is treating the original header row in the file as legitimate data. Plus, the application that generated the csv added a row of dashes, probably in misguided attempt to make the file “pretty.” At some point this is where many admins will break down and manually massage the csv so they can get a clean import. This might be ok if you’re only doing this once, but if we’re building an automated routine we don’t any manual steps.

Here we take a completely different approach and skip using the Import-Csv cmdlet altogether. This allows us to utilize Select-Object to skip the first couple lines of garbage we don’t want, and we’re left with clean data.

This is nothing mind-blowing or innovative, but at this point I’ve seen so many people manually editing csv files to deal with spaces in headers and junk formatting that I figured somebody might benefit from a quick post.

Getting Started with the DSC Toolkit (Slide Deck)

DSC Demo Files

I’ve never been to Seattle, and I have to thank Aaron Nelson and Jason Helmick for being my de-facto tour guides Sunday afternoon. Walking around downtown and getting a taste of the stereotypical Seattle weather was a great start to the week, and after that first day the sun decided to come out, which seemed to be a bigger deal for the locals than someone from Florida. Sunday evening there was a community mixer that immediately clarified how different this was going to be than a typical IT conference. At any other venue, I don’t get to chat with Jeffrey Snover about DevOps, or talk to Don Jones about PowerShell content for desktop administration. This was truly a Most Valuable Professional (MVP) experience for non-MVPs.

Don Jones

I like to think I know a thing or two about PowerShell. I read about it, I write about it, I use it every day at work, but after the first session it became clear how much I don’t know. There weren’t just PowerShell gurus attending, there were C# MVPs, SQL MVPs, and senior software engineers that designed the programs I use every day. It’s quite humbling to go from being “The PowerShell Guy” at work to a conference where it feels like everyone knows more about PowerShell than me.

The summit was held at the Microsoft campus, which is a neat experience itself, but you have to hand it to them for providing such outstanding support for this event. The conference rooms, the food, the power strips everywhere for laptops was a notch above what you’d see at a typical conference. There’s no requirement for the PowerShell Team to put together a hands-on workshop, but they put in the effort, and that level of community involvement pays off. Conferences like TechEd don’t allow me to pull aside someone like Kenneth Hansen and discuss the areas where I think the PowerShell team should focus on. This kind of experience is invaluable.

Alan Renouf

While many of the attendees are well-known authors and developers in the PowerShell world, there are a ton of people that are just writing some very cool PowerShell code. The entire week I saw people cracking open laptops to show someone else what they were working on. It’s astounding to watch a conversion at the coffee station transform into a group crowding around a laptop to see how someone solved a problem at their company.

Jason Shirk

The PowerShell Summit North America 2013 was a great use of my time, and my cup runneth over with ideas for using what I’ve learned in my own organization. It was great to see old friends again, and meet a bunch of new ones. Thank you to PowerShell.org, the event sponsors, and everyone that put in long hours making things run so smoothly. This was an event to remember, and I’ll definitely be back next year.

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held Sep 29 2012 at Seminole State College, Main Campus, 100 Weldon Boulevard Bld, University Partnership Building, Sanford, FL 32773. Admittance to this event is free, but we do charge a lunch fee of 10.00 so that we can provide a lunch – not pizza! Please register soon as seating is limited, and let friends and colleages know about the event.

An Introduction to PowerShell Remoting: Part One

An Introduction to PowerShell Remoting Part Two: Configuring PowerShell Remoting

An Introduction to PowerShell Remoting Part Three: Interactive and Fan-Out Remoting

An Introduction to PowerShell Remoting Part Four: Sessions and Implicit Remoting

An Introduction to PowerShell Remoting Part Five: Constrained PowerShell Endpoints

I’ve uploaded the transcript from my PowerShell for Beginners session, and the slide deck from my Introduction to PowerShell Remoting session.

If you want to learn more about PowerShell, a great place to start is the Hey, Scripting Guy! blog from Microsoft’s scripting guy Ed Wilson. If it can be done with PowerShell, Ed has probably written about it. I also have a list of free PowerShell eBooks available, along with a list of paid books on various PowerShell topics. I also listed several resources in my post on the Jacksonville SQL Saturday.

Thanks for coming out on Saturday, and I hope to see you again at a future event!

If you liked what you saw and want to learn more, there are many resources available for learning PowerShell. I have a list of free PowerShell eBooks which is a good place to start. I also have a list of paid PowerShell books. For someone completely new to PowerShell or someone that prefers a very structured learning approach, I suggest Learn PowerShell in a Month of Lunches by Don Jones. Be sure to check out the additional resources at the book’s website, and take advantage of the companion videos Don has made available on YouTube. Someone with a scripting or programming background that’s looking for a solid reference book would do well with Bruce Payette’s book PowerShell in Action. This is a very detailed look at how PowerShell works and why certain decisions were made when designing the language.

Many PowerShell videos are available at Channel 9, including the PowerShell sessions from TechEd events. Don Jones has a four hour sample of a training class uploaded to Youtube, and Jeff Hicks has a series of videos from TrainSignal.

Have a long commute and need something to listen to? Try downloading some podcasts from the PowerScripting Podcast. Last week Michiel Wories, the Principal Architect and Lead Developer of SQL Server, was talking about SQL Server 2012.

Most of these resources focus on PowerShell in general and don’t get specific on SQL Server. For some examples on how PowerShell is used by SQL Server experts, I suggest reading the blogs from Allen White, Aaron Nelson, Chad Miller, and Max Trinidad.

For some more ideas on how PowerShell can be useful for a Database Administrtor, check out the SQL Server PowerShell Extensions. SQLPSX is an open source project for working with ADO.NET, SMO, Agent, RMO, SSIS, SQL script files, PBM, Oracle and MySQL and using Powershell ISE as a SQL and Oracle query tool.

There is a massive amount of information available, but hopefully this is enough to get you started. The PowerShell community is very active and close-knit in the way the SQL community is. Send someone an email, ask a question in a forum, or throw the twitter tag #PowerShell on your tweets, and you’re very likely to get pointed in the right direction.

In a more recent situation, I was handed a spreadsheet of computers with printer shares that needed to be removed for security reasons. After getting the list of computer names from the spreadsheet into PowerShell, I had to figure out how to remove the printer shares. I very often turn to Windows Management Instrumentation (WMI) in situations like this because I’m familiar with what it can do from my vbscript days where WMI was very often the only way to get anything done. PowerShell makes working with WMI much easier than it ever was with vbscript.

Get-WmiObject -ComputerName Computer01 -Class Win32_Printer | foreach {$_.Shared = $False; $_.Put()}

The documentation for the Win32_Printer class says the shared property is Read/write, so it can be modified. The put method is used to update the printer object. So in this example, all the printers on Computer01 are retrieved, the Shared property on each printer is set to false, and the put method is called to save the change.

Invoke-Command -ComputerName $Computers -ScriptBlock {Get-WmiObject Win32_Printer | foreach {$_.Shared = $False; $_.Put()}}

This can be done very quickly with PowerShell remoting enabled, in which case the Invoke-Command cmdlet is used. The difference here is that instead of specifying a single computer name, the $Computers variable contains a list of computer names. This allows the scriptblock to be sent to all the computers in parallel.

Get-WmiObject Win32_Printer -ComputerName Computer01 | Set-WmiInstance -Arguments @{Shared=$False}

Invoke-Command -ComputerName $Computers -ScriptBlock {Get-WmiObject Win32_Printer | Set-WmiInstance -Arguments @{Shared=$False}}

Another method is to use the Set-WmiInstance cmdlet to set properties on the printer objects.