JSI Tip 7993. How can I export Active Directory contact data to a delimited file?

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

The syntax for using ContactCSV.bat is:

Contactcsv 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 How do I know what attribute names to use when performing a 'DSQUERY *'?).

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

NOTE: See How do I add a contact to Active Directory using the command-line?


ContactCSV "%UserProfile%\My Documents\Export.csv" name telephonenumber mail

would create an Export.csv file that might contain:

"Sample Contact";"12345678900";"[email protected]"

ContactCSV.bat contains:

@echo off
if \{%2\}

\{\} @echo Syntax: ContactCSV CSVFile Attribut1 \[Attribute2 ... AttributeN\]&exit /b setlocal set CSVFile=%1 if exist %CSVFile% del /q %CSVFile% if exist "%TEMP%\ContactCSV.TM2" del /q "%TEMP%\ContactCSV.TM2" set /a cnt=0 set attr=%2 set attr=%attr:"=% set line="%attr%" set dsquery=dsquery * domainroot -filter "(&(objectClass=Contact))" -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%\ContactCSV.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%\ContactCSV.TM2" /O "%TEMP%\ContactCSV.TM3" copy "%TEMP%\ContactCSV.TM1"+"%TEMP%\ContactCSV.TM3" %CSVFile% if exist "%TEMP%\ContactCSV.TM1" del /q "%TEMP%\ContactCSV.TM1" if exist "%TEMP%\ContactCSV.TM2" del /q "%TEMP%\ContactCSV.TM2" if exist "%TEMP%\ContactCSV.TM3" del /q "%TEMP%\ContactCSV.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%\ContactCSV.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.