Sunday 16 May 2010

[Tip] Quick and Dirty Repetitive Command Line Tasks with Excel

At work we had to create folders for all our users. We didn't want to create each folder manually so we went through the following set to accomplish the task (It took about 10 minutes from start to finish):

1. Get a list of users from Active Directory.
We have a SQL Server Database with a linked table to Active Directory so we queried that to get a list of usernames.

2. Copy the list of users to a blank excel document into Column B

3. Type "mkdir" in to Column A.

4. Fill down Column A to the number of username rows.

5. Save the excel file as type "Text file (tab delimited)" to your desktop

6. Close excel and rename the created file so that it has the extension ".bat" at the end.

7. Move the file to the folder where you want to create the username folders.

8. Double click on the file to run it.

9. With in a few seconds you should have a list of folders called the names of your users.

This kind of procedure has a great many uses. Please let me know if you have used a technique like this and how you used it.

This procedure should work in a similar way on Linux / Macintosh and Windows alike.

1 comment:

Anonymous said...

I also sometime produce code like
a1=dir1
a2=dir2

then produce 1 code to process all the dirs in one loop.

Didier