Add Contacts to Outlook using Powershell and a csv file

The Story

My wife has a beautysalon that she runs from a room in our house. She signed up with a beautyvoucher and she received regular updates via mail containing a CSV file with contact information of customers who bought a voucher for her salon. She asked me if I could create emailcontacts using the information in those CSV files. So, instead of manually copy-pasting everything, I decided to use powershell to automate this process. When I got it all working I used the script as a model to build a new script for general use and posted it her for your reading pleasure.

The Script

The script contains the following:

  • An option to list all properties of a contact which you can use as headers for your CSV file.
  • An option to create an empty CSV file containing all possible properties/headers.
  • An option to change the delimiter for the CSV so it can be opened/edited with MS Excel versions from all over the world. (Edit: has been removed, see bottom of the post)
  • A function that converts all headers into contact properties.

Here are a few examples of how the script would be used (also available using the Get-Help command) :

-------------------------- EXAMPLE 1 --------------------------

C:\PS>.\Import-OutlookContacts.ps1 -FilePath "C:\Users\Pete\Documents\Contacts.csv"

This will create Outlook content based on the Contacts.csv file.

-------------------------- EXAMPLE 2 --------------------------

C:\PS>.\Import-OutlookContacts.ps1 -FilePath "C:\Users\Duncan\Documents\Contacts.csv" -Delimeter ';'

This will create Outlook contacts based on the Contacts.csv file. The CSV file uses

the ';' character to seperate headers and values.

-------------------------- EXAMPLE 3 --------------------------

C:\PS>.\Import-OutlookContacts.ps1 -ListAvailableHeaders

This will generate a list of possible headers to use for your csv file.

-------------------------- EXAMPLE 4 --------------------------

C:\PS>.\Import-OutlookContacts.ps1 -GenerateEmptyCSV "C:\Users\Ryan\Documents\Contacts.csv" -Delimiter ';'

This will create an empty CSV file with all possible headers seperated by the character ';'.

Get it here!

You can get the whole script here. Either click to view or right-click Save as to download.

Later, folks!

Edit: Thanks to Clemens’ comments the script now works on PowerShell version 2. Thanks Clemens!

16-11-2016 : Moved this script to GitHub and performed a small overhaul. The delimeter parameter has been removed.

Advertisements

About MicaH

I'm a Technical Specialist at PepperByte BV (the Netherlands).
This entry was posted in Powershell and tagged , , , , , , . Bookmark the permalink.

25 Responses to Add Contacts to Outlook using Powershell and a csv file

  1. does not work. breaks from the word “go.” ugh! you need a bit more info, documentation, etc.

    • MicaH says:

      I agree, the script is a bit quick and dirty. I just needed it one time so I made it for that occasion only. When it was succesful I posted it to help others who need a good starting point. I’ll make improvements if I have time (some day ;^)).

  2. More info: [D] Do not run [R] Run once [S] Suspend [?] Help (default is “D”): r
    New-Object : Retrieving the COM class factory for component with CLSID {0006F03
    A-0000-0000-C000-000000000046} failed due to the following error: 80080005.
    At C:\scripts\Import-OutlookContacts.ps1:118 char:22
    + $outlook = new-object <<<< -com Outlook.Application
    + CategoryInfo : ResourceUnavailable: (:) [New-Object], COMExcept
    ion
    + FullyQualifiedErrorId : NoCOMClassIdentified,Microsoft.PowerShell.Comman
    ds.NewObjectCommand

    You cannot call a method on a null-valued expression.
    At C:\scripts\Import-OutlookContacts.ps1:119 char:46
    + $contacts = $outlook.session.GetDefaultFolder <<<< (10)
    + CategoryInfo : InvalidOperation: (GetDefaultFolder:String) [],
    RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

    Import-Csv : Cannot bind argument to parameter 'Path' because it is an empty st
    ring.
    At C:\scripts\Import-OutlookContacts.ps1:122 char:18
    + $csv = Import-Csv <<<< $FilePath -Delimiter $Delimiter
    + CategoryInfo : InvalidData: (:) [Import-Csv], ParameterBindingV

    • MicaH says:

      So basically powershell is unable to load the outlook application. Without the loaded COM+ application the rest of the script will fail also. A quick google search got me this KB article that may be of use to you:

      http://support.microsoft.com/kb/870655

      Hopefully the workaround in this article will speed you on your way!

  3. MicaH says:

    I’ve made some serious improvements to this script. It now get’s the contact properties from outlook itself so there will be no issues with different properties in different outlook versions.

  4. Clemens says:

    Hi there, wanted to first create the csv file to see how its suposed to look like.
    Ends always up with a zero byte file :/ tried to track down the error and found:

    $contacts.Items | select -First 1 | gm -MemberType property | Where-Object {$_.definition -like ‘string*{set}*’}.Name

    Where-Object : Das Argument kann nicht an den Parameter “FilterScript” gebunden werden, da es NULL ist.
    Bei Zeile:1 Zeichen:75
    + $contacts.Items | select -First 1 | gm -MemberType property | Where-Object <<< $contacts.Items | select -First 1 | gm -MemberType property | Where-Object {$_.definition -like ‘string*{set}*’}

    TypeName: System.__ComObject#{00063021-0000-0000-c000-000000000046}

    Name MemberType Definition
    —- ———- ———-
    Account Property string Account () {get} {set}
    AssistantName Property string AssistantName () {get} {set}

    So what does the “.Name” do, and do you know, why its not working?

    Thx and greetings!

    • MicaH says:

      Hi Clemens,

      What this command does (or is supposed to do) is list all contacts, select the first one, get all the properties, filter out the read-only properties and create an array of only the property names (that’s what the .Name does). Can you tell me if the $contacts variable contains any address book information? And if so, are there any items in $contacts.items? This script will only work if you have at least one contact already. I’ve added some checks to the script to improve error handling. Please re-download, try again and report back the error you’re getting now.

      • MicaH says:

        Clemens, I’ve made another change to the script. It now also works if you have no contacts in your address book. Thank you for reporting this bug and please let me know if these changes were helpful.

      • Clemens says:

        thx for replying so fast!
        $contacts.items returns a loong list of all my contacts with all its entries. looks fine.

        filtering the first contact ( select -First 1) and showing only its properties (gm -MemberType Property) also works. Finding those properties, that contain ‘string*{set}*’ also works:

        $contacts.Items | select -First 1 | gm -MemberType property | Where-Object {$_.definition -like ‘string*{set}*’}

        TypeName: System.__ComObject#{00063021-0000-0000-c000-000000000046}

        Name MemberType Definition
        —- ———- ———-
        Account Property string Account () {get} {set}
        AssistantName Property string AssistantName () {get} {set}
        AssistantTelephoneNumber Property string AssistantTelephoneNumber () {get} {set}
        BillingInformation Property string BillingInformation () {get} {set}

        However, adding the “.Name” at the end, returns an error:
        $contacts.Items | select -First 1 | gm -MemberType property | Where-Object {$_.definition -like ‘string*{set}*’}.Name
        Where-Object : Das Argument kann nicht an den Parameter “FilterScript” gebunden werden, da es NULL ist.
        Bei Zeile:1 Zeichen:75
        + $contacts.Items | select -First 1 | gm -MemberType property | Where-Object <<<< {$_.definition -like 'string*{set}*'}.Name
        + CategoryInfo : InvalidData: (:) [Where-Object], ParameterBindingValidationException
        + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.WhereObjectCommand

        The german error message says something like: The argument cannot be bound to the parameter "FilterScript", because it is NULL.

        I saw the lines that you added, however the two cases (!$contacts and $contacts.Items.Count -lt 1) are not relevant for my error :/

      • MicaH says:

        for the .name to work you need to put brackets around the commands.Like so:

        ($contacts.Items.Add() | gm -MemberType property | ?{$_.definition -like ‘string*{set}*’}).name.

        I’ve made a small adjustment to the script. I’ve created a separate function for property retrieval. It should be more clear how it works now. Please re-download and test the script.

      • Clemens says:

        ok, the () are necessary, I am not familiar with PS, but I see, the .Name is applied to the whole expression.

        I downloaded the latest version of your script. I looked at the function Get-ContactProperties.

        $Props contains again the properties:
        $props
        TypeName: System.__ComObject#{00063021-0000-0000-c000-000000000046}

        Name MemberType Definition
        —- ———- ———-
        Account Property string Account () {get} {set}
        AssistantName Property string AssistantName () {get} {set}

        however $Props.Name does not return anything, hence

        $properties = Get-ContactProperties sets $properties to an empty variable, and therefore the CSV file remains empty.

      • MicaH says:

        What version Powershell are you using?

      • Clemens says:

        Good Point:

        $PSVersionTable
        Name Value
        —- —–
        CLRVersion 2.0.50727.5477
        BuildVersion 6.1.7601.17514
        PSVersion 2.0
        WSManStackVersion 2.0
        PSCompatibleVersions {1.0, 2.0}
        SerializationVersion 1.1.0.1
        PSRemotingProtocolVersion 2.1

      • MicaH says:

        Yup. You’re on PS2 and I only tested on PS4. I’ve updated the script and tested it using PS2 and it now works like a charm! Give it a try. Thank you for bringing this to my attention and good luck this Sunday against Argentina!

  5. Clemens says:

    thanks! now it works for me, too! and yes, it will be a big party, hopefully! Same to you for tomorrow, as well!

  6. holian says:

    Dear MicaH,

    First of all thnx for this great tutorial / script.

    I would like to ask you to help get it work. My main goal is to empty my outlook contacts every night and than import contacts from csv to it. The source csv is ready, but whatever i want to import with your script i get empty contacts imported. (i tried with my source csv with generated from exchange get-contact, than i made an export from my outlook and than try to import this file..but no luck.)

    Any idea whats wrong?

    Thank you.

  7. holian says:

    Never mind…something was wrong with my headers… its ok now.

  8. Ana Almarza says:

    How can I get the last script, the one that works properly? It would be very helpful for me. Thanks.

    • MicaH says:

      Hi Ana. I’ve made a few improvements to the script and moved it to my Github. Please re-download the script and give it a spin. If it’s not working for you, please let me know what versions of PowerShell and Outlook you’re using and what the error message is. If it is working I’d like to know as well.

      • Ana Almarza Ordoyo says:

        Thanks, I’ll give it a try tomorrow and I’ll tell you.

        Regards,

        Enviado desde mi dispositivo Samsung

      • Ana Almarza Ordoyo says:

        Hi Micah,

        Anything I run, it does nothing:

        [cid:image001.png@01D240B9.A5DAE800]

      • MicaH says:

        I can’t view the image. Do all options fail? If you run

        Import-OutlookContacts -ListPossibleHeaders

        do you get a list of possible properties? Also, I’ve turned the script into a function so you’ll first have to run the script to load the function and then use the function itself. Like so:

        . .\ImportContacts.ps1
        Import-OutlookContacts -ListPossibleHeaders

        The first period in the first line is essential.

  9. mohan says:

    hi Micah,
    i am tring to run the script , but nothing happens.
    tried different ways as you suggested. no error or output.
    .\ImportContacts.ps1 Import-OutlookContacts -GenerateEmptyCSV -FilePath “E:\contacts.csv”

    using:
    $PSVersionTable

    Name Value
    —- —–
    PSVersion 3.0
    WSManStackVersion 3.0
    SerializationVersion 1.1.0.1
    CLRVersion 4.0.30319.42000
    BuildVersion 6.2.9200.16481
    PSCompatibleVersions {1.0, 2.0, 3.0}
    PSRemotingProtocolVersion 2.2

    • MicaH says:

      The script is not meant to have output. It either creates an empty csv file or imports the contacts in a csv file in outlook. The only option that returns output is the -ListAvailableHeaders parameter.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s