Converting the master term file to a Localist-formatted .csv

Owned by Denise Kidd
Sept 24, 2024
3 min read

Once the term config master spreadsheet is available, the term config team will let the website academic calendar team know it’s ready for publishing.

Through Summer 2025, the academic calendar team uses an Excel workbook with formulas to convert the data in the term config master file to a file formatted for Localist upload.

Download the Localist template >

Event names

Because Localist requires a unique name for each event, we use the following format for event names:

[Term] [Event] [(PoT)]

Example:

Fall 2024 Classes Begin (LMJ)

Set up the Localist workbook

We’re making a copy of a like term Localist master workbook and formatting it in preparation of the term config master file data. We’ll paste the term config master file data into a tab in the Localist master workbook.

Make a copy of a like term “Localist Master” workbook from Box > Registrar > Academic Calendar > ‘Term’ > Localist. Change the name so that it accurately reflects the term you’re working with. Make the following modifications:

  1. Remove all tabs EXCEPT the following. Basically, you are removing all tabs that include concatenated or queried data.

    1. Other Dates

    2. Master

    3. Resource Sheet

    4. Individual PoT tabs

  2. Update the Other Dates spreadsheet to accurately reflect holidays and other dates used by the other parts of term. Not all PoTs may need to have all of events listed in Other Dates. This tab is to make it easy to create or modify formulas to pull these dates in to the PoT tabs.

  3. On the Resource Sheet, update the Term Code and Term Name. Compare the term config master with the list of PoTs and their descriptions.

    1. Note that we include some parts of term in Localist that are not configured in Banner such as LA (Liberal Arts), SSE (Science & Engineering), BMS (Biomedical Sciences). Every part of term that you want displayed on the website, whether or not it’s configured in Banner should be in this list.

  4. Delete all data in the Master tab.

Copy the data in the term config master file

In a new tab or window, go to Box > RegistrarReporting > Term Config, and identify the folder with the term you’re working with. In that folder, you’ll find the term config master file. Copy everything from the term config master file and paste it into the Master tab of your Localist Master. Then, close the term config master file.

Review and clean up tab data

Review each of the PoT tabs to make sure there’s valid data in the Date From and Date To columns. Update formulas as appropriate.

Note that some dates must be manually entered rather than pulled in by formula. Note that the Date From and Date To values are ALWAYS the same.

Pull data from all tabs together

Rather than using Box’s Excel app, open the Localist master workbook from your Box drive on your computer. Use Power Query to pull the data from all the tabs together in one tab.

First, format the data in each tab as a table. I recommend naming each table as the respective PoT.

Next, use Power Query to consolidate the data from all the tabs into a single tab. Instructions >

Copy all the data from the Query tab and paste into a new tab. Create a blank column after Column E. In Column F concatenate Columns A through E. Create a blank column after Column G. Copy Column F and ‘paste as values’ into Column G. The value in Column G is the event title you’ll use in the Localist template.

Add data to the Localist template

Paste Column G from the Localist master into the Title column.

Paste the Date From and Date To from the Localist Master into Date From and Date To.

Paste the tags into the Tags column.

In the template, for Department, enter Office of the University Registrar for each event.

In Event Type, enter Academic Dates & Deadlines for each event.

Save the file in Box > Registrar > Academic Calendar > ‘Term’ > Localist > Uploads using the .csv file type. Save all upload files, including revisions or additions.

Upload to Localist

Upload the file to Localist. Once complete, spot check entries in Localist against the term config master. If needed, update or create events manually.