How to export calendar items to csv-file with powershell

Article
2013-12-23

Question

_{Monday, December 23, 2013 9:21 PM}

Hi guys,

Does anyone know how to export calendar items, from a specific user to a CSV-file with a Power Shell script?

Example:

How do I export all calendar items from the mailbox [email protected] to a CSV-file with a Power Shell script?

Thanks for you support!

Kind regards,

Jordi Martin Lago

All replies (11)

_{Monday, December 23, 2013 10:00 PM ✅Answered | 1 vote}

There is no standard for exporting a calendar Item to a CSV file so you may need to consider what your going to do with the data and what fidelity you need on the data (eg do you need to know for instance if your exporting meeting what the attendees are etc).

In addition to the Outlook examples Fred linked you an do the same thing with EWS eg

## Get the Mailbox to Access from the 1st commandline argument

$MailboxName = $args[0]

## Load Managed API dll  
Add-Type -Path "C:\Program Files\Microsoft\Exchange\Web Services\2.0\Microsoft.Exchange.WebServices.dll"  
  
## Set Exchange Version  
$ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP2  
  
## Create Exchange Service Object  
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService($ExchangeVersion)  
  
## Set Credentials to use two options are availible Option1 to use explict credentials or Option 2 use the Default (logged On) credentials  
  
#Credentials Option 1 using UPN for the windows Account  
$psCred = Get-Credential  
$creds = New-Object System.Net.NetworkCredential($psCred.UserName.ToString(),$psCred.GetNetworkCredential().password.ToString())  
$service.Credentials = $creds      
  
#Credentials Option 2  
#service.UseDefaultCredentials = $true  
  
## Choose to ignore any SSL Warning issues caused by Self Signed Certificates  
  
## Code From http://poshcode.org/624
## Create a compilation environment
$Provider=New-Object Microsoft.CSharp.CSharpCodeProvider
$Compiler=$Provider.CreateCompiler()
$Params=New-Object System.CodeDom.Compiler.CompilerParameters
$Params.GenerateExecutable=$False
$Params.GenerateInMemory=$True
$Params.IncludeDebugInformation=$False
$Params.ReferencedAssemblies.Add("System.DLL") | Out-Null

$TASource=@'
  namespace Local.ToolkitExtensions.Net.CertificatePolicy{
    public class TrustAll : System.Net.ICertificatePolicy {
      public TrustAll() { 
      }
      public bool CheckValidationResult(System.Net.ServicePoint sp,
        System.Security.Cryptography.X509Certificates.X509Certificate cert, 
        System.Net.WebRequest req, int problem) {
        return true;
      }
    }
  }
'@ 
$TAResults=$Provider.CompileAssemblyFromSource($Params,$TASource)
$TAAssembly=$TAResults.CompiledAssembly

## We now create an instance of the TrustAll and attach it to the ServicePointManager
$TrustAll=$TAAssembly.CreateInstance("Local.ToolkitExtensions.Net.CertificatePolicy.TrustAll")
[System.Net.ServicePointManager]::CertificatePolicy=$TrustAll

## end code from http://poshcode.org/624
  
## Set the URL of the CAS (Client Access Server) to use two options are availbe to use Autodiscover to find the CAS URL or Hardcode the CAS to use  
  
#CAS URL Option 1 Autodiscover  
$service.AutodiscoverUrl($MailboxName,{$true})  
"Using CAS Server : " + $Service.url   
   
#CAS URL Option 2 Hardcoded  
  
#$uri=[system.URI] "https://casservername/ews/exchange.asmx"  
#$service.Url = $uri    
  
## Optional section for Exchange Impersonation  
  
#$service.ImpersonatedUserId = new-object Microsoft.Exchange.WebServices.Data.ImpersonatedUserId([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress, $MailboxName) 
#Bind to Calendar    
$folderid= new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Calendar,$MailboxName)     
$Calendar = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderid)    
  
$exportCollection = @() 
#Define Date to Query 
$StartDate = (Get-Date).AddDays(-30)
$EndDate = (Get-Date).AddDays(7)  
  
#Define the calendar view  
$CalendarView = New-Object Microsoft.Exchange.WebServices.Data.CalendarView($StartDate,$EndDate,1000)    
$fiItems = $service.FindAppointments($Calendar.Id,$CalendarView)    
foreach($Item in $fiItems.Items){      
    $exportObj = "" | Select StartTime,EndTime,Subject,Location
    "Start    : " + $Item.Start  
    "Subject  : " + $Item.Subject  
    $exportObj.StartTime = $Item.Start
    $exportObj.EndTime = $Item.End
    $exportObj.Subject = $Item.Subject
    $exportObj.Location = $Item.Location
    $exportCollection +=$exportObj
}
$FileName = (Get-Location).Path.ToString() + "\CaledarExport-$MailboxName-" + (Get-Date).ToString("yyyy-MM-dd-hh-mm-ss") + ".csv"
$exportCollection | Export-Csv -NoTypeInformation -Path $FileName

Cheers
Glen

_{Thursday, June 26, 2014 10:42 AM}

Hi Glen,

I used that code for a job I need to run here.

I made a few changes to suit me; but it really, really helped.

Thank you very much,

Colin

_{Tuesday, December 22, 2015 11:52 PM}

Glen, How do you adjust this code to export the same for secondary calendars? This is only pulling the data for the primary calendar.

_{Wednesday, December 23, 2015 4:23 AM}

Try something like

## Get the Mailbox to Access from the 1st commandline argument

$MailboxName = $args[0]

## Load Managed API dll  
Add-Type -Path "C:\Program Files\Microsoft\Exchange\Web Services\2.0\Microsoft.Exchange.WebServices.dll"  
  
## Set Exchange Version  
$ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP2  
  
## Create Exchange Service Object  
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService($ExchangeVersion)  
  
## Set Credentials to use two options are availible Option1 to use explict credentials or Option 2 use the Default (logged On) credentials  
  
#Credentials Option 1 using UPN for the windows Account  
$psCred = Get-Credential  
$creds = New-Object System.Net.NetworkCredential($psCred.UserName.ToString(),$psCred.GetNetworkCredential().password.ToString())  
$service.Credentials = $creds      
  
#Credentials Option 2  
#service.UseDefaultCredentials = $true  
  
## Choose to ignore any SSL Warning issues caused by Self Signed Certificates  
  
## Code From http://poshcode.org/624
## Create a compilation environment
$Provider=New-Object Microsoft.CSharp.CSharpCodeProvider
$Compiler=$Provider.CreateCompiler()
$Params=New-Object System.CodeDom.Compiler.CompilerParameters
$Params.GenerateExecutable=$False
$Params.GenerateInMemory=$True
$Params.IncludeDebugInformation=$False
$Params.ReferencedAssemblies.Add("System.DLL") | Out-Null

$TASource=@'
  namespace Local.ToolkitExtensions.Net.CertificatePolicy{
    public class TrustAll : System.Net.ICertificatePolicy {
      public TrustAll() { 
      }
      public bool CheckValidationResult(System.Net.ServicePoint sp,
        System.Security.Cryptography.X509Certificates.X509Certificate cert, 
        System.Net.WebRequest req, int problem) {
        return true;
      }
    }
  }
'@ 
$TAResults=$Provider.CompileAssemblyFromSource($Params,$TASource)
$TAAssembly=$TAResults.CompiledAssembly

## We now create an instance of the TrustAll and attach it to the ServicePointManager
$TrustAll=$TAAssembly.CreateInstance("Local.ToolkitExtensions.Net.CertificatePolicy.TrustAll")
[System.Net.ServicePointManager]::CertificatePolicy=$TrustAll

## end code from http://poshcode.org/624
  
## Set the URL of the CAS (Client Access Server) to use two options are availbe to use Autodiscover to find the CAS URL or Hardcode the CAS to use  
  
#CAS URL Option 1 Autodiscover  
$service.AutodiscoverUrl($MailboxName,{$true})  
"Using CAS Server : " + $Service.url   
   
#CAS URL Option 2 Hardcoded  
  
#$uri=[system.URI] "https://casservername/ews/exchange.asmx"  
#$service.Url = $uri    
  
## Optional section for Exchange Impersonation  


#Define Function to convert String to FolderPath  
function ConvertToString($ipInputString){  
    $Val1Text = ""  
    for ($clInt=0;$clInt -lt $ipInputString.length;$clInt++){  
            $Val1Text = $Val1Text + [Convert]::ToString([Convert]::ToChar([Convert]::ToInt32($ipInputString.Substring($clInt,2),16)))  
            $clInt++  
    }  
    return $Val1Text  
} 
$exportCollection = @() 
#Define Date to Query 
$StartDate = (Get-Date).AddDays(-30)
$EndDate = (Get-Date).AddDays(7)  

#Define Extended properties  
$PR_FOLDER_TYPE = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(13825,[Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Integer);  
$folderidcnt = new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::MsgFolderRoot,$MailboxName)  
#Define the FolderView used for Export should not be any larger then 1000 folders due to throttling  
$fvFolderView =  New-Object Microsoft.Exchange.WebServices.Data.FolderView(1000)  
#Deep Transval will ensure all folders in the search path are returned  
$fvFolderView.Traversal = [Microsoft.Exchange.WebServices.Data.FolderTraversal]::Deep;  
$psPropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)  
$PR_Folder_Path = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition(26293, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::String);  
#Add Properties to the  Property Set  
$psPropertySet.Add($PR_Folder_Path);  
$fvFolderView.PropertySet = $psPropertySet;  
#The Search filter will exclude any Search Folders  
$sfSearchFilter = new-object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.FolderSchema]::FolderClass,"IPF.Appointment")  
$fiResult = $null  
#The Do loop will handle any paging that is required if there are more the 1000 folders in a mailbox  
do {  
    $fiResult = $Service.FindFolders($folderidcnt,$sfSearchFilter,$fvFolderView)  
    foreach($ffFolder in $fiResult.Folders){  
        $foldpathval = $null  
        #Try to get the FolderPath Value and then covert it to a usable String   
        if ($ffFolder.TryGetProperty($PR_Folder_Path,[ref] $foldpathval))  
        {  
            $binarry = [Text.Encoding]::UTF8.GetBytes($foldpathval)  
            $hexArr = $binarry | ForEach-Object { $_.ToString("X2") }  
            $hexString = $hexArr -join ''  
            $hexString = $hexString.Replace("FEFF", "5C00")  
            $fpath = ConvertToString($hexString)  
        }  
        "FolderPath : " + $fpath  
        #Define the calendar view  
        $CalendarView = New-Object Microsoft.Exchange.WebServices.Data.CalendarView($StartDate,$EndDate,1000)    
        $fiItems = $service.FindAppointments($ffFolder.Id,$CalendarView)    
        foreach($Item in $fiItems.Items){      
            $exportObj = "" | Select FolderPath,StartTime,EndTime,Subject,Location
            "Start    : " + $Item.Start  
            "Subject  : " + $Item.Subject
            $exportObj.FolderPath = $fpath
            $exportObj.StartTime = $Item.Start
            $exportObj.EndTime = $Item.End
            $exportObj.Subject = $Item.Subject
            $exportObj.Location = $Item.Location
            $exportCollection +=$exportObj
        }
    } 
    $fvFolderView.Offset += $fiResult.Folders.Count
}while($fiResult.MoreAvailable -eq $true)    

$FileName = (Get-Location).Path.ToString() + "\CaledarExport-$MailboxName-" + (Get-Date).ToString("yyyy-MM-dd-hh-mm-ss") + ".csv"
$exportCollection | Export-Csv -NoTypeInformation -Path $FileName

_{Friday, September 16, 2016 2:10 PM}

Our calendar Appointment includes custom forms added by developer tools.

I have been playing this exporting script, but cannot get custom forms in csv export file. Is there any way to get these custom forms in csv-file with this script.

More information about custom fields

https://msdn.microsoft.com/en-us/library/office/ff863938.aspx
http://www.slipstick.com/developer/designing-custom-forms/
http://www.slipstick.com/developer/publishing-custom-forms/

_{Monday, September 19, 2016 2:57 AM}

You need to use a MapiEditor to work out what Mapi properties are being used and then included these as extended properties in your code https://msdn.microsoft.com/en-us/library/office/dd633691(v=exchg.80).aspx and https://blogs.technet.microsoft.com/heyscriptingguy/2011/12/02/learn-to-use-the-exchange-web-services-with-powershell/

Cheers
Glen

_{Monday, September 19, 2016 2:46 PM}

Thank you Glen. I managed to made some progress.

Our exchange server version is 2016. Blog post mentioned ExFolders tool, but it wont run on our exchange. I don't know how I'm able to get the "property names". Do you have an idea how?

https://gallery.technet.microsoft.com/Exchange-2010-SP1-ExFolders-e6bfd405

_{Monday, September 19, 2016 4:24 PM}

Now I noticed the MapiEditor, what you did mention. So, I downloaded this https://mfcmapi.codeplex.com/

Here are the properties what I need to get http://oi64.tinypic.com/n71khx.jpg

And here is the PowerShell code, how I'm trying to

## Get the Mailbox to Access from the 1st commandline argument

$MailboxName = $args[0]

## Load Managed API dll  
Add-Type -Path "C:\Program Files\Microsoft\Exchange server\V15\bin\Microsoft.Exchange.WebServices.dll"  
  
## Set Exchange Version  
$ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP2  
  
## Create Exchange Service Object  
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService($ExchangeVersion)  
  
## Set Credentials to use two options are availible Option1 to use explict credentials or Option 2 use the Default (logged On) credentials  
  
#Credentials Option 1 using UPN for the windows Account  
#$psCred = Get-Credential  
$creds = New-Object System.Net.NetworkCredential("kalenteri",$psCred.GetNetworkCredential().password.ToString())  
$service.Credentials = $creds      
  
#Credentials Option 2  
#service.UseDefaultCredentials = $true  
  
## Choose to ignore any SSL Warning issues caused by Self Signed Certificates  
  
## Code From http://poshcode.org/624
## Create a compilation environment
$Provider=New-Object Microsoft.CSharp.CSharpCodeProvider
$Compiler=$Provider.CreateCompiler()
$Params=New-Object System.CodeDom.Compiler.CompilerParameters
$Params.GenerateExecutable=$False
$Params.GenerateInMemory=$True
$Params.IncludeDebugInformation=$False
$Params.ReferencedAssemblies.Add("System.DLL") | Out-Null

$TASource=@'
  namespace Local.ToolkitExtensions.Net.CertificatePolicy{
    public class TrustAll : System.Net.ICertificatePolicy {
      public TrustAll() { 
      }
      public bool CheckValidationResult(System.Net.ServicePoint sp,
        System.Security.Cryptography.X509Certificates.X509Certificate cert, 
        System.Net.WebRequest req, int problem) {
        return true;
      }
    }
  }
'@ 
$TAResults=$Provider.CompileAssemblyFromSource($Params,$TASource)
$TAAssembly=$TAResults.CompiledAssembly

## We now create an instance of the TrustAll and attach it to the ServicePointManager
$TrustAll=$TAAssembly.CreateInstance("Local.ToolkitExtensions.Net.CertificatePolicy.TrustAll")
[System.Net.ServicePointManager]::CertificatePolicy=$TrustAll

## end code from http://poshcode.org/624
  
## Set the URL of the CAS (Client Access Server) to use two options are availbe to use Autodiscover to find the CAS URL or Hardcode the CAS to use  
  
#CAS URL Option 1 Autodiscover  
#$service.AutodiscoverUrl($MailboxName,{$true})  
#"Using CAS Server : " + $Service.url   
   
#CAS URL Option 2 Hardcoded  
  
$uri=[system.URI] "https://localhost/ews/exchange.asmx"  
$service.Url = $uri    
  
## Optional section for Exchange Impersonation  
  
#$service.ImpersonatedUserId = new-object Microsoft.Exchange.WebServices.Data.ImpersonatedUserId([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress, $MailboxName) 
#Bind to Calendar    
$folderid= new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Calendar,$MailboxName)     
$Calendar = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderid)    
  
$exportCollection = @() 
#Define Date to Query 
$StartDate = (Get-Date).AddDays(-30)
$EndDate = (Get-Date).AddDays(7)  
  
#Define the calendar view  
$CalendarView = New-Object Microsoft.Exchange.WebServices.Data.CalendarView($StartDate,$EndDate,1000)    

$CalendarView.PropertySet = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)

$fiItems = $service.FindAppointments($Calendar.Id,$CalendarView)    


foreach($Item in $fiItems.Items){      
    
    
    $psPropset = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)

    #Create extended properties
    $OhjelmanNimi = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition([Microsoft.Exchange.WebServices.Data.DefaultExtendedPropertySet]::Appointment, 0x8481, [Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Binary)

    #Add extended properties to properties set
    $psPropset.Add($OhjelmanNimi);

    #Add properties to calendar view
    $CalendarView.PropertySet = $psPropset;

    #Load properties into current appointment
    $Item.Load($psPropset);

    $OhjelmanNimiRef = @()

    $Item.TryGetProperty($OhjelmanNimi, [ref] $OhjelmanNimiRef)

    "OhjelmanNimi: " + $OhjelmanNimi
    "OhjelmanNimi Ref: " + $OhjelmanNimiRef 


    $exportObj = "" | Select StartTime,EndTime,Subject,Location
    "Start    : " + $Item.Start  
    "Subject  : " + $Item.Subject  
    $exportObj.StartTime = $Item.Start
    $exportObj.EndTime = $Item.End
    $exportObj.Subject = $Item.Subject
    $exportObj.Location = $Item.Location
    $exportCollection +=$exportObj
    
    "Property count: " + $Item.ExtendedProperties.Count

    foreach($ExtendedProperty in $Item.ExtendedProperties) {
        "Property: " + $ExtendedProperty.PropertyDefinition.Name
    }

    " "
}
$FileName = (Get-Location).Path.ToString() + "\CaledarExport-$MailboxName-" + (Get-Date).ToString("yyyy-MM-dd-hh-mm-ss") + ".csv"
$exportCollection | Export-Csv -NoTypeInformation -Path $FileName

_{Monday, September 19, 2016 4:54 PM}

I managed to get this done!

The key thing was

    $OhjelmanNimi = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition([Microsoft.Exchange.WebServices.Data.DefaultExtendedPropertySet]::PublicStrings,"Ohjelman nimi",[Microsoft.Exchange.WebServices.Data.MapiPropertyType]::String);

foreach($Item in $fiItems.Items){      
      
    $psPropset = new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)

    #Create extended properties
    $OhjelmanNimi = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition([Microsoft.Exchange.WebServices.Data.DefaultExtendedPropertySet]::PublicStrings,"Ohjelman nimi",[Microsoft.Exchange.WebServices.Data.MapiPropertyType]::String);

    #Add extended properties to properties set
    $psPropset.Add($OhjelmanNimi);

    #Add properties to calendar view
    $CalendarView.PropertySet = $psPropset;

    #Load properties into current appointment
    $Item.Load($psPropset);


    $exportObj = "" | Select StartTime,EndTime,Subject,Location
    "Start    : " + $Item.Start  
    "Subject  : " + $Item.Subject  
    $exportObj.StartTime = $Item.Start
    $exportObj.EndTime = $Item.End
    $exportObj.Subject = $Item.Subject
    $exportObj.Location = $Item.Location
    $exportCollection +=$exportObj

    foreach($ExtendedProperty in $Item.ExtendedProperties) {
        "Property: " + $ExtendedProperty.PropertyDefinition.Name + ": " + $ExtendedProperty.Value
    }

    " "
}

_{Thursday, January 19, 2017 3:09 PM}

Hi Glen,

Is there anyway that this script can be modified to show the calendars that the mailbox being queried has full access to?

In our case, we have a meetings mailbox that has full access to all of our resource rooms and would like to show their calendars as well.

Thanks

ShGuy

_{Friday, January 20, 2017 3:49 AM}

No its not because that information isn't back linked (Delegates are back linked (publicdelgatesbl Active Directory property https://msdn.microsoft.com/en-us/library/aa563283(v=exchg.80).aspx ) but this is not the only way someone may grant access) so you need to query every calendars ACL to work that out. In your case it probably just easier to put whatever calendars you want to access into a Group and update the group as you need. Then write whatever code you want to get the calendars to access from the group and query those calendars.

Cheers
Glen

Share via

How to export calendar items to csv-file with powershell

Question

All replies (11)

Additional resources