Wednesday, February 4, 2009

Import Outlook Calendar to SharePoint

For Outlook 2003 Users:

1. Create a new calendar list on your SharePoint site. You can do this by clicking Site Actions > View all Site Content then...
click Create
And select Calendar

2. Now that our blank calendar is created we can now work on getting the items imported Open your Microsoft Outlook Calendar and copy the Public Folder by
(a.) right-clicking on the Calendar Icon and
(b.) copying it into a new folder called "Calendar Copy" located in your mailbox folders (you will have to create this folder before copying).

Above: Depending on how old the Calendar and its size it could take 2-20 minutes to copy.

3. Export your Outlook Calendar into a Excel file format with the following columns:
· Subject
· Location
· Start Date
· Start Time
· End Date
· End Time
· All Day event
We can do this by selecting the "Calendar copy" folder that we just created and clicking
File à Import and Export


This will bring up the Wizard view . Select Export to a file

Now select Microsoft Excel 97-2003

Select the location you wish to save the file.

This will bring up the date range configuration popup. Select the appropriate dates.

Confirm the actions and finish the Wizard.

4. Now that your data is in the Excel file let's open it up and see what's in there. At this point you will notice the Start Date and End Date are in two separate columns. Now before we do anything to this data we will need to go back to the blank Calendar list in SharePoint and switch to All Event View. You can do this by entering the list and clicking Modify View àAll Events View


5. Export this empty view to a Excel Spreadsheet and open it. You can do this by clicking
Actions à Export to Spreadsheet

6. Open this exported Outlook Calendar, merge Start Day and Start Time to a single column.
You can use a formula similar to: =CONCATENATE(B:B," ",C:C) or =CONCATENATE(D:D," ",E:E) for a blank column in the same workbook to merge 2 column's data. The "B:B" is the column containing the Start date, the " " adds a space, and the "C:C" is the End Date. If your data is in another you must column change the letters correspond to the appropriate column.
Above: In the image above you can see column M and N hold both Start/End date and times.

7. Merge End Date and End Time to another single column. Both of these two column should have two spaces between date and time and YY as year, not YYYY or 4 digits.

8. Copy all Subjects from the exported Calendar to Title column of the Calendar list. The rest are straight forward. Do not put anything to WorkSpace column since it's read-only.

9. Correct any errors if you have any and please post them within this article's comment for a solution. Then sync this list to the SharePoint calendar.

Final Result:

All items from Outlook are now on our new Calendar.
Source: www.dlocc.com/sharepoint/36-solutions/61-import-outlook-calendar-to-sharepoint.html

4 comments:

  1. I have data validation errors on my Start Date/Time and my End Date/Time cells. When I check the format it is set to custom. mm/dd/yy hh:mm (two spaces between date and time). Any thoughts?

    ReplyDelete
  2. There should be only one space between date and time field.

    ReplyDelete
  3. Thanks for your help it was quite easy to do!

    ReplyDelete
  4. I have a problem that is simailar but more difficult. I am using sharepoint 2.0 and Outlook 2003. I imported a calendar list from a excel file into a custom list on sharepoint 2.0. Everything is configure correctly and working perfectly. The issue is that I realized this custom list does not have 'link to outlook' feature.

    I did not find a way to import spreadsheet to a event list which has 'link to outlook' feature. Please help me if there is a solution. The calendar list is very large so it is impossible to enter them manually. Thanks.

    ReplyDelete