Importing ICS Calendar to Office 365

This short article will present "How to import ICS Calendar onto Office 365 Calendar". I was searching for free (or not free) tools to convert ICS to CSV without success. The only tools I found & working well were online tools...this was too hard to manually process all my ICS files (more than 200 calendar). So, I write 2 scripts to do what I need !

This article will be helpful if you have to migrate a Linux Mail Server to Office 365; It works with all standard ICS standard format (Google calendar or other mail products).

I wrote 2 scripts to do this :

- The first one : This script will parse the ICS file and convert it to CSV File. You have to create a "source" folder and store into it all ICS Files. The script will parse & copy all new generated CSV files onto a new folder.

- The second one :  This script will read the CSV File and import it to Office 365. This script read all CSV files and use each line to create a appointment on Office 365.

First script : You have to copy your ICS file with the format "emailaddress@youdomain.com.ics" on the ICS Folder

# Benjamin MOREAU - 13/05/2015
                      # ICS To CSV converter
                      $ICSpath="...\ICS\" 
                      
                      #Your ICS Folder (input)
                      $CSVpath="...\CSV\" 
                      
                      # Your CSV Folder (output)
                      $ICSlist = get-childitem $ICSPath 
                      
                      #Read each ICS file on your ICS folder
                      
                      Foreach ($i in $ICSlist )
                      {     
                      $file= $i.fullname         
                      #Convert your ICS filename with CSV extension     
                      $CSVFile = $CSVpath  +  $i.name     
                      $CSVFile = $CSVFile.substring(0,$CSVFile.Length-3)     
                      $CSVFile = $CSVFile + "csv"    
                      
                      #Create first line of CSV
                      "Titre,Note,StartDate,Starttime,EndDate,EndTime" | out-file -FilePath $CSVFile        
                      
                      #get content of your file    
                      $content = Get-Content $file -Encoding UTF8          
                      $content | foreach-Object {
                                #If we found ":", we cut on 2 parts => key:values      
                      if($_.Contains(':')){            
                      $z=@{ $_.split( ':')[0] =( $_.split( ':')[1]).Trim()}
                                                      
                      if ($z.keys -eq "BEGIN") 
                      {           
                      #If we detect "BEGIN", It's the start of the appointment block ...but we do nothing :)           
                      }           
                      
                      
                      if ($z.keys -eq "DTSTART;VALUE=DATE") {
                                 #If we detect date without time we use 00:00:00
                      
                                 $RdvStart=$z.values -replace "r\n\s"
                                 $RdvStart=[datetime]::ParseExact($RdvStart,"yyyyMMdd" ,$null)
                                 $RdvStartDate = $RdvStart.ToShortDateString()
                                 
                                 $RdvStartDate = get-date $RdvStartDate -Format MM/dd/yyyy
                                 $RdvStartTime = "00:00:00"
                                 
                                 }
                      
                      
                      if ($z.keys -eq "DTSTART") {                      
                      $RdvStart=$z.values -replace "r\n\s"           
                      $RdvStart=$RdvStart -replace "T"           
                      $RdvStart=$RdvStart -replace "Z"           
                      $RdvStart=[datetime]::ParseExact($RdvStart,"yyyyMMddHHmmss" ,$null)           
                      $RdvStartDate = $RdvStart.ToShortDateString()           
                      $RdvStartTime = $RdvStart.ToLongTimeString()
                      $RdvStartDate = get-date $RdvStartDate -Format MM/dd/yyyy
                      
                      }
                      
                      if ($z.keys -eq "DTEND") 
                      {
                                 $RdvEnd=$z.values -replace "\r\n\s"           
                      $RdvEnd=$RdvEnd -replace "T"           
                      $RdvEnd=$RdvEnd -replace "Z"           
                      $RdvEnd=[datetime]::ParseExact($RdvEnd,"yyyyMMddHHmmss" ,$null)           
                      $RdvEndDate = $RdvEnd.ToShortDateString()           
                      $RdvEndTime = $RdvEnd.ToLongTimeString()                      
                      $RdvEndDate = get-date $RdvEndDate -Format MM/dd/yyyy                      
                      }           
                      if ($z.keys -eq "SUMMARY") 
                      {           
                      $RdvTitre=$z.values -replace "\r\n\s"           
                      $RdvTitre=$z.values -replace ",","-"           
                      }           
                      if ($z.keys -eq "DESCRIPTION") 
                      {           
                      $RdvNote=$z.values -replace "\r\n\s"           
                      $RdvNote=$RdvNote -replace "<p>"           
                      $RdvNote=$RdvNote -replace "</p>"           
                      $RdvNote=$RdvNote -replace ",","-"                      
                      }           
                      
                      if ($z.keys -eq "DTEND;VALUE=DATE") {
                                 #If we detect a date without time, we use 00:00:00
                      
                                 $RdvEnd=$z.values -replace "r\n\s"
                                 $RdvEnd=[datetime]::ParseExact($RdvEnd,"yyyyMMdd" ,$null)
                                 $RdvEndDate = $RdvEnd.ToShortDateString()
                                 
                                 $RdvEndDate = get-date $RdvEndDate -Format MM/dd/yyyy
                                 $RdvEndTime = "00:00:00"
                                 
                                 }
                      
                      
                      
                      if ($z.keys -eq "END") {            
                      # We detect "END", so we add the line on the CSV File. 1 line = 1 appointment
                      
                      "$RdvTitre,$RdvNote,$RdvStartDate,$RdvStartTime,$RdvEndDate,$RdvEndTime" | out-file -FilePath $CSVFile -append                        
                      
                      # clearing vars...           
                      $RdvStart =""           
                      $RdvEnd =""           
                      $RdvNote=""           
                      $RdvTitre=""           
                      $RdvEndDate = ""           
                      $RdvEndTime = ""           
                      $RdvStartDate = ""           
                      $RdvStartTime = ""                      
                      }       
                      }}}

Select all Open in new window

Second script : You have to configure your Office 365 access...

# Benjamin MOREAU - 15/05/2015
                      # CSV to Office 365
                      
                      
                       $username = "your mail of Office 365 admin account"
                       $password = "your password"
                       $EWSURL = "https://outlook.office365.com/EWS/Exchange.asmx"
                        
                      
                      
                      $EWSManagedApiPath = "C:\Program Files\Microsoft\Exchange\Web Services\2.0\Microsoft.Exchange.WebServices.dll"
                      
                       if (!(Get-Item -Path $EWSManagedApiPath -ErrorAction SilentlyContinue))
                       {
                           throw "EWS Managed API could not be found at $($EWSManagedApiPath).";cl
                       }
                      
                      [void][Reflection.Assembly]::LoadFile($EWSManagedApiPath);
                      
                      
                       $service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1)
                       $service.Credentials = New-Object  Microsoft.Exchange.WebServices.Data.WebCredentials($Username,$Password);
                       $service.URL = New-Object Uri($EwsUrl)
                       $CalendarFolder = [Microsoft.Exchange.WebServices.Data.CalendarFolder]::Bind($service, [Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Calendar);
                      
                      
                      $CSVpath="...\CSV\" # Your CSV folder
                      $CSVlist = get-childitem $CSVPath
                      
                      
                      #Read CSV Files
                      Foreach ($i in $CSVList ){
                           $file= $i.name
                      	 
                       
                           $EmailAddress = $i.name
                      $EmailAddress = $EmailAddress.substring(0,$file.Length-4)
                      	
                           $CSVFile = Import-Csv -Path $i.fullname
                        
                      
                           
                           $service.ImpersonatedUserId = New-Object Microsoft.Exchange.WebServices.Data.ImpersonatedUserId([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress, $EmailAddress);
                           
                           Write-Host $EmailAddress
                           
                           
                      
                           # Parse the CSV file and add the appointments
                      
                          foreach ($CalendarItem in $CSVFile)
                      { 
                      	# Create appointment
                      	$NoError=$true;
                      	
                      		$Appointment = New-Object Microsoft.Exchange.WebServices.Data.Appointment($service);
                      		$Appointment.Subject=$CalendarItem."Titre";
                      		
                      		
                      		write-host ($CalendarItem."StartDate" + " " + $CalendarItem."StartTime")
                              $StartDate=[DateTime]($CalendarItem."StartDate" + " " + $CalendarItem."StartTime");
                      		
                              $Appointment.Start=$StartDate;
                      		$EndDate=[DateTime]($CalendarItem."EndDate" + " " + $CalendarItem."EndTime");
                      		$Appointment.End=$EndDate;
                              if ($CalendarItem.Note) {$Appointment.Body=$CalendarItem.Note}
                               
                      	
                      	
                      	
                      
                      		# Upload Appointment on Office 365
                      		$Appointment.Save([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Calendar);
                      		Write-Host "Created $($CalendarItem."Subject")" -ForegroundColor green;
                      	
                      
                      }
                      
                      
                      
                      }

Select all Open in new window

Be careful, the time to process the CSV files to import onto Office 365 can be slow (depending of the Office 365 response time). For example, for 1 calendar with 1000 appointment, it can take more than 5 minutes to upload.

Other important point is the date format. In the ICS to CSV script, I invert day and month. I do that because my Office 365 accept only US format like Month/day/Year. I use this function to convert it :    

undefined

Select all Open in new window

So, if you want to use this script with an on premise Exchange server, check the format your server will accept (depending on your regional settings).

I hope this will be helpful for a lot...