An Exchange administrator needs to import new appointments into several calendars. Their current method is labor-intensive: someone imports the CSV files into each calendar every day. The admin was looking for a macro to make the process easier. While it is possible to use a macro in Outlook to speed the process up, it’s even faster to use PowerShell on the Exchange server.
Although Exchange doesn’t include a cmdlet to import appointments from a CSV file, Dave Barrett published a script that does just this: PowerShell: Script to import calendar items into Exchange mailbox from CSV file
To use this script, you need to:
- Install the EWS Managed API
- Copy the Import-CalendarCSV.ps1 to the Exchange directory at C:\Program Files\Microsoft\Exchange Server\V14\Bin (Not required, but it’s easier to use if you do)
- Configure Exchange Impersonation. You can use the mailbox owner username and password in the cmdlet, instead of impersonation.
- Create CSV file using, at minimum, these fields: Subject, Start Date, Start Time, End Date, End Time
Now you are ready to import the CSV into one mailbox using this command
Import-CalendarCSV -CSVFileName F:\Imports\calendar.csv -EmailAddress firstname.lastname@example.org -Impersonate $true
If you aren't using Impersonation, you need to add the username and password to the cmdlet:
Import-CalendarCSV -CSVFileName F:\Documents\calendar.csv -EmailAddress email@example.com -username firstname.lastname@example.org -password thepassword
Using just the required fields, Free/Busy will be set to Busy and a reminder is set based on the user’s default reminder options.
The Administrator wanted to import notes in the Body, change the Free/Busy state, as well create some All Day Events and set reminders, so we added columns to the CSV for Body, LegacyFreeBusyStatus, IsAllDayEvent, IsReminderSet and ReminderMinutesBeforeStart.
Use Busy, Free, or Tentative as values in the Free/Busy column. The IsAllDayEvent and IsReminderSet columns are left blank for "no"; you can use anything for yes: 1, "x", or the word "Yes" is suggested.
Enter a number for ReminderMinutesBeforeStart (1440 is 1 day). If reminder minutes are set but the reminder isn’t set, a reminder won’t be triggered. If a reminder is set but the reminder minutes field is blank, the reminder is 0 minutes.
Values can be hard-coded in the script. Use 1 for Yes and 0 for No in Yes/No fields such as IsAllDayEvent and IsReminderSet.
$Appointment.LegacyFreeBusyStatus = [Microsoft.Exchange.WebServices.Data.LegacyFreeBusyStatus]::Tentative;
Tweaked script I use and a sample CSV file
Get the PowerShell script here: Import-CalendarCSV.ps1
A list of field names is available here: CalendarItem
Cross-reference field names with the list here: Appointment members