PowerShell Tips: Working with CSV Files

PowerShell and Your Data Made Easy

PowerShell-logoThere 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:

Basic CSV FileIt 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”

}

Import CSV File

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.

VN:F [1.9.22_1171]
Rating: 8.5/10 (8 votes cast)

About Fpweb.net Crew

Our business is centered on bringing enterprise-class strategy, support, and security to your hosted or managed platforms no matter where you choose to deploy your environment. We specialize in providing managed services, cyber security, and expert, USA-based, 24/7 Absolute Support® on-premises, or in any cloud.
This entry was posted in PowerShell and tagged , , , , , , , , , . Bookmark the permalink.

2 Responses to PowerShell Tips: Working with CSV Files

  1. Shakeel Shahid says:

    Dear Sir,

    I am working on a project and i am facing some issues in it and if you can help me then i will be thankful to you.

    Actually my issue is i am importing users in Active Directory from CSV file but after this i need to replace values of some custom attributes of all AD imported users and the Values needs to be imported from CSV file automatically. Please send me your contact details so that i can further discuss with you on this issue.

    Regards,
    Shakeel Shahid.

  2. Erik says:

    Awsome,
    I had trouble using data in my loop now it works :)
    Works:
    $csv = Import-Csv -path $csvlocation1
    ForEach ($item in $csv ){
    $name = $item.Name
    $originalOU = $item.ParentContainerDN
    $pwdlastset = $item.pwdLastSet

    Set-QADComputer -Identity $item.name -Description “moved on $date from $originalOU because pwdlastset $pwdLastset”
    }

    Doenst work:
    $csv = Import-Csv -path $csvlocation1
    ForEach ($item in $csv ){
    Set-QADComputer -Identity $item.name -Description “moved on $date from $item.dn because pwdlastset $_.pwdLastset”
    }

    Erik

Leave a Reply

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

Let's make sure you're human first: *