In this post, I explain how to create a PowerShell function to process CSV data. This allows you to reuse your code whenever you are working with CSV files in PowerShell.

Josh Rickard

Josh's primary focus is in Windows security and PowerShell automation. He is a GIAC Certified Windows Security Administrator (GCWN) and GIAC Certified Forensic Analyst (GCFA). You can reach Josh at MSAdministrator.com or on Twitter at @MS_dministrator.

CSV (Comma-Separated Values) is used by almost every technology platform that we encounter. Manipulating this data can be cumbersome if you're NOT an Excel wizard, but PowerShell can simplify this job. For example, let's take a CSV with the following data:

CSV example of expected input

CSV example of expected input

We have been asked to modify this data and reorder where the data resides in the new CSV. We have been given an example of what the output should look like:

CSV example of expected output

CSV example of expected output

I always encourage people to create a function if they believe they will reuse it in the future, and I'm guessing we will be asked to provide this information again. First, I open the PowerShell ISE and then use the Crtl+J shortcut to pull up my Snippets. Next, I select Cmdlet (Advanced Function), which creates the outline for my new PowerShell function.

PowerShell ISE Snippets are always a good starting point for any new tool

PowerShell ISE Snippets are always a good starting point for any new tool

Next, we change the name of our function and add some parameters. Remember, you should always reference Get-Verb when deciding on a new function name.

The first thing we should do is create an object to hold any changes that we make to our CSV data. This will allow us to manipulate all the data, and then we will be able to export it to a new CSV. I like to add this to the Begin block, even though in this case it is not needed since we are only processing one CSV at a time. The Begin block should look like this:

Now, we have the general structure of our function. Next, we need to figure out how to modify the existing fields and add new ones. Luckily, Export-CSV has some really great examples that seem to fit our needs. Based on those examples, we can use a PSCustomObject to create our CSV headers and their corresponding values.

Since we know that our CSV headers should be "ID,""DEPARTMENT," "COMPLETED_DATE," and "NAMES," we use these as the property names for our object. We also expect more than one entry in our CSV, so we create a temporary PSCustomOjbect. For every iteration of the loop, we add the newly created temporary object to our $ReturnObject. To do this, we add the following to our foreach loop:

We're almost there, but first we need to access the values of the imported CSV. To do this, we reference the header of the CSV that was imported and add it to the property of our temporary custom object. You may have noticed that we are creating an [ordered] object. This is because it is required that our new CSV have values in a specified order (see above). This is how we access the values and set them in our temporary object:

To explain what we are doing, I'll start from the inner-most line of the foreach loop. First, we save the value in HD_OUTPUT_ID to a variable so that we can pad its value when we create our temporary object.

Next come lines borrowed from Hey Scripting Guy, which allow us to ensure that we are capitalizing correctly and that we are splitting at the ' ,'. When a string is split using the Split() method, it creates an array of values based on where it was split. To access these values, we need to access them directly with $name[0] or $name[1]. We know that in our input CSV the first value was the surname and the second value was the given name. To switch the order, we simple create a new string with the second value first and the first value second.

Then, we create a $props ordered hashtable, after which, we set the ID field in our CSV with the HRId value, but we pad the value on the left-hand side with zeros if its value is less than 8. Moving to the DEPARTMENT property, we access the current field in our input CSV, called "Department and Office Number," by accessing its property name (which is the name of the field in the CSV). The output CSV example told us that we need the first 4 letters, so we call substring() and ToUpper() methods of the string value.

Moving on to the COMPLETED_DATE property, we create a [DateTime] object and pass in the value in the existing CSV Value ($d.'Sample Test Ended/Completed on Date') and use the ToString() method. Within the ToString() method, we pass the format we want the output CSV to have and set that to the COMPLETED_DATE property (key).

Once we have our new ordered list, we create a temporary PSCustomObject and save this to our $ReturnObject. Once we have exited the foreach loop, we simply export our $ReturnObject using the Export-CSV cmdlet:

Here is the full function for our sample problem:

Win the monthly 4sysops member prize for IT pros

Share
1+

Related Posts

0 Comments

Leave a reply

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

*

CONTACT US

Please ask IT administration questions in the forum. Any other messages are welcome.

Sending
© 4sysops 2006 - 2017

Log in with your credentials

or    

Forgot your details?

Create Account