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:

<font size="-2">"sAMAccountName";"distinguishedName";"displayName";"co";"postalCode";"l";"st";"homePhone";"streetAddress"
"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."</font>
UserCSV.bat contains:
@echo off
if \{%2\}<h1><a name="_echo_Syntax_UserCSV_CSVFile_Attribut1_Attribute2_AttributeN_amp_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_amp_objectCategory_Person_objectClass_User_sAMAccountName_attr_attr_AttrLoop_shift_if_2_">\{\} @echo Syntax: UserCSV CSVFile Attribut1 \[Attribute2 ... AttributeN\]&exit /b
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%
if \{%2\}</a></h1>\{\} goto Export
set /a cnt=%cnt% + 1
set attr=%2
set attr=%attr:"=%
set line=%line%;"%attr%"
set dsquery=%dsquery% %attr%
goto AttrLoop
@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:"<a href="/article/tips/jsi-tip-7980-how-can-i-export-active-directory-user-data-to-a-delimited-file--80753?topic=*%24">*$</a>"') do (
 set line=%%u
 call :Export1
call :quiet>nul 2>&1
exit /b 0
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
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
set /a seq=%seq% + 1
call set char=%%line:~%seq%^,1%%
if "%char%" EQU " " goto head1
goto head
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
set headtab=%headtab%2047########
goto :EOF
set /a cnt=0
set query=
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:   =%
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
set query=%query%;
goto Export3
set work=%query:~1,9999%
@echo %work%>>"%TEMP%\UserCSV.TM2"