Read a CSV File and Build Distinguished Names on the Fly by Using PowerShell
Summary: Learn two ways to read a CSV file with Windows PowerShell and build distinguished names on the fly.
Hey, Scripting Guy! I am working on my first big project with Windows PowerShell, and I have been unable to locate a good answer on the Internet. I am having trouble figuring out how to connect a bunch of different array items. Here is a quick look at my code:
$data = “c:\fso\myusers.csv”
$list = import-csv $data | select class, dept, campus –unique
After I import the CSV file and chose the properties I need, I attempt to create a distinguished name attribute from the data. Here is what that code looks like.
$dn = “OU=$list[0].class,OU=$list[0].dept,OU=$list[0].campus,DC=contoso,DC=com”
Unfortunately, this command does not work. I ended up having to put each piece of the array into its own variable before I can get it to go together properly. Here is what I came up with.
$part1 = $list[0].class
$part2 = $list[0].dept
$part3 = $list[0].campus
$dn = “OU=$part1,OU=$part2,OU=$part3,DC=contoso,DC=com”
I am not certain why my first attempt failed, nor am I certain if my second attempt is the best approach. I am hoping that you can take what I have given you and come up with a better way of doing things. I love your funny way of writing—it makes me laugh most of the time (when I am not pulling my hair out)…LOL. Thank you for what you do.
—TJ
Hello TJ,
Microsoft Scripting Guy, Ed Wilson, is here. I am glad that you enjoy the blog posts, and I am sorry that you are having a bit of difficulty with the array.
Note: This is the fourth post in a series of blog posts that are devoted to discussing working with arrays in Windows PowerShell.
- In the first post, Learn Simple Ways to Handle Windows PowerShell Arrays, I discussed creating arrays, indexing into arrays, and two techniques for walking through an array.
- In the second post, Add, Modify, Verify, and Sort Your PowerShell Array, I discussed adding, modifying, or verifying values in a Windows PowerShell array, and two easy techniques for sorting the array.
- In yesterday’s post, Find the Index Number of a Value in a PowerShell Array, I talked about working with arrays to determine the index number of a given value. In addition, I covered working with one half of the array at a time.
First test reading a CSV file
TJ, the first thing I need to do is to create a CSV file that mimics your setup. I created a file named myusers.csv, and I placed it in my c:\fso folder. The CSV file contains Class, Dept, and Campus column headings. The CSV file is shown in the following image.
After I have the CSV file created, I use the Data variable to store the path, and I call the Import-CSV cmdlet to read the content of the file into another variable named List. I then display the content of the variable to ensure that the file parses correctly. These three commands are shown here.
$data = “C:\fso\myusers.csv”
$list = Import-Csv $data
$list
TJ, now it is time to see the problem you are experiencing. Here is the command you use.
“OU=$list[0].class,OU=$list[0].dept,OU=$list[0].campus,DC=contoso,DC=com”
The command looks like it should work. You index into the data that is stored in the List variable, and you use dotted notation to retrieve each portion you require. When I run the command, the string literals appear, but the values from the CSV do not populate. The commands that import the CSV file and verify the content of the file, in addition to the command that does not work properly and its associated output are shown in the following image.
The problem is that the data from the CSV file is not displaying in the output. But as shown here, the data does in fact exist because it displays when I use dotted notation to retrieve the data from the first element in the array.
PS C:\> $list[0].class
101
PS C:\> $list[0].dept
math
PS C:\> $list[0].campus
main
I need to force the evaluation of each value so I can display the data. To do this, I use a subexpression. A subexpression consists of a dollar sign and a pair of parentheses. Here is the revised command that will display the data properly.
“OU=$($list[0].class),OU=$($list[0].dept),OU=$($list[0].campus),DC=contoso,DC=com”
The command that creates the CSV file, uses dotted notation to retrieve column data, and incorporates a subexpression around each substitution group, and its associated output are shown in the following image.
Although the previous command works, the code is a bit complex and cluttered. It makes it difficult to see clearly what is actually going on. A better way to do this is to use parameter substitution. To do this, each parameter that requires a substitution appears in a pair of curly brackets with a number. The f operator appears on the right side of the substitution, and each replacement value appears on the right side in a comma-separated fashion. Here is the revised code.
“OU={0},OU={1},OU={2},DC=contoso,DC=com” -f $list[0].class,$list[0].dept,$list[0].campus
The code that creates the CSV file and uses parameter substitution to replace the tokens in the output is shown in the following image.
Now that we understand the fundamentals of using a parameter substitution technique, let’s see how to apply this to each item in the CSV file to create a collection of distinguished names. I can accomplish this in a single line of code. I decided to drop the $data variable, and I assign the path directly to the CSV file to the Import-CSV cmdlet. I pipe the results to the Foreach-Object cmdlet, and inside the associated script block, I do the parameter substitution.
The change to the original code is to use the $_ automatic variable, instead of indexing into the array. The $_ automatic variable refers to the current item on the pipeline, and it takes the place of the array index number in the code. (I could shorten the command by using the alias ipcsv for Import-CSV and by using % for the Foreach-Object cmdlet). Here is the revised line of code. (The following is a single-line command, but I broke the command at the pipe character due to display constraints).
import-csv c:\fso\myusers.csv |
foreach-object {“OU={0},OU={1},OU={2},DC=contoso,DC=com” -f $_.class,$_.dept,$_.campus}
The following image displays the one-line command to read a CSV file, and it uses parameter substitution to create the distinguished names.
TJ, that is all there is to reading a CSV file and retrieving items from the array to make a substitution. Array Week will continue tomorrow when I will talk about multidimensional arrays and adding to arrays.
I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.
Ed Wilson, Microsoft Scripting Guy
Light
Dark
0 comments