Tuesday, May 11, 2010

How to Dump/Export GAL to CSV file.

This is one of the most common request comes time to time, although there are many ways to accomplish the task I will show you one of the easiest one.

Log into one of your management server , Csvde.exe is a Microsoft Windows 2000 command-line utility, so open CMD prompted. Copy and paste below…..

CSVDE -f GalDump.csv -r objectCategory=person -l "displayName, title, telephoneNumber, department, mail"

 

image

 

All you need to do it to open the CSV file called GalDump.csv , the fiel will be on the same directory as you ran the command.

If you want everything use this one

CSVDE -f GalDumpAll.csv -r objectCategory=person

 KB555397

image

Tips

If you look at the first column you will figured out the attribute names and you can use this to modify the first CSVDE to fit to your needs

Best regards,
Oz Casey , Dedeal
MCITP (EMA), MCITP (SA)
MCSE 2003, M+, S+, MCDST
Security+, Project +, Server +
Http://smtp25.blogspot.com (Blog
Http://telnet25.spaces.live.com (Blog)
Http://telnet25.wordpress.com (Blog

2 comments:

jw said...

CSVDE has a bug where it will mangle data when dumping out a large number of items, I've come up with a powershell script that does a much better job using ADSI:

$date = get-date -format yyyyMMdd
$fname = "userDump-$date.csv"
#creates a variable named $root that binds to the ADSI path in quotes
$root = [ADSI]"LDAP://DC=domain,DC=com"
# variable with the sub OU's that we will search on since I want to skip certain OU's in the domain
$DN = "OU=some,OU=OU", "OU=someother,OU=OU", "CN=Users"

$DN2 = $DN | foreach {
$root.PSBase.Children.find($_)
}

filter Format-DumpADUser {
$_ | select @{name='distinguishedName';Expression={$_.distinguishedName} },
@{ name='homeMDB'; Expression={$_.homeMDB} },
@{ name='displayName'; Expression={$_.displayName} },
@{ name='mail'; Expression={$_.mail} },
@{ name='employeeID'; Expression={$_.employeeID} },
@{ name='legacyExchangeDN'; Expression={$_.legacyExchangeDN} },
@{ name='proxyAddresses'; Expression={$_.proxyAddresses} },
@{ name='memberOf'; Expression={$_.memberOf} },
@{ name='sAMAccountName'; Expression={$_.sAMAccountName} },
@{ name='whenChanged'; Expression={$_.whenChanged} },
@{ name='whenCreated'; Expression={$_.whenCreated} },
@{ name='extensionAttribute1'; Expression={$_.extensionAttribute1} },
@{ name='extensionAttribute2'; Expression={$_.extensionAttribute2} },
@{ name='extensionAttribute3'; Expression={$_.extensionAttribute3} },
@{ name='extensionAttribute4'; Expression={$_.extensionAttribute4} },
@{ name='extensionAttribute5'; Expression={$_.extensionAttribute5} },
@{ name='extensionAttribute6'; Expression={$_.extensionAttribute6} },
@{ name='extensionAttribute7'; Expression={$_.extensionAttribute7} },
@{ name='extensionAttribute8'; Expression={$_.extensionAttribute8} },
@{ name='extensionAttribute9'; Expression={$_.extensionAttribute9} },
@{ name='extensionAttribute10'; Expression={$_.extensionAttribute10} },
@{ name='extensionAttribute11'; Expression={$_.extensionAttribute11} },
@{ name='extensionAttribute12'; Expression={$_.extensionAttribute12} },
@{ name='extensionAttribute13'; Expression={$_.extensionAttribute13} },
@{ name='extensionAttribute14'; Expression={$_.extensionAttribute14} },
@{ name='extensionAttribute15'; Expression={$_.extensionAttribute15} },
@{ name='mailNickname'; Expression={$_.mailNickname} },
@{ name='mDBUseDefaults'; Expression={$_.mDBUseDefaults} },
@{ name='mDBOverQuotaLimit'; Expression={$_.mDBOverQuotaLimit} },
@{ name='mDBStorageQuota'; Expression={$_.mDBStorageQuota} }
}

$DN2 | foreach {
$_.PSBase.Children
} | Format-DumpADUser | export-csv -NoTypeInformation E:\Directory-Dumps\Archive\$fname

Oz Casey Dedeal said...

Nice thanks for providing PS, which is always welcome, the CSVDE I used recently was able to export little over 250K GAL into CSV with no time and issues by the way (-:

I liked it because it is so simple and short (-:

thanks again for the PS
ocd