Skip navigation

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 Documents\Export.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 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

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.
Publish