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.

Example:

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 DocumentsExport.csv" sAMAccountName distinguishedName displayName co postalCode l st homePhone streetAddress

The Export.csv file might contain:

"sAMAccountName";"distinguishedName";"displayName";"co";"postalCode";"l";"st";"homePhone";"streetAddress""Administrator";"CN=Administrator,CN=Users,DC=JSIINC,DC=COM";;;;;;;"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 offif {%2}

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

{} goto Exportset /a cnt=%cnt% + 1set attr=%2set 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 0set first=Yfor /f "Tokens=1* Delims=:" %%t in ('%dsquery%^|Findstr /l /n /v /c:"*$"') do ( set line=%%u call :Export1 )call :quiet>nul 2>&1endlocalexit /b 0:quietsort "%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:Export1if "%line:~0,2%" NEQ "  " goto :EOFset line=%line:~2,9999%if "%first%" EQU "N" goto Export2set first=Nset line=%line%#####set headtab=0000set /a cnt=0set /a seq=0:headset /a seq=%seq% + 1call set char=%%line:~%seq%^,1%%if "%char%" EQU " " goto head1goto head:head1set /a seq=%seq% + 1call set char=%%line:~%seq%^,5%%if "%char%" EQU "#####" goto head9call set char=%%line:~%seq%^,1%%if "%char%" EQU " " goto head1set /a point=%cnt% * 8call set pos=%%headtab:~%point%^,4%%set /a pos=10000%pos%%%10000set /a len=%seq% - %pos% -1set /a len=%len% + 10000set /a nxt=%seq% + 10000set headtab=%headtab%%len:~1,4%%nxt:~1,4%set /a cnt=%cnt% + 1goto head:head9set headtab=%headtab%2047########goto :EOF:Export2set /a cnt=0set query=:Export3set /a seq=%cnt% * 8set /a cnt=%cnt% + 1call set char=%%headtab:~%seq%^,8%%if "%char%" EQU "########" goto Export6set /a pos=10000%char:~0,4%%%10000set /a len=10000%char:~4,4%%%10000call set char=%%line:~%pos%^,%len%%%set char="%char%"set char=%char:   =%:Export4set work=%char:  "="%if %work% NEQ %char% set char=%work%&goto Export4set char=%char: "="%if %char% EQU "" goto Export5if %char% EQU " " goto Export5set char=%char:;=:%set query=%query%;%char%goto Export3:Export5set query=%query%;goto Export3:Export6set work=%query:~1,9999%@echo %work%>>"%TEMP%UserCSV.TM2"