This article will instruct you on preparing a calendar data import sheet and bulk importing events into your website in SchoolStatus Sites. 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 SchoolStatus Sites.
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 Dates
The Start and End date fields must be formatted properly before your import sheet will process. Here is how to do 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" events
Handling 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.
Once you have all of the data in the sheet ready, you can import the sheet.
How do I import data for Calendar events?
- After logging into your site, click Settings, the gear located in the top navigation bar, followed by System Settings in the left navigation on the next screen.
- Click on Imports followed by the Import button at the top-right of the screen, then select Events as the import type.
- On the following screen, enter the name of the import, 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 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 choose your sheet, and click Save.
- With your sheet now uploaded, click the three vertical dots again to the right of the job and select Mappings from the options given. This is simply telling SchoolStatus 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 SchoolStatus (the field names at left). Once done, click Save.
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).
- Now click the three vertical dots to the right of the job one last time and select Process Import from the options given, select Test, and if there are no errors, select Process. If there are any problems, SchoolStatus Sites 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.