PowerShell Problem Solver: Importing CSV Files into PowerShell

Posted on December 17, 2014 by Jeff Hicks in PowerShell with 0 Comments

I see a lot of posts, questions and scripts that involve importing a CSV file into PowerShell. On one hand, working with a CSV file couldn’t be easier. On the other hand, PowerShell will happily turn any imported CSV file into a collection of objects, where working with those objects can be a little confusing if you don’t completely understand the process. I think part of the problem PowerShell beginners face is that they are still thinking about text, especially when importing a CSV file. Let me show you what I’m talking about.

Here’s a sample CSV file that includes test data I want to use in PowerShell.

I’ll import once and save the results to a variable so I don’t have to keep re-importing the data.

As you can see, each value in the CSV header became a property name.

Each value in the CSV header becomes a property name. (Image Credit: Jeff Hicks)

Each value in the CSV header becomes a property name. (Image Credit: Jeff Hicks)

I recommend you make sure the column entries in the header line do not have any spaces. It isn’t illegal, but it makes working with the resulting properties a bit more difficult as you have to quote the property name.

Next, let’s say I want to get results where the size is less than 0. Looking at the output, you might think this will work:

Given the first screenshot you would expect to see at least Data_2 with a size value of -30. But this isn’t the case.

Grabbing the size of $data in PowerShell. (Image Credit: Jeff Hicks)

Grabbing the size of $data in PowerShell. (Image Credit: Jeff Hicks)

The only result is an object with nothing defined for the size property. When you face this situation, this is where Get-Member is useful. Exactly what type of objects are in $data?

Using Get-Member in Windows PowerShell. (Image Credit: Jeff Hicks)

Using Get-Member in Windows PowerShell. (Image Credit: Jeff Hicks)

Notice that all of the properties are treated as strings. This means that any operation that relies on something other than a string will fail to run properly.

The date sort has failed. (Image Credit: Jeff Hicks)

The date sort has failed. (Image Credit: Jeff Hicks)

The date sort is wrong, and I shouldn’t be seeing anything with a test value of false. One way around this issue is to tell PowerShell to treat properties as a certain type. Let’s go back to my first filtering expression.

I’m using the –as operator and telling PowerShell to treat the size property as an integer. Now I get the result I expect.

Using the -as property in Windows PowerShell. (Image Credit: Jeff Hicks)

Using the -as property in Windows PowerShell. (Image Credit: Jeff Hicks)

You have to do this sort of type conversion whenever you are doing something with the property. However, using –as doesn’t always work. If you try this with my test data, then you’ll see that it won’t work.

An alternative is to use one of the built-in methods to convert the value.

Using the System.IFormatProvider method in Windows PowerShell. (Image Credit: Jeff Hicks)

Using the System.IFormatProvider method in Windows PowerShell. (Image Credit: Jeff Hicks)

You’ll see that all of these methods require a parameter value, which helps PowerShell determine how to format the value. For our purpose, this System.IFormatProvider method comes down to two choices: a number or data.

The easiest way is to reference them directly with the .NET Framework. We already know that the size property will be treated as a string. But with a little help, we can tell PowerShell to treat it as an integer.

Telling Windows PowerShell to treat size value as a integer. (Image Credit: Jeff Hicks)

Telling Windows PowerShell to treat size value as a integer. (Image Credit: Jeff Hicks)

Now I can run a command like this:

I will admit this is a bit advanced, but it works.

It is important for you to remember that you haven’t changed the underlying objects in $data, only how they are being processed in your PowerShell expression.

Sponsored

As you can see, I still have string properties.

String properties in Windows PowerShell. (Image Credit: Jeff Hicks)

String properties in Windows PowerShell. (Image Credit: Jeff Hicks)

One way to avoid all of this is to give your newly imported objects the proper type from the very beginning. Allow me to share an advanced function of mine called Import-TypedCSV.

Without going into extreme detail, I’ve tried to thoroughly document the function, where my command looks at the first line of data in the CSV file and attempts to determine the appropriate object type using regular expressions.

Sponsored

If any of the properties for the first object have no value, you will be prompted to specify the object type.

Prompting you to provide the object type in PowerShell. (Image Credit: Jeff Hicks)

Prompting you to provide the object type in PowerShell. (Image Credit: Jeff Hicks)

But now that everything is properly typed, using the data is much easier.

Properly typed objects in Windows PowerShell. (Image Credit: Jeff Hicks)

Properly typed objects in Windows PowerShell. (Image Credit: Jeff Hicks)

I do have yet one more alternative you might want to consider, especially if you often import the same type of CSV file repeatedly. It is definitely an advanced technique, which I’ll guide you through in a future article.

Sponsored

Tagged with , ,