<

Importing ICS Calendar to Office 365

Published on
8,419 Points
5,319 Views
1 Endorsement
Last Modified:
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 = ""                      
}       
}}}

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;
	

}



}

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

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...


 
1
Comment
2 Comments
LVL 10

Author Comment

by:Benjamin MOREAU
Ok, I think it's OK.
0
LVL 38

Expert Comment

by:Jian An Lim
I hope you still active on this topic.
I am very interest to read on your solutions/scripts.

However, by converting that .ICS to .CSV, it actually drop 60% of the original file size, means data are not getting into the mailbox.


What i am reading is https://msdn.microsoft.com/en-us/library/office/dn672319(v=exchg.150).aspx#sectionSection2 
that is will allow EWS to import the ICS file into the Exchange Online directly.

Being not a programmer, i will need to figure out how to do so


private static void UploadMIMEAppointment(ExchangeService service)
{
    Appointment appointment = new Appointment(service);

    string iCalFileName = @"C:\import\appointment.ics";

    using (FileStream fs = new FileStream(iCalFileName, FileMode.Open, FileAccess.Read))
    {
        byte[] bytes = new byte[fs.Length];
        int numBytesToRead = (int)fs.Length;
        int numBytesRead = 0;

        while (numBytesToRead > 0)
        {
            int n = fs.Read(bytes, numBytesRead, numBytesToRead);

            if (n == 0)
                break;

            numBytesRead += n;
            numBytesToRead -= n;
        }

        // Set the contents of the .ics file to the MimeContent property.
        appointment.MimeContent = new MimeContent("UTF-8", bytes);
    }

    // This results in a CreateItem call to EWS. 
    appointment.Save(WellKnownFolderName.Calendar);
}

Open in new window

0

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Join & Write a Comment

Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Basic Overview of office 365 user portal

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month