Greetings,

I have no experience in working with regex, so I need a hand to accomplish the following.

I have a file with the following information:

"name","whencreated","distinguishedname"
"Rune Olsson","2012-07-24 16:00:04","CN=Rune Olsson,OU=Bork,OU=viking,DC=viking,DC=local"
"Arne Petersson","2006-12-27 15:09:08","CN=Arne Petersson,OU=Nangijala,OU=viking,DC=viking,DC=local"
"Tengil Rosendal","2007-02-08 08:29:39","CN=Tengil Rosendal,OU=Birka,OU=viking,DC=viking,DC=local"

What I want is to "rip out" the OU were the user is located, so this is how I want to be like:

"Rune Olsson","2012-07-24 16:00:04","Bork"
"Arne Petersson","2006-12-27 15:09:08","Nangijala"
"Tengil Rosendal","2007-02-08 08:29:39","Birka"

I would really apreciate an regex (or another solution) on how to get it like that.

Thanks in advance!

I wouldn't do this entirely with regex. You have a CSV file, so start with the Import-Csv cmdlet and limit your string parsing to just manipulating what's in the distinguishedName field. For example:

$inputFile = '.\test.csv'
$outputFile = '.\output.csv'

Import-Csv -Path $inputFile |
ForEach-Object {
    $OU = ''

    if ($_.DistinguishedName -match 'OU=([^,]+)')
    {
        $OU = $matches[1]
    }

    New-Object psobject -Property @{
        Name = $_.Name
        WhenCreated = $_.WhenCreated
        OU = $OU
    }
} |
Export-Csv -Path $outputFile -NoTypeInformation

I used regex only to strip out the first instance of "OU=<Something>" from the distinguishedName string; the rest of the code just works based on Import-Csv / Export-Csv and objects.

• Marked as answer by smekfis 21 hours 47 minutes ago

I wouldn't do this entirely with regex. You have a CSV file, so start with the Import-Csv cmdlet and limit your string parsing to just manipulating what's in the distinguishedName field. For example:

$inputFile = '.\test.csv'
$outputFile = '.\output.csv'

Import-Csv -Path $inputFile |
ForEach-Object {
    $OU = ''

    if ($_.DistinguishedName -match 'OU=([^,]+)')
    {
        $OU = $matches[1]
    }

    New-Object psobject -Property @{
        Name = $_.Name
        WhenCreated = $_.WhenCreated
        OU = $OU
    }
} |
Export-Csv -Path $outputFile -NoTypeInformation

I used regex only to strip out the first instance of "OU=<Something>" from the distinguishedName string; the rest of the code just works based on Import-Csv / Export-Csv and objects.

Works brilliant! Except that the OU-column is first in the CSV,  I would like it to be last. I tried to sort this but I can't manage it.. Any tips?

EDIT: This did the trick: Select-Object Name, WhenCreated, OU

Thanks once again David!

• Edited by smekfis 21 hours 47 minutes ago
• Marked as answer by smekfis 21 hours 47 minutes ago

Hi Smekfis,

a bit bulky, but this method allows you a bit more precise control over your return objects. First of all, at the beginning you create your own Object Class like this ...

$source = @"
using System;

namespace MyNamespace
{
	public class UserInfo
	{
		public string Name;
		public string WhenCreated;
		public string OU;
		
		public UserInfo()
		{
			
		}
		
		public UserInfo (string Name, string WhenCreated, string OU)
		{
			this.Name = Name;
			this.WhenCreated = WhenCreated;
			this.OU = OU;
		}
		
		public override string ToString()
		{
			string temp = Name + " " + WhenCreated + " : " + OU;
			return temp;
		}
	}
}
"@
Add-Type $source

later in the script, you replace some components:

# Old block
New-Object psobject -Property @{
	Name = $_.Name
	WhenCreated = $_.WhenCreated
	OU = $OU
}

# New Block
New-Object MyNamespace.UserInfo($_.Name, $_.WhenCreated, $OU)

and that ought to have the OU at the last position *g*

Cheers,
Fred

Ps.: Yes, there are easier ways to do this, was just a bit in the mood to do some C# ^^

It mainly depends on whether you need this code to run on PowerShell 2.0 or not.  In PowerShell 3.0, you can use this syntax to create a custom object where the properties are in the order you defined:

# Instead of this:

    New-Object psobject -Property @{
        Name = $_.Name
        WhenCreated = $_.WhenCreated
        OU = $OU
    }

# You can do this:

    [pscustomobject] @{
        Name = $_.Name
        WhenCreated = $_.WhenCreated
        OU = $OU
    }

When PowerShell 2.0 support is needed, you have a few options.  As FWN demonstrated, you can define the type in C#.  You can also use Select-Object (which you found), or add the properties to an empty object one at a time with Add-Member, or set up an OrderedDictionary object instead of using a Hashtable literal (to be passed to New-Object's Property parameter).  Just comes down to personal preference; they all basically get you to the same place.

Thanks both of you for the information! I will learn it and try to remember it. 

 they all basically get you to the same place.

That's certainly true :)

While I happily admit that the C#-way is probably the most clunky, unwieldy one of the lot, I personally favor it, as it allows me to define the ToString() method (and thus how the object is displayed when you pack it into a field of another object. Hashtable-Based objects don't look all that great in that situation).

For everyday usage I guess the Select-Object way is the fairest combination of ease-of-use and compatibility.

Cheers and thanks for that list, hadn't known the first option available in V3+,
Fred