JSI Tip 7980. How can I export Active Directory user data to a delimited file?

Using DSQUERY, an Active Directory command-line tool, I have scripted UserCSV.bat to export user data to a semi-colon delimited file.

The syntax for using UserCSV.bat is:

usercsv CSVFileName Attribute1 \[Attribute2 ... AttributeN\]

where CSVFileName is the path and file name you want the data exported to, and each Attributei is an attribute name (see What attribute names can I use with the user filtered dsquery command?).

NOTE: If an attribute data value contains a semi-colon, it will be exported as a colon.

NOTE: Multi-line attributes, like streetAddress, and multi-valued attributes, like MemberOf, cause DSQUERY heading/data mis-alignment. If you must use one of these attributes, make it the last attribute specified.

NOTE: The case of the attribute name is NOT important, sAMAccountName, SAMACCOUNTNAME, and samaccountname work equally well.


To export the User Name, Distinguished Name, Display Name, Country Name, Postal Code, City, State code, Home Phone, and Street Address, use:

UserCSV "%UserProfile%\My Documents\Export.csv" sAMAccountName distinguishedName displayName co postalCode l st homePhone streetAddress

The Export.csv file might contain:

"SUPPORT_388945a0";"CN=SUPPORT_388945a0,CN=Users,DC=JSIINC,DC=COM";"CN=Microsoft Corporation,L=Redmond,S=Washington,C=US";;;;;;
"test";"CN=test,CN=Users,DC=JSIINC,DC=COM";"My Display Name";"United States";"30338";"Atlanta";"GA";"14561239876;"123 Smith St"
"USER_OU_TEST";"CN=OU TEST,OU=OU_TEST,DC=JSIINC,DC=COM";"OU TEST";"United States";"30004";"Alpharetta";"GA";"19876543210";"1 South Main St."
UserCSV.bat contains:
@echo off
if \{%2\}

\{\} @echo Syntax: UserCSV CSVFile Attribut1 \[Attribute2 ... AttributeN\]&exit /b setlocal set CSVFile=%1 if exist %CSVFile% del /q %CSVFile% if exist "%TEMP%\UserCSV.TM2" del /q "%TEMP%\UserCSV.TM2" set /a cnt=0 set attr=%2 set attr=%attr:"=% set line="%attr%" set dsquery=dsquery * domainroot -filter "(&(objectCategory=Person)(objectClass=User)(sAMAccountName=*))" -attr %attr% :AttrLoop shift if \{%2\}

\{\} goto Export set /a cnt=%cnt% + 1 set attr=%2 set attr=%attr:"=% set line=%line%;"%attr%" set dsquery=%dsquery% %attr% goto AttrLoop :Export @echo %line%>"%TEMP%\UserCSV.TM1" set /a max=%cnt% set dsquery=%dsquery% -Limit 0 set first=Y for /f "Tokens=1* Delims=:" %%t in ('%dsquery%^|Findstr /l /n /v /c:"*$"') do ( set line=%%u call :Export1 ) call :quiet>nul 2>&1 endlocal exit /b 0 :quiet sort "%TEMP%\UserCSV.TM2" /O "%TEMP%\UserCSV.TM3" copy "%TEMP%\UserCSV.TM1"+"%TEMP%\UserCSV.TM3" %CSVFile% if exist "%TEMP%\UserCSV.TM1" del /q "%TEMP%\UserCSV.TM1" if exist "%TEMP%\UserCSV.TM2" del /q "%TEMP%\UserCSV.TM2" if exist "%TEMP%\UserCSV.TM3" del /q "%TEMP%\UserCSV.TM3" goto :EOF :Export1 if "%line:~0,2%" NEQ " " goto :EOF set line=%line:~2,9999% if "%first%" EQU "N" goto Export2 set first=N set line=%line%##### set headtab=0000 set /a cnt=0 set /a seq=0 :head set /a seq=%seq% + 1 call set char=%%line:~%seq%^,1%% if "%char%" EQU " " goto head1 goto head :head1 set /a seq=%seq% + 1 call set char=%%line:~%seq%^,5%% if "%char%" EQU "#####" goto head9 call set char=%%line:~%seq%^,1%% if "%char%" EQU " " goto head1 set /a point=%cnt% * 8 call set pos=%%headtab:~%point%^,4%% set /a pos=10000%pos%%%10000 set /a len=%seq% - %pos% -1 set /a len=%len% + 10000 set /a nxt=%seq% + 10000 set headtab=%headtab%%len:~1,4%%nxt:~1,4% set /a cnt=%cnt% + 1 goto head :head9 set headtab=%headtab%2047######## goto :EOF :Export2 set /a cnt=0 set query= :Export3 set /a seq=%cnt% * 8 set /a cnt=%cnt% + 1 call set char=%%headtab:~%seq%^,8%% if "%char%" EQU "########" goto Export6 set /a pos=10000%char:~0,4%%%10000 set /a len=10000%char:~4,4%%%10000 call set char=%%line:~%pos%^,%len%%% set char="%char%" set char=%char: =% :Export4 set work=%char: "="% if %work% NEQ %char% set char=%work%&goto Export4 set char=%char: "="% if %char% EQU "" goto Export5 if %char% EQU " " goto Export5 set char=%char:;=:% set query=%query%;%char% goto Export3 :Export5 set query=%query%; goto Export3 :Export6 set work=%query:~1,9999% @echo %work%>>"%TEMP%\UserCSV.TM2"

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.