In the first article of this series we discussed what CSVDE is and why you should use it. I will now discuss how to export data from Active Directory and how to import it in Microsoft Excel using CSVDE as well as touch on some basic data manipulation commands.
Exporting Data from AD
The easiest way to learn CSVDE is through a baptism by fire – try out the commands yourself. However, most of you will think “How can I experiment with AD without ruining my setup?” The easiest way to do this is by learning to use the export commands, which simply exports data and does not in any way modify the setup that there is in place.
When using the CSVDE tool, the command will always be formatted as follows:
CSVDE <-switch> or CSVDE <-switch> <switch requirement>
CSVDE –f users.csv runs the default CSVDE function, exporting all data to a file users.csv
The easiest way to view the data that has been exported by CSVDE is to locate the CSV file that was created, and open it using your favourite spreadsheet utility (all of them work, but here all examples will refer to Microsoft Excel). Note that CSVDE always exports to or imports from the current directory you are browsing in command prompt, thus if you are currently in c:\ it will save the files to c:\.
There a various switches that can be used and here’s a complete list with a full description. Here we will only discuss a few of the commands.
The most commonly used export switches:
- -f Specifies file name to export to:
CSVDE –f example.csv
Note: Try not to use this switch alone, as it will export too much data for it to be comprehensible and thus useful to us.
- -d Specifies a particular Object to export, such as an OU:
CSVDE -d “OU=<ouname>,DC=<domainname>,dc=com” -f example.csv
- -r Specifies which rows you want to export, such as users using the objectClass or object category:
CSVDE –f example.csv –r objectClass=Person
You can even combine multiple objects in your filter:
CSVDE -f userdata2.csv -r “(&(objectCategory=person)(objectclass=user))”
- -L Specifies which LDAP fields, or columns, will be exported, separated by a comma:
CSVDE –f example.csv –l “DN, objectClass, givenName, sn, name”
Do not mix –l with –i as they are used for two completely different things, thus since CSVDE is not case sensitive it would be wise to use –L to avoid confusion.
- -m Used to exclude Active Directory properties such as the ObjectGUID, objectSID, pwdLastSet and samAccountType attributes.
- -n Used to exclude binary values from the exported CSV file.
Thus, for example let us imagine you are an administrator, and you have been contacted by HR to produce a list of all the users in the Sales OU, with their Full Name, Logon and full distinguished name and without other junk such as binary data and so on. The command used to produce such a CSV file would be:
CSVDE -d “OU=Test,dc=bernard8,dc=com” -m -n -f testou.csv -r objectclass=user -l “name, userPrincipalName,dn”
In this case the first line of the CSV file would read:
“CN=John Smith,OU=Test,DC=test,DC=com”,John Smith,email@example.com
For some reason CSVDE sorts the LDAP fields in its own way no matter how you enter the data.
Now you should be confident enough with CSVDE to be ready to try the import switch, which we will discuss in part 3 of this article.