I needed to create and populate a security group in active directory so I could limit a group policy to only the relevant users. Those relevant users each had a folder under the server share with the folder name being their username. Given that there were several dozen users already in the directory, I was hoping to be able to get that data in the format I needed so I could do a simple copy/paste instead of typing them all in again. Here is the process I came up with, feel free to drop any further tips in the comments.
What I had: a directory on a server share full of folders named with each user’s username
What I needed: a semi-colon separated list of said usernames
Step 1: Map drive
My previously suggested registry trick to allow the command prompt to use UNC paths wasn’t working in Windows 7 (at least not without a restart). Instead, I mapped a network drive to the server share containing the user folders.
Step 2: Run dir from command prompt on mapped drive
I switched to Z: (CD Z:) and then wrote out the output from the dir command to a text file (dir > c:personaldrives.txt).
Step 3: Import the text into Excel
The text output included more data than what I needed. I deleted the information out of the top and bottom that didn’t fit the column format and saved/closed the file.
I now took the scrubbed data and opened up Excel. Switching to the Data tab, I selected to “Get External Data” from Text. I browsed to the personaldrives.txt file and chose to import it. On the screen that popped up, I chose Delimited.
Import into Excel as delimited and on the next screen check ‘Space’ as the delimiter and ensure the ‘Treat consecutive delimiters as one’ is checked. This should add lines to each column and you can finish the Import Wizard.
Step 4: Add the semicolon after each username
Copy column E data out of Excel and into Notepad++. Next, we’re going to do a Find and Replace. Hit Ctrl + F to bring up the dialog.
Choose the radio button near the bottom for ‘Search Mode’ to set it to Extended.
Then in the ‘Find what’ field, enter: r
The ‘Replace with’ field should have: ;r
Then hit the Replace All button once.
The r symbolizes the carriage return after each line. Your data is now properly formatted.
Step 5: Copy into your AD group
Copy and paste the semicolon-separated information into the group’s Members tab in Active Directory Users and Computers.
Mission accomplished.
Alternate Step 4: Use only Excel
If you don’t want to include Notepad++ in the process, you can complete it using only Excel. With the column to the right of your usernames, put a semicolon in the top field. Drag that box down to repeat the semicolon for each row where there are user names. Then, use another column to concatenate the two columns together into this third column.
Optimize step 2 and skip step 3: Use parameters to clean up the output
For the sake of illustration, I went the long way around with this example. In our case, however, we could have revised Step 2. Instead of using dir > C:personaldrives.txt, we could use dir /B > c:personaldrives.txt.
The /B parameter “Uses bare format (no heading information or summary).” It provides only the folder names on separate lines and nothing else to clean up. This means we could output text, open it in Notepad++, do a find and replace to add the semicolon and been done. In case you’re using other commands that don’t make it so easy, the full example should still be useful of different ways to massage and manipulate the data into a format you need.