PowerShell and Your Data Made Easy
There comes a time in every Administrator’s life that requires performing some action, multiple times, with some form of data.
For example, maybe you have an exported list of user records from Active Directory that need to be imported into another system. Or maybe you have a list of SharePoint users that need to be migrated from one domain to another. Whatever the goal is, it’s not very practical or efficient to copy data from a list and manually perform an action with that data hundreds or thousands of times in a row.
While there are a lot of file formats and tools that can be used to utilize data (Excel workbooks, xml files, even text files, etc.), this blog will be about a format that PowerShell has built-in and a very easy-to-use tool: the CSV file. PowerShell makes it incredibly easy to loop through the data in a CSV to accomplish any task with that data. In fact, it’s almost too easy!
For our demonstration, I’ve created a basic CSV file with some dummy data:
It just has three columns and a couple rows of data (technically, there are three rows in the screenshot above, but I’ll talk about that part in a bit).
The PowerShell cmdlet we’ll utilize today is the import-csv cmdlet. (You can see the technical description and all of the parameters available for the cmdlet in this TechNet article.)
First, I’ll import the CSV file to a variable called “$users”. (It doesn’t really matter what variable name is used.)
$users = import-csv “C:\CSV_Files\Users.csv”
Now that we’ve imported the CSV, we need to do something with all that data. For starters, let’s just see how easy it is to iterate through the rows with a ForEach loop:
ForEach ($item in $users)
Let’s discuss the ForEach loop above. Each $item is basically just one row from the file. By default, import-csv will treat the first row in the CSV file as the property names. So when the loop iterates through the first time, it’ll start with the second row from the CSV.
This is what I meant earlier when I said that there were couple rows of data in that screenshot. That first row was really just used for the property names. According to my screenshots above, the file has three header values, “First Name” in the first column, “Last Name” in the second column, and “Username” in the third column. Now, let’s see an example of how we can manipulate this data:
ForEach ($item in $users)
# I like to assign each property value to a simpler named variable, but it’s not necessary
$firstName = $item.(“First Name”)
$lastName = $item.(“Last Name”)
$username = $item.Username
#This is where we will do something with the data
Write-Output “First Name: $firstName”
Write-Output “Last Name: $lastName”
Write-Output “Username: $username”
Of course just outputting the data is not very exciting, but the example above demonstrates how easy it is to iterate through the items, set the value of each item’s property to a variable and then use that variable to perform an action.
NOTE: The data type of each object in a CSV file will be a string when it’s imported so you may need to perform some type casting if you want any of the data to be a different data type after it’s been imported.