Wednesday, June 25, 2008

Use VBScript to Set the Country for an AD Contact

I was recently asked to produce a VBScript that would take data imported from a very useless CRM system as a CSV and create contacts in Active Directory.

All was going swimmingly until I got to the last field of the contact - country.  The source database allowed free text for this field and AD expects to get a 2 letter ISO country code.

I found a web site with the country codes listed and copy and pasted it into Word.

Next I selected all the text and pressed Shift+F3 to toggle it all into upper case.  This gave me a long list in the format


Where XX is the corresponding country code.  (For example, Australia is AU)

I copied and pasted the resulting list into Primal Script.  This is where Primal Script came into its own and saved me hours.

I created two new Snippets in the Primal Script Snippet manager

image The CountryCase snippet contains the following code:


If I select a country name and double click the CountryCase snippet it replaces it with a correctly indented Case statement with the selected text inside the quotes.  The snippet also included a new line character, so the associated country code would be on the next line.



The CountryCode snippet contained the following code:


which would create a line of code that sets a string to the selected country code.  Again this was appropriately indented and contained a newline character after it.


    strCC = "AU"

The result was that I could double click the country name, double click the Country Case snippet, double click the country code, then double click the CountryCode snippet and I had added that case to the SELECT statement.  Within 35 minutes I had added a massive Select Case structure that handled hundreds of countries.

The result is too long to post here, but if anyone needs it drop me a comment or an email.