1

I have to update AD with home users number, etc. I've written the script and imported the usernames and phone numbers into Excel

Script:

dsquery user -samid "username" "OU=Sys,OU=Prod Services,DC=win,DC=ite,DC=com" | dsmod user -mobile  "999" -hometel -u XXXXXX -p XXXXXXXXX

In Excel: A1 = Username B1 = Num

How do I get the above script working so it takes the usernames and numbers from Excel and puts them into a batch file?

2
  • 6
    Why are you not using PowerShell for this? There are excellent cmdlets ready to do the stuff you're looking for (Get-ADUser and Set-ADuser with CSV import/export)
    – pauska
    Commented Jan 7, 2014 at 14:19
  • 1
    Exactly what @pauska said. You should absolutely be using PowerShell for this and not the ds tools.
    – MDMarra
    Commented Jan 7, 2014 at 14:44

1 Answer 1

7

Okay, this is definitely in the quick and dirty school of IT Engineering but we've all done it and, actually, Excel is mega handy for this. I do agree with @Pauska that Powershell and CSV's are the way forwards, though.

Anyway, the quickest and easiest way to do this (For a one off job, anyway) is to organise your excel sheet as follows:

     Cell A        Cell B       Cell C
  +------------+------------+------------+
1 +  Username  +  Telephone +     Fax    +
  +------------+------------+------------+
2 +   User1    +123456789012+987643210987+

And so on. Then, in column D, on Row 2 create a formula that uses concatenation to build your individual command line, ending with something along these lines:

="dsquery user -samid '" & A2 & "' 'OU=Sys,OU=Prod Services,DC=win,DC=ite,DC=com' | dsmod user -mobile '" & B2 & "'"

You can then select that whole column, copy and paste it notepad and voila - a batch file which will do what you need.

5
  • Tempted to click the down arrow for being this dirty. It's beautiful in it's dirtiness though :) Commented Jan 7, 2014 at 14:44
  • I am useless at using excel, where do i enter in the formula- I highlight the first cell user1 and drag it over to cellC fax, and then down to the end of the file? so where do i enter in my query, I new to this please bear with me.
    – JJJJNR
    Commented Jan 7, 2014 at 15:11
  • 1
    @JJJJNR You don't click and drag anything, just put the formula in cell D2 (Based on my diagram). You'll need to tweak the formula more to make it work for you, though. Then, click the little square on the bottom right of D2, and drag down. The formula will autoupdate for each row. Beyond that, you really need to learn how to use Excel for the above to work.
    – Dan
    Commented Jan 7, 2014 at 15:17
  • my excel is broken as it wont autoupdate for each row, despite it been selected in the tools to update, thanks for your help.
    – JJJJNR
    Commented Jan 9, 2014 at 10:56
  • 1
    It works! just FYI this is the dsqury I went with in the end: dsquery user -samid username | dsmod user -mobile xxxxxxx do not put any spaces in the numbers when you have them in and also you only need one set of exclamation marks on the side of the excel cell, also do not put the number in comma's –
    – JJJJNR
    Commented Jan 24, 2014 at 10:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .