Reading this will likely save you time and effort. We will take a closer look at the spreadsheet in this chapter, discuss how to enter (or copy-paste) data, how to export to a CSV and prepare your data for bulk upload.

Tempting as it may be we advise against uploading the working sample CSVs to give the process a try, because you will have to clean the data from your institutions course catalogue afterwards. They are merely examples of how your importable CSV will look like.

Sheets in the Excel workbook

Please do not remove sheeet protection. You shall only use the "Programme" and "Course" sheets to enter data. The rest of the spreadsheet should remain read-only . "Course" and "Programme" sheets are also protected, to guard validation rules and conditional formatting from being overwritten by pasting content, but you will not be prevented from typing and pasting data into them.

The Excel workbook contains following sheets:

• "Intro" Describes the data points present in the sheets below and clarifies some basic concepts related to how the data is structured. Also contains a link to an additional source of information, the OCCAPI v2 specification.
• "Programme" The sheet to enter Programme data into. Contains drop-downs, data validation and conditional formatting to allow checking invalid data by highlighting cells with incorrect values.
• "Programme - Export" This is a read-only sheet to export Programme data from. It contains automation to gather rows from the Programme sheet, substitute values entered via drop-downs with values the course catalogue system is expecting.
• "Course" and "Course - Export" Same principle as the "Programme" and "Programme - Export" sheets. Contains tools and validation that should make entering Course data easier and more reliable.
• "Values - *" Sheets having names prefixed with "Values - " are read-only sheets that match values entered via drop-downs with values the course catalogue system is expecting.

Entering data in the spreadsheet

1. Use the Programme and Course Sheets (colored green) to enter or paste your data. Other sheets should remain read-only. They are used to build the sheets to be exported to CSV in the end of the data entry process. The sheets have validation rules and conditional formatting set up to row 500.
2. In the two editable sheets, columns marked with an asterix (*) after their header names are required data points, make sure to fill them for every record
3. You will be prevented from typing most invalid data by validation rules
4. If you paste rows and columns from other spreadsheets, validation will not prevent entering invalid data, but it will be highlighted using a red background
5. Test the whole process with a small set of data first, before doing a mass import

Exporting data to UTF-8 CSV

Once you are done with data entry, you can export the data to a CSV file that can be imported into the course catalogue system.

1. Go to the "Programmes - Export" or "Course - Export" sheet
2. If you simply use Save as for the export sheets, your original spreadsheet will be referencing the CSV file you just created, which comes with a set of problems. Read on to see how to avoid this.
3. Right click on the sheet name and pick "Move or Copy"
4. Use "To book: (new sheet)" and check the "Create a copy" checkbox
5. An Excel file should open in a new window
6. Unprotect the single sheet in the new file (Review > Unprotect Sheet)
7. Since the export sheets have formulas, when exporting, empty rows will appear at the bottom of the CSV. To prevent this select the rows with no data up to row 500 by clicking the numbers on their left, right click and choose "Delete". This will get rid of rows with empty cells
8. Save this file using the format CSV UTF-8 (Comma delimited) (*.csv), using any name

Importing to the EWP Dashboard

If you have your CSV(s) ready, you can start the import process in the EWP Dashboard.

1. Always import Programmes first and Courses after
2. Click on the Course Catalogue menu and pick Programmes or Courses
3. Click on the Import button around the top right corner, browse the file and import it
4. If Programmes or Courses in the spreadsheet already exist with the same code in the Course Catalogue, their data will be updated
5. Validation in the Excel spreadsheet does not cover all cases, so you might have to adjust your data according to the feedback you get on the user interface of the EWP dashboard when attempting to upload the CSV file