EWS: Importing bank holidays from an Outlook.hol based CSV file


This is a follow up post for Outlook Code: Importing bank holidays from an Outlook.hol based CSV file.

I’ve written the script a while back and I thought I’d update for ease of use and to add some extra functionality.

The script uses the EWS Managed API to connect to the mailboxes so please make sure that you have downloaded and installed the API before launching the script: http://www.microsoft.com/en-us/download/details.aspx?id=42022.


Update: 22/01/2015

Added some extra switches to add support for different date formats (ie. mm/dd/yyyy or dd/mm/yyyy) and setting the Busy status of the newly created entries. Removed the IgnoreSSLCertificate switch as the code seemed to break the script.

The new entries no longer have a reminder and they now show up as appointments rather than meetings.


Please find the syntax information below:

Process-Holidays -EmailAddress <string> 
                          -CountryName <string> 
                          [-Year <integer>] 
                          [-CSVFilePath <string>] 
                          [-CultureInfoName <string>]
                          [-FBStatus <sting>]
                          [-EwsUrl <string>]
                          [-EWSManagedApiPath <string>]
                          [-TimeZoneName <string>]
                          [-ExchangeVersion <string>]
                          [-Impersonate]
                          [-UseAutodiscover]
                          [-UseDefaultCredentials]
                          [-TrustAllCertificates] 
                          [-Import]
                          [-List]
                          [-ListAll]
                          [-Update]
                          [-Remove]
                          [-RemoveAll]
                          [-Help]

PARAMETERS

<></>

Required parameters  
-EmailAddress The e-mail address of the mailbox we are connecting to.
-CountryName The name of the Country the holidays are observed in.
Optional parameters  
-Year Specifies the Year in which the holidays occur. Can only be used with the 'List' switch.
-CSVFilePath

Specifies the path towards the CSV file containing the entries to be added, removed or updated. Can only be used with the 'Import', 'Remove' or 'Replace' switch. The CSV file needs to contain four columns : 'Country', 'Holiday', 'Date' and 'NewDate'. The 'NewDate' column is only required with the 'Update' switch.

-CultureInfoName Specifies the CultureInfoName to be used for date formats amongst other things. The default value is 'en-GB' which means the expected date format in the input file is in the 'dd/mm/yyyy' format (i.e. 31/01/2015 for the 31st January 2015). If your date format is 'mm/dd/yyyy' (i.e.  01/31/2015 for the 31st January 2015), then please use en-US. For example -CultureInfoName  en-US.
-FBStatus Allows you to specify the busy status of the newly created entries. By default the busy status is set to 'Free'. The possible values are 'Free', 'Busy' and 'OOF'
-EwsUrl

Specifies the EWS URL to connect to. For example: https://mail.contoso.com/ews/exchange.asmx. This parameter is optional if the 'UseAutodiscover' switch is used.

-EWSManagedApiPath

Specifies the path to the Microsoft.Exchange.Services.dll. The EWS Managed API can be downloaded at: http://www.microsoft.com/en-us/download/details.aspx?id=42022. You may need to use this parameter if the script prompts you to do so.

-TimeZoneName Specifies the time zone to be used for creating the appointments. If not specified, the local time zone will be used.
-ExchangeVersion Specifies the Exchange version to be used by the Managed API. The highest version supported by the server is recommended. By default, the script is configured to use Exchange2007_SP1. The options are: Exchange2007_SP1, Exchange2010, Exchange2010_SP1, Exchange2010_SP1, Exchange 2013 and Exchange2013_SP1
Switches  
-Impersonate

This switch needs to be used if you are accesing a mailbox other than the one of the account used to run the script. For instructions on how to grant impersonation permissions please visit: http://msdn.microsoft.com/en-us/library/exchange/bb204095(v=exchg.140).aspx.

-UseAutodiscover

This switch allows you to use Autodiscover to lookup the connection information. If you use this switch, you do not need to specify an EWS Url using hte -EwsUrl switch.

-UseDefaultCredentials This switch instructs PowerShell to use the default credentials.
-TrustAllCertificates This switch instructs PowerShell to trust all certificates.
-Import

Use this switch in conjunction with the -CSVFilePath to perform a holiday import. New appointments will be created in the target mailbox based on the content of the CSV file.

-List

Use this switch to list the existing holidays for the specified Country in the year specified using the 'Year' parameter. The 'Year' parameter is required for this switch.

-ListAll

Use this switch to list all the existing holidays for the specified country.

-Update

Use this switch in conjunction with the -CSVFilePath to find and update existing holidays. The CSV file will need to contain a 'Date' column and a 'NewDate' column. Existing appointments will be moved to the date specified in the 'NewDate' column.

-Remove

Use this switch in conjunction with the -CSVFilePath switch to remove the existing holidays corresponding to the entries in the CSV file.

-RemoveAll Use this to remove all existing holidays for the specified County.

EXAMPLES

Example 1
-----------------
Process-Holidays.ps1 -EmailAddress user1@contoso.com -CountryName 'United Kingdom' -CSVFilePath C:\Holidays.csv
-EwsUrl
https://mail.contoso.com/ews/exchange.asmx -Impersonate -UseDefaultCredentials -TrustAllCertificates -Update

CSV file content
-----------------
Country,Holiday,Date,NewDate
United Kingdom,Battle of the Boyne (N. Ireland),12/07/2014,13/07/2014
United Kingdom,Boxing Day,26/12/2014,27/12/2014

Explanation
-----------------
The script will connect to the user1@contoso.com mailbox using the default credentials and Exchange impersonation.
It will lookup the two holidays based on the holiday name and the date specified in the 'Date' column and will move
them to the date specified in the 'NewDate' column.

Example 2
-----------------
Process-Holidays.ps1 -EmailAddress user1@contoso.com -CountryName 'United Kingdom' Year 2014 -EwsUrl
https://mail.contoso.com/ews/exchange.asmx -Impersonate -UseDefaultCredentials -TrustAllCertificates -List

Explanation
-----------------
The script will connect to the user1@contoso.com mailbox using the default credentials and Exchange impersonation.
It will lookup the holidays for United Kingdom occurring in 2014 and list them in the PowerShell window .

Example 3
-----------------
Process-Holidays.ps1 -EmailAddress user1@contoso.com -CountryName 'United Kingdom' -CSVFilePath C:\Holidays.csv
-UseAutodiscover -Impersonate -UseDefaultCredentials -TrustAllCertificates -Import

CSV file content
-----------------
Country,Holiday,Date
United Kingdom,Battle of the Boyne (N. Ireland),12/07/2014
United Kingdom,Boxing Day,26/12/2014

Explanation
-----------------
The script will connect to the user1@contoso.com mailbox using the default credentials and Exchange impersonation.
It will lookup the connection information using Autodiscover and create two holidays based on the holiday name and
the date specified in the 'Date' column.

Example 4
-----------------
Process-Holidays.ps1 -EmailAddress user1@contoso.com -CountryName 'United Kingdom' -CSVFilePath C:\Holidays.csv
-UseAutodiscover -Impersonate -UseDefaultCredentials -TrustAllCertificates -Remove

CSV file content
-----------------
Country,Holiday,Date
United Kingdom,Battle of the Boyne (N. Ireland),12/07/2014
United Kingdom,Boxing Day,26/12/2014

Explanation
-----------------
The script will connect to the user1@contoso.com mailbox using the default credentials and Exchange impersonation.
It will lookup the connection information using Autodiscover and locate and remove the two holidays based on the
holiday name and the date specified in the 'Date' column.

Example 5
-----------------
Process-Holidays.ps1 -EmailAddress user1@contoso.com -CountryName 'United Kingdom' -UseDefaultCredentials
-EwsUrl
https://mail.contoso.com/ews/exchange.asmx -Impersonate -TrustAllCertificates -RemoveAll

Explanation
-----------------
The script will connect to the user1@contoso.com mailbox using the default credentials and Exchange impersonation.
It will locate and remove the holidays for United Kingdom. This request is slow as all calendar entries
are processed.

Example 6
-----------------
Process-Holidays.ps1 -EmailAddress user1@contoso.com -CountryName "United Kingdom" -Impersonate -EwsUrl
https://mail.contoso.com/ews/exchange.asmx -UseDefaultCredentials -CSVFilePath "C:\Holidays1.csv" -ExchangeVers
ion "Exchange2010_SP2" -ListAll

Explanation
-----------------
The script will connect to the user1@contoso.com mailbox using the default credentials and Exchange impersonation.
It will locate and list all the holidays for United Kingdom. This request is slow as all calendar entries are processed.

CSV FILE FORMAT

1. Locating the Outlook.hol file

The Outlook.hol file is located in the 1033 subfolder of the Office install folder. For example: C:\Program Files\Microsoft Office\Office12\1033\Outlook.hol. We need to locate this file and open it using Notepad.exe and copy the holidays that we need into a CSV file.

Say we need the holidays for Germany. We just need to find Germany in the Outlook.hol file and copy all the entries:

image

2. Creating the CSV file

Once the entries have been copied from the Outlook.hol file, open a new instance of Excel and paste the data in. Next, go to the Data tab on the Ribbon and select Text To Columns and split the first column in two columns using Comma “,” as a delimiter. Once you have done that, insert a new column in front of the first column and type the name of the Country on all the rows and set Country, Holiday, Date as the column titles.

The result should be similar to this:

image

Alternatively, if you don’t want to use Excel, paste the copied data in a new Notepad document, insert the name of the country on each line and add Country, Holiday, Date on the first line:

Country,Holiday,Date
Deutschland,Allerheiligen,01/11/2009
Deutschland,Allerheiligen,01/11/2010
Deutschland,Allerheiligen,01/11/2011
Deutschland,Allerheiligen,01/11/2012
Deutschland,Allerheiligen,01/11/2013
Deutschland,Allerheiligen,01/11/2014
Deutschland,Allerheiligen,01/11/2015
Deutschland,Allerheiligen,01/11/2016
Deutschland,Allerheiligen,01/11/2017
Deutschland,Allerheiligen,01/11/2018
Deutschland,Allerheiligen,01/11/2019
Deutschland,Allerheiligen,01/11/2020
Deutschland,Allerheiligen,01/11/2021
Deutschland,Allerheiligen,01/11/2022
Deutschland,Allerheiligen,01/11/2023
Deutschland,Allerheiligen,01/11/2024
Deutschland,Allerheiligen,01/11/2025
Deutschland,Allerheiligen,01/11/2026
Deutschland,Allerheiligen,01/11/2027
Deutschland,Allerheiligen,01/11/2028

Next, save the CSV file.

Process-Holidays.ps1

Comments (6)

  1. mazebaer says:

    Hi, thanks for the script.

    I included a  "$Appointment.IsReminderSet = $false"

    because no holiday has a reminder.

    Also I wanted to include a "SendToNone", to define it is a date, not a appointment, but i didn't get it.

    Any ideas?

    greetings

    M.

  2. Andrei Ghita says:

    Hi there,

    I will post an updated version of the script soon which will correct a few minor issues and I will maybe even add extra functionality.

    Andrei

  3. mazebaer says:

    Hi Andrei,

    I got it. I changed the "$Appointment.Save($CalendarFolderId)" to

    "$appointment.Save([Microsoft.Exchange.WebServices.Data.SendInvitationsMode]::SendToNone)"

    M.

  4. mazebaer says:

    Hi Andrei,

    cool would be the use of a list of mail-addresses, like

    -EmailAddressPath <string>

    instead of a single email address

    Thanks

    M.

  5. Asundsharen says:

    Hi Andrei ,,

    It's a great script and very useful but I also wonder if you are going to add a function for using a CSV-list as Mazebaer wrote.

    regards

    /Bo

  6. Andrei Ghita says:

    Hi,

    I will update the script soon. In the meantime, you can try running it as follows to process multiple mailboxes:

    get-mailbox -RecipientTypeDetails UserMailbox| foreach-object { Process-Holidays.ps1 -EmailAddress $_.PrimarySmtpAddress -EwsUrl zone-ex-01.zone.lab/…/exchange.asmx -IgnoreSSLCertificate -TrustAllCertificates -Impersonate -UseDefaultCredentials -List }

    alternatively, you could use import-csv:

    $csvfile = import-csv -Path "C:tempinput.csv"

    foreach ($line in $csvfile)

    { Process-Holidays.ps1 -EmailAddress $line.EmailAddress -EwsUrl zone-ex-01.zone.lab/…/exchange.asmx -IgnoreSSLCertificate -TrustAllCertificates -Impersonate -UseDefaultCredentials -List }

    Andrei

Skip to main content