This article will instruct you on 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.
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. You can find the Department ID of a department by taking your cursor over the department's title in the department list view and recording the series of numbers after the last forward slash (shown above).
- Friendly Dept Name - Optional. Since you can import events into multiple departments on 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 departments 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 to your site via the tag manager before the import sheet will process.
- Teaser - 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 DatesThe 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 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 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" eventsHandling the times that are displayed 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 a 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 Settings, the gear located in the top navigation bar, followed by System Settings in the left navigation on the next screen. Then, click Imports followed by the green Import button at the top-right of the screen then select Events as the import type. Add the name, which is just a name that is friendly to you. This is not shown to the public. You can also add an optional description. Then if you like click, on the switch to the right of Indexed to allow for the syncing up of events with any future imports, and finally click Create. You'll now see your import job ready to manage. Click the three vertical dots to the right of the job and select Upload New Sheet to upload your import sheet. Browse to select your sheet, and click Save.
With your sheet now uploaded, click the three vertical dots to the right of the job and select Mappings from the options given. 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 (Index if you chose to have the import Indexed). Once done, click Save.
Now click the three vertical dots to the right of the job and select Process Import from the options given, select Test, and if there are no errors select Process. If there are any problems, Campus Suite will tell you. If it successfully imports, you will be taken back to the import jobs screen and see your job now is labeled Processed (also Updatable if the index switch was clicked).
Congratulations, you can now go to your departments and find all your events.