微軟帝國 : [轉貼]CSV, yeah you know me! – PowerShell and the Import-Csv CmdLet

Managing data inside a CSV file is surprisingly simple thanks to the Import-CSV CmdLet. I’m often presented with data from different sources in a variety of formats, and by and large the common format used for data extracts is CSV. Regardless of the system that generates the content, we can all render and consume data using this simple and effective format.

You will find the Microsoft TechNet documentation here:
http://technet.microsoft.com/en-us/library/dd347665.aspx

The file that we will use as our example is one that contains a username, email address, street address, postal code (zip code for my US friends) and a phone number. This is what the file would look like for our sample which will be named UserList.CSV:

username,full name,email,address,postalcode,phone
ewright,Eric Wright,eric@somedomain.com,123 Any Street,L4C 1N8,555-1212
jvoigt,Jens Voigt,jens@somedomain.com,456 Any Street,L4C 1N8,555-3456
ppocklington,Peter Pocklington,peter@anotherdomain.com,890 Back Street,M2H 4Y1,555-9876

Note that there is a header row which is one of the most important aspects for our script. If the file that you want to use does not have a header row, we can add one. I’ll go into that more deeply in an upcoming article.

What we do first is to bring the file into a variable as an array. This is done by creating a variable which we will call $userobjects and assigning it a value using the Import-CSV CmdLet. Let’s assume that you have the import file in a folder X:ImportData

$userobjects = Import-CSV x:ImportDataUserList.CSV

No really, it’s just that simple. Now you have an array variable which you can use to access the data in a number of ways. Let’s look at a couple of methods to pick out data:

Display all instances in the array:

$userobjects

Display the first element in the array:

$userobjects[0]

Ok, hold on a second. Why did we use 0 (zero) to pull the first record? This is a done because when an array is populated, the first element is in position 0. For a 2 dimensional array, the first element is 0,0 rather than 1,1 which can be a source of confusion.

The purpose of the article was to talk about headers so now you will see where that comes into play. We have drawn out the different elements inside the array, so now we can use the headers to be able to gather the content of each element in a meaningful way. Let’s say that we want to see the email address of the first element.

$userobjects[0].email

It’s so dangerously simple really. Because the array was imported with the Import-CSV, the header row was assigned to columns just as we would see it inside a spreadsheet program. Just think of it as element[instance].property which means you can select any assigned property (column header) from any instance of the element.

We can also do things such as count the elements which can be a handy piece of information to have:

$userobjects.count

In part 2 we will expand our script to loop through the elements and show you different ways that we can manage our data from the CSV file and begin to perform other operations using the contents.

In
part 1 of this series we touched on the basics of using the Import-Csv CmdLet by reading a file into an array variable and extracting elements and data from our input data.

To enhance this process we can now take the data in memory and using a ForEach CmdLet to parse the data and do a multitude of actions with it.

One thing that you may have noticed in the first part of the script is that we named the variable using the pluralized $userobjects rather than the singular $userobject. Why is that you ask? Great question!

To simplify the identification of objects in our array we want to refer to them in a logical way of a singular userobject in an array, or list, of userobjects. While PowerShell doesnt formally use pluralization, I just find it easier to follow and hopefully it’s a better practice of programming.

First we will add the very basic ForEach to output the Usernames to the screen. Using our pluralization format this is what we do:

ForEach ( $userobject in $userobjects ) {
Write-Host Username is $userobject.Username
}

Remember that everything within the curly braces will be performed against each object in the array sequentially and then move on to the next until it reaches the end of the array and moves to the next command outside of the curly braces.

In other words the ForEach loops through the data and then move onwards, so if we want to display all of the usernames, and emails followed by a count of the total records it would look like this:

ForEach ( $userobject in $userobjects ) {
Write-Host Username is $userobject.Username had email $userobject.email
}
Write-Host Total Records = $userobjects.count

Because we are capturing elements and the contained data, it is better to assign these as variables within our loop so that we can address them and also perform other tasks such as concatenation, splitting, calculation and any of a variety of other actions. I prefer to assign a meaningful name in a format like $FileVariableName or $InputVariableName. Here is what I would do in this particular case:

ForEach ( $userobject in $userobjects ) {
#Assign the content to variables
$FileUsername = $userobject.username
$FileFullName = $userobject.”full name”
$FileEmail = $userobject.email
$FileAddress = $userobject.address
$FilePostalCode = $userobject.postalcode
$FilePhoneNumber = $userobject.phone

# Output the content to the screen
Write-Host $FileFullName has a username of $FileUsername with email $FileEmail
}
Write-Host Total Records = $userobjects.count

Notice that because one of our columns had a space in the name (Full Name) we had to enclose the column header name in double quotes as $userobject.”full name” or else it would throw an error.

At this point you can see the capabilities of using your ForEach loop process. Recall that you can also pass along multiple commands to the loop. As long as you have the correct modules loaded, you can now use this process to run Microsoft Exchange CmdLets such as Get-Contact to check our Exchange directory if these are existing contacts:

ForEach ( $userobject in $userobjects ) {
#Assign the content to variables
$FileUsername = $userobject.username
$FileFullName = $userobject.”full name”
$FileEmail = $userobject.email
$FileAddress = $userobject.address
$FilePostalCode = $userobject.postalcode
$FilePhoneNumber = $userobject.phone

# Output the content to the screen
Write-Host $FileFullName has a username of $FileUsername with email $FileEmail

if ( Get-Contact -Identity $FileEmail ) {
Write-Host $FileFullName exists in the Exchange directory
}
}
Write-Host Total Records = $userobjects.count

Now we’ve expanded our script to make it more reliable, and depending on the complexity of the data we could see many other operations that we require to make use of the input data. The next article in the series will have us attack some additional tasks including:

• Adding a Header row where one does not exist


• Expand our Get-Contact query to perform a New-MailContact command if the record does not exist

I hope that you find this helpful. The goal is to lay out the fundamentals that I’ve used for some of the more complex scripts that have been created for tasks such as importing and managing Exchange Contacts, managing Active Directory user account information and many such tasks.

This was a long overdue post, so thanks for sticking with me while I finally got back on track with our CSV, yeah you know me series (Here are Part 1 and
Part 2). As I’d mentioned in the closing of Part 2, we want to be able to deal with files that have no header in the CSV which we haven’t encountered yet. Also, we want to expand the use of our CSV content by running the Get-Contact query and creating a contact in the event that one doesn’t exist already.

The Head(er)less Horseman

In our previous posts, we have been using a file which contained some field data in the file and a header row containing the field names which made our job nice and easy with parsing out the data.

Let’s take a look at the same source file, but this time we have no header row and our task will be to insert a header. This isn’t necessarily a common task, but we may find that we receive some batch data from a vendor and they have either no header, or the field names would have been useless to us, so we opt to create our own header row for ease of use.

Here is our starter file named NoHeader.CSV

We know that the field names that we wish to apply are as follows: username,full name,email,address,postalcode,phone so this will become part of our routine before parsing the data.

I’m a big fan of leaving source data intact when manipulation of the content occurs, so the steps I’m going to use include working with a new file which is populated from the clean source file and my custom inserted header row. Getting back to basics, this was the command to import the data to begin with.

$userobjects = Import-CSV x:ImportDataUserList.CSV

Now that we want to start with a clean file though, we change the tactic to reading the content using the Get-Content CmdLet and then piping the output into a new file. But first, let’s setup our new target file.

I’m also going to start referring to our script as CSVProcess.PS1 rather than just pasting all of my content in the command line each time.

When using temporary files we need to do some simple steps:

1. Define the file name and location (best done in a variable)


2. Empty out the file if it exists because we are creating the content on the fly
3. Send the header row data to the new empty file
4. Append the content from our header-less file
5. Revel in the joy of having done a cool task

So our new filename will be called ImportData.CSV and I will assign it a variable $ImportFile at the start of our script. While we are at it, we will also define the NoHeader.CSV file as $ImportFileNoHeader at the same time:

$ImportFile = “x:ImportDataImportData.CSV”

$ImportFileNoHeader = “x:ImportDataNoHeader.CSV”

Next we will use the Clear-Content CmdLet to empty out the file. The first time this line is executed it will generate an error because the file doesn’t exist, but that’s not a big problem because it will be created during the first script run:

Clear-Content $ImportFile

This is the error because if the missing file. We could handle the error and be fancy, but because this is a one-time error I would rather skip on lots of coding. I’ve used the Test-Path CmdLet for checking the file location, but it’s overkill for the simple process we are running here.

So now we have our empty file (ImportData.CSV) and we will use the Add-Content CmdLet to append our header row:

Add-Content $ImportFile “username,full name,email,address,postalcode,phone”

The file now contains our header row. Yay!

Now we read our header-less file (NoHeader.CSV) using the Get-Content CmdLet and pipe it into an Add-Content CmdLet to append to our ImportData.CSV file:

Get-Content $ImportFileNoHeader | Add-Content $ImportFile

Yup, it’s just that easy. And here is our new file to be used for the contact management section of the script:

So we now have our full process defined to add the header data, import the CSV data into the new import file, import the data into an array and loop through to use the Get-Contact process to check if they exist.

Adding Contacts if they Don’t Exist

Adding Exchange contacts is easily done (sort of) by using the New-MailContact CmdLet but there is a catch: Only some field can be added using the New-MailContact CmdLet and the rest have to be set after creation using the Set-MailContact CmdLet. Confused? Don’t worry, it takes a little getting used to, but this becomes second nature once you build the process once.

I’m not going to dive into the features of the New-MailContact and Set-MailContact section here, so I’m doing the old Martha Stewart trick by putting the raw turkey in the top oven, and showing you the cooked one a minute later from the bottom oven. Here is the cooked version of the contact creation section of the script:

So we have now reached the end of the quick 3 part series on using the Import-CSV CmdLet and how CSV data is easy to manage and manipulate for using in other processes.

If you want to really dive deep, you can go over to my
Importing, and Updating Exchange 2010 Contacts from CSV post which is probably the most popular overall post on the site. Hope that this has helped and if you have any questions, drop me a a comment below!

Here is the final copy of the whole script so that you can see it from end to end. Happy Scripting!

$ImportFile = “x:ImportDataImportData.CSV”
$ImportFileNoHeader = “x:ImportDataNoHeader.CSV”

Clear-Content $ImportFile
Add-Content $ImportFile “username,full name,email,address,postalcode,phone”
Get-Content $ImportFileNoHeader | Add-Content $ImportFile
$userobjects = Import-CSV $ImportFile

ForEach ( $userobject in $userobjects ) {
#Assign the content to variables
$FileUsername = $userobject.username
$FileFullName = $userobject.”full name”
$FileEmail = $userobject.email
$FileAddress = $userobject.address
$FilePostalCode = $userobject.postalcode
$FilePhoneNumber = $userobject.phone

# Output the content to the screen
Write-Host $FileFullName has a username of $FileUsername with email $FileEmail

if ( Get-Contact -Identity $FileEmail ) {
Write-Host $FileFullName exists in the Exchange directory
}

# First we create the contact with the required properties
New-MailContact -Name “$FileUsername” `
-OrganizationalUnit “OU=YourContactsOU,DC=yourdomain,DC=com” `
-ExternalEmailAddress $FileEmail `
-Alias $FileUsername `
-DisplayName $FileFullName `
-PrimarySmtpAddress $FileEmail

# Now we set the additional properties that aren’t accessible by the New-MailContact cmdlet
Set-Contact -Identity $FileEmail `
-DisplayName $FileFullName `
-SimpleDisplayName $FileFullName `
-Phone $FilePhoneNumber `
-PostalCode $FilePostalCode `
-StreetAddress $FileAddress `
-WindowsEmailAddress $sourceEmail
}
Write-Host Total Records = $userobjects.count