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 alias@domain.com -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 alias@domain.com -username alias@domain.com -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;
$Appointment.IsAllDayEvent=1;
$Appointment.IsReminderSet=0;
More Information
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
Hi,
Thank you for sharing a great workaround to bulk Appointment creation.
I'm facing following issue while executing this script, I have Exchange 2019.
.
Exception calling "AutodiscoverUrl" with "1" argument(s): "The Autodiscover service couldn't be located."
At E:\Program Files\Microsoft\Exchange Server\V15\bin\Import-CalendarCSV.ps1:134 char:2
+ $service.AutodiscoverUrl($EmailAddress)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : AutodiscoverLocalException
Exception calling "Bind" with "2" argument(s): "The Url property on the ExchangeService object must be set."
At E:\Program Files\Microsoft\Exchange Server\V15\bin\Import-CalendarCSV.ps1:144 char:1
+ $CalendarFolder = [Microsoft.Exchange.WebServices.Data.CalendarFolder ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ServiceLocalException
Failed to locate calendar folder
Not sure if this post is still checked but I did all the steps and am getting an error is not recognized as a cmdlet
What version of Exchange are you using?
great script, thank you.
By default this script import the appointments to the default calendar, Is there anyway to specify the non-default calendar ?
This script is written to use the default calendar- it might be possible to change it but my PowerShell skills are not good and i don't know how to do it.
Is there a way to make this script overwrite duplicate appointments?
No, not to my knowledge. Sorry.