HOW TO MAKE THE .CSV FILE

HOW TO MAKE A .CSV FILE OF AD USERS

This page will discuss what a .CSV file is, arranging the data in an Excel spreadsheet (or Excel .CSV file) to make a clear presentation of users, concatenating data to create the correct user data format, one way to make randomized passwords in Excel, and finally how to make the .CSV file from an Excel spreadsheet.

What is a .CSV file?  Simply a .CSV file is a comma separated values file.  This is a text file in which each line contains all the user data whose parameter values are separated by commas.  A .CSV file looks like this:

In general, the first line of .CSV files may or may not contain a “header row.”  This is a row that defines what each parameter is if you were to align the data into columns.  For the process of bulk loading users into active directory, the .CSV file will have a “header row” that will define the parameter values.  This is the first row that Excel will display as a .CSV file as interpreted by Excel.  One can see part of how Excel interprets the .CSV file above here:

There is little difference between the presentation of an Excel spreadsheet of .XLSX format (the XML backed file format of an Excel spreadsheet) and a .CSV file being presented by Excel.  The difference is that there is formatting that can be lost when saving a file as a .CSV file.  The steps that follow are the same when working in either an .XLSX file in Excel or a .CSV file in Excel.  

The following image shows users in a “raw data” .CSV file that will be formatted to make a .CSV file suitable for loading into AD via a PowerShell script.  Parameter names in the header row do not have to be in the order presented nor are they required to be named the way they are.  However, they must match what the PowerShell script will be looking for.  As such, for this project, I chose the formatting shown.  The next image shows the “raw data” started with.

The raw data .CSV file can be downloaded here:

https://cmit220.ronniekupfer.com/wp/wp-content/uploads/2021/05/RawUsers2.csv

The following two images show how the .CSV file user data has been ultimately formatted.  The top image is the left side of the .CSV file and the bottom image is the right side of the .CSV file.  For this project only one phone number column was used and the county column was deleted.  However, Country, JobTitle, and Department were added.  An OU column has also been added. 

Format the ZipCode

After organizing the columns and setting the header names as shown, the format of the ZipCode column was changed to show the leading zero(s).  This is done by highlighting the column with the zipcode data, clicking on the dialog launcher next to the number format ribbon group, clicking on the Number tab of the Format Cells box, selecting Custom in the Category box, and typing five zeroes in the Type: field.  This can be seen in the image below.  Here is a tip for selecting all the data down a column starting with the cell you’ve highlighted.  After selecting your starting cell, press CTRL + SHIFT + DOWN ARROW.

Country Code

The country column was populated with the United States.  However, because AD looks for a country code, you must use fill in the column with the correct country code for the country to populate in AD.  The correct country code for the United States is US.

Random Passwords

To generate a random password in Excel, the RAND (random) function can be used.  RAND simply returns a random number between 0 and 1.  Then multiply that number by 123456789 to generate a random number that exceeds minimum password length requirements.  Additionally, to meet minimum password complexity concatenate the random number with !Pp.  This applies the capitol letter requirement, the lowercase letter requirement, and the special character requirement.  Note that because the random number is a decimal, if you leave the decimal in the password, you will meet the special character requirement.  As shown in the picture below, in a column to the right of all the user data, add to the first row of user data !Pp.  To the right of that type in 123456789. To the right of that cell type =P2&(Q2*RAND()) to generate the random password.  Double click the fill handle of these three cells to fill in all the rows which have users.  The formula and the fill handle have been circled in the image below:

A couple things to note.  The & symbol performs concatenation of two cells.  In this case it tells cell R2 to combine the data in cell P2 with the product of cell Q2 and a random number (between 0 and 1).  Also, the cell letter will vary depending on where you choose to do your work.  Once the random passwords are generated they must be copied to the password column.  The way that they are copied to the column is important.  Select all the passwords (CTRL + SHIFT + DOWN ARROW), copy them, and then in the password column select PASTE > VALUES.  If PASTE > VALUES is not selected, when you delete the data from in the previously generated data, your passwords will be lost.  Finally delete the three columns that were just generated.

Generate UserNames

The UserName convention in AD is to use the first letter of the first name concatenated with the last name.  To do this in Excel, take advantage of the LEFT formula and concatenation.  The LEFT formula takes the number of characters you specify from the left side of the data in the cell you specify.  Again, in a cell to the right of all the user data, starting with the first row of user data type the following formula: =LEFT(A2,1)&B2.  This formula takes the first letter of the first name (cell A2) and concatenates it with the last name (cell B2).  Double click the fill handle to populate each row of user data.  Then copy and PASTE > VALUES all the usernames into the UserName column.  Then delete the column on the right that generated the usernames.  The image below shows the username generation with the formula and fill handles circled.

Populate The OU Column

Obtain the correct OU from your AD domain.  Right click on the OU in the management console > select properties > click on the Attribute Editor tab > scroll down to distinguished name. 

Select the distinguishedName by clicking on it, then click the view button.  You can then copy the OU in the box that opens after clicking view.  This can be seen in the following image.

Use this copied OU value into your .CSV file.  Double click the fill handle to auto populate all the rows of user data.

Creating Email Addresses

The final step in formatting the data to make a useable .CSV file is to generate the correct e-mail addresses.  The e-mail addresses in the raw data are not the ones we would use.  The e-mail should be in the ronniekupfer.org domain.  Again, in a cell to the right of all the user data, starting with the first row of user data type @ and the domain name, in this case @ronniekupfer.org.  Change the formatting of the column to Text, otherwise Excel tries to make it a link.  To the right of this cell, concatenate the UserName with the cell with @ronniekupfer.org.  The image below shows the formula is = C2&P2.  Double click the fill handle to populate each row of user data in that column with @ronniekupfer.org.  Then double click the fill handle with the formula to populate each row of user data with their respective e-mail addresses.  Copy and PASTE > VALUES in the Email column.  Then delete the two columns generated on the right. 

Save File

The last step in making this file a .CSV file whether it already is one or is an Excel spreadsheet is to simply Save as type: CSV (Comma delimited) (*.csv) file as shown below.

The steps above can be watched in the two videos below.