This article will instruct you how to prepare a calendar data import sheet, and bulk import events into your web site in Campus Suite. Typically, data can be exported from your current system into .xls or .csv formats. These sheets are OK to use, but the data within will need to be put into the proper format before it can be imported into Campus Suite.
Optionally, you can download the v3 Calendar Import Template (a Google spreadsheet) and use it to populate your events before bulk importing. The only required fields for calendar data imports are the event name, the start date, and the department ID. The department ID is the ID of the department within your web site. You can find the ID to any department by logging in to your dashboard, going to Sites and Departments, and rolling over the desired department. Look down at the bottom-left tray in your browser to see the ID as part of the URL. See screenshot at right (Click for larger view).
Now that you have a populated sheet with all your data, let's review the columns, and any needed special formatting.
- Owning Department ID - Required. ID of the department that should receive the event.
- Friendly Dept Name - Optional. Since you can import events in to multiple departments in the same sheet, this field helps you identify the department whose ID is in the Owning Department ID column.
- Shared Depts - Optional. The ID's of any other departements this event should be shared with, separated by a pipe |.
- Event Title - Required. The name of your event.
- Start Date - Required. The start date and time of your event. If no time, simply use the date. This field requires special formatting described below.
- Start Display - Optional. Used to hide or change the beginning time to either TBA or TBD for all day events. The time will be displayed if kept blank.
- End Date - Optional. The end date and time of your event. This field requires special formatting described below.
- End Display - Optional. Used to hide or change the end time to either TBA or TBD for all day events. The time will be displayed if kept blank.
- Description Text - Optional. Add a description for your event.
- Tags - Optional. You can tag your events so they come directly in already tagged. Note: The tag(s) must be added in your site via the tag manager before the import sheet will process.
- Abstract - Optional. This is a brief description of your event. Schools often add the location here, i.e. "Band Room." The abstract text shows out on the listing views and in widgets before the event is clicked on.
Formatting Start and End Dates
The Start and End date fields must be formatted properly before your import sheet will process. Here is how to it. Note, these instructions are for users using using Google sheets. If you are using Microsoft Excel, you can accomplish the same functions, but the directions will differ.
- Event dates and their times must be formatted in a single column using the following format "YYYY-MM-DD HH:MM:SS" - See an example in the template linked above. Notice there is a Start Date and End Date field. These fields should house both the date and time for the start and end dates.
- Once the sheet is ready for import, in the column name for start and end dates, add something like "ORIGINAL" to the end of the column headers and then make a copy of the column (basically, we are making a back-up of the column because we will be messing with formatting and permanently changing the data. It may be wise to have the original data to come back to, if needed.)
- Select the the new columns and set them both to text. Verify that they are before continuing.
- Now that you’ve made new "Start Date" and "End Date" columns, copy the following formula, and paste it into line 1 of the proper column (it’s OK to paste this in the column header) =TEXT(A1,"yyyy-mm-dd hh:mm:ss.0") to populate the columns (the cell A1 should be changed to whatever the appropriate original column is.)
- Click/drag to fill this formula down each column
- Select the entire column (do Start Date and End Date columns separately)
- Copy the column content (it is OK to get the column name in there too)
- Right-click on the column selection, choose "Paste Special" and then "Paste values only"
- Then, with the column still selected, choose the top menu "Format", then "Number", then "Plain Text"
Hiding the times for "All Day" events
Handling the times that are display for all-day events is done as follows. In the columns that are titled Start Display and End Display (for auto-mapping) in the Calendar Import Template sheet above you would do one of the following;
- Keep the cell empty/blank for the default behavior which is to show the time
- Enter hidden to hide either the start/end date completely
- Enter either tba for TBA wording or tbd for TBD wording
You still MUST have an end date.
There was special meaning in v2.5 with the 23:59:59 times and the 01:31:00 times.
- 01:31:00 time was used for TBA/TBD behavior (we allow both in V3, but they are the same, some may prefer TBA or TBD.
- 23:59:59 was used for when no end time was supposed to be given, so mark those end times as "hidden"
On a 2.5 export sheet, PM's handling this migration for a customer will notice two columns that come out. allday, and natime. Sort by both of these and set the Start and End display date for these items to “hidden” - You will then want to review the start and end times for the events that are left to display... setting ANY times of 01:31:00 to "TBA" or "TBD" in the Start Display and End Display columns accordingly, and any times of 23:59:59 to "hidden" accordingly. This will make the event times appear proper to the public.
Having all the other data in the sheet ready, the sheet will now import.
Importing the data
Login to your dashboard, and click Site Settings in the left navigation. Then, click Imports. Click the green Import button at the top-right of the screen then select Calendar Event as the import type. Add the name, which is just a name that is friendly for you. This is not shown to the public. You can also add an optional description. Then, click Create.
You'll now see your import job ready to manage. Click the up arrow icon to the right of the job to upload your import sheet. Browse to select your sheet, and click Save.
With your sheet now uploaded, click the grid matrix icon to the right of the job to set your mappings. This is simply telling Campus Suite what columns to find your data in. If you used the import sheet we provided above, these mappings should automatically line up. If they don't, simply interact with each of the pull-down menus to specify which column in your sheet should be used to pull that data into the proper column in Campus Suite (the field names at left). It's OK to leave fields blank, but remember, the department ID, title and start date are required. Once done, click Save.
Now click the paper airplane icon to the right of the job to process it. If there are any problems, Campus Suite will tell you. If if successfully imports, you will be taken back to the import jobs screen and see your job now has an eraser icon that can be used to purge the data (if you needed to).
Congratulations, you can now go to your departments and find all your events.