When working with multi-dimensional arrays like imported CSV data, sometimes you want to change one or more values in one of the rows.
For example, you have imported user account data from a CSV file that uses an AD account’s samAccountName as a unique value for each row. Included in this CSV data is the employment status of each user account, and you want to be able to change that value when the employment status changes.
Here’s an example of how this can be done with the following CSV data:
FirstName,LastName,samAccountName,Email,Status Jill,Jones,jjones,jjones@contoso.com,Active Steve,Smith,ssmith,ssmith@contoso.com,Active Jeff,Johnson,jjohnson,jjohnson@contoso.com,Suspended John,Anderson,janderson,janderson@contoso.com,Retired
Import the CSV data into a variable:
PS C:\> $Users = Import-CSV "C:\data\users.csv"
View the data in the variable as a table:
PS C:\> $Users | FT FirstName LastName samAccountName Email Status --------- -------- -------------- ----- ------ Jill Jones jjones jjones@contoso.com Active Steve Smith ssmith ssmith@contoso.com Active Jeff Johnson jjohnson jjohnson@contoso.com Suspended John Anderson janderson janderson@contoso.com Retired
View just the First & Last Names and samAccountName:
PS C:\> $Users | Select FirstName, LastName, samAccountName FirstName LastName samAccountName --------- -------- -------------- Jill Jones jjones Steve Smith ssmith Jeff Johnson jjohnson John Anderson janderson
View the value of each row in the “samAccountName” column:
PS C:\> $Users.samAccountName jjones ssmith jjohnson janderson
Getting the Index Number of a Row
After importing the CSV data into a variable, you can retrieve the index number for a single row of data. Here’s a template for doing this:
[array]::IndexOf( $VariableName.ColumnNameToSearch , UniqueValueToMatchInTheColumn )
The first part inside the parenthesis defines which column in the array to search. The second part defines the value to search for.
If you wanted to get the index number for the Jeff Johnson row, you would search the “samAccountName” column using a value of “jjohnson”, and since the index numbers start at 0 (zero), the index number for the Jeff Johnson row will equal 2.
PS C:\> [array]::IndexOf($Users.samAccountName,"jjohnson") 2 PS C:\> $Users[2] | FT FirstName LastName samAccountName Email Status --------- -------- -------------- ----- ------ Jeff Johnson jjohnson jjohnson@contoso.com Suspended
Make Changes to a Row’s Data
Now that we know how to get the index number, we can add the index number to its own variable, and use it to modify any of the data in that row including changing the value of Status from “Suspended” to “Active”.
PS C:\> $RowIndex = [array]::IndexOf($Users.samAccountName,"jjohnson") PS C:\> $Users[$RowIndex] | FT FirstName LastName samAccountName Email Status --------- -------- -------------- ----- ------ Jeff Johnson jjohnson jjohnson@contoso.com Suspended PS C:\> $Users[$RowIndex].Status Suspended PS C:\> $Users[$RowIndex].Status = "Active" PS C:\> $Users[$RowIndex] | FT FirstName LastName samAccountName Email Status --------- -------- -------------- ----- ------ Jeff Johnson jjohnson jjohnson@contoso.com Active
Now, let’s change Jill’s last name from “Jones” to “Brown”, and then change the samAccountName and email address to match up with the name change.
PS C:\> $RowIndex = [array]::IndexOf($Users.samAccountName,"jjones") PS C:\> $Users[$RowIndex].LastName = "Brown" PS C:\> $Users[$RowIndex].samAccountName = "jbrown" PS C:\> $Users[$RowIndex].Email = "jbrown@contoso.com" PS C:\> $Users[$RowIndex] | FT FirstName LastName samAccountName Email Status --------- -------- -------------- ----- ------ Jill Brown jbrown jbrown@contoso.com Active
Once we are done, we can save our changes to the CSV file:
PS C:\> $Users | Sort LastName | Select FirstName,LastName,samAccountName,Email,Status | Export-Csv "C:\data\users.csv" -nti
Final Thoughts
Keep in mind that when a match is not found, the command will return -1 (negative one). Let’s give that a try:
PS C:\> [array]::IndexOf($Users.samAccountName,"jdoe") -1
Therefore, you can use an “If” statement to make sure a match is made before trying to change any values in a row.
Also, if there are duplicate values in your search of the column, the command will stop at the first match. So, make sure the values in the column are unique.