Data Import: Excel Timetables
Relevant source files
The following files were used as context for generating this wiki page:The Data Import system is a core feature for populating the Campuspal Room Finder application with essential room and class schedule information. It provides two primary mechanisms for data ingestion from .xlsx files: a web-based upload interface for administrators and a command-line management command for developers and system administrators. This allows for bulk creation and updating of the database, which is fundamental for the application's search and timeline visualization functionalities.
Before any import, existing data in the Room and ClassSchedule tables is cleared to ensure a fresh state. This behavior is consistent across both the web upload and the management command, making it suitable for initial setup and complete data refreshes.
Sources: README.md:21-23, finder/views.py:127, 155, finder/management/commands/load_timetable.py:12
Data Models
The import process populates two key Django models: Room and ClassSchedule. These models store all the necessary information about physical rooms and the events scheduled within them.
Sources: finder/models.py
Entity Relationship Diagram
The diagram below shows the relationship between the Room and ClassSchedule models. A single Room can have multiple ClassSchedule entries associated with it.
Sources: finder/models.py:6, 30
Room Model
This model represents a physical room on campus.
| Field | Type | Description | Constraints |
|---|---|---|---|
name | CharField(100) | The name or number of the room (e.g., "A101"). | Part of unique_room_building constraint. |
building | CharField(100) | The building where the room is located (e.g., "Apollo Building"). | Part of unique_room_building constraint. |
capacity | PositiveIntegerField | The maximum occupancy of the room. Defaults to 0. | |
amenities | JSONField | A JSON list of amenities (e.g., ["Projector", "Whiteboard"]). Defaults to []. | |
photo_url | URLField | A URL to an image of the room. Can be blank. |
Sources: finder/models.py:6-12
ClassSchedule Model
This model represents a single scheduled event, such as a class or lecture, in a specific room.
| Field | Type | Description | Constraints |
|---|---|---|---|
course | CharField(50) | The course code (e.g., "CS101"). | |
subject | CharField(100) | The full subject name (e.g., "Intro to Programming"). | |
day | CharField(3) | The day of the week, chosen from DAY_CHOICES (e.g., "Mon"). | |
start_time | TimeField | The start time of the class. | |
end_time | TimeField | The end time of the class. | |
room | ForeignKey(Room) | A reference to the Room where the class is held. | on_delete=models.CASCADE |
lecturer | CharField(100) | The name of the lecturer. | |
is_cancelled | BooleanField | A flag to mark if the class is cancelled. Defaults to False. |
Sources: finder/models.py:29-47
Excel File Structure
The system expects two separate Excel files: rooms.xlsx and timetable.xlsx. The structure of these files is rigid, and the column names must match exactly.
Sources: generate_sample_data.py, finder/views.py:123, 151, finder/management/commands/load_timetable.py:24
rooms.xlsx
This file contains the list of all rooms.
| Column | Description | Example |
|---|---|---|
Room | The unique identifier for the room within a building. | AB101 |
Building | The name of the building. | Apollo Building |
capacity | The seating capacity of the room. | 50 |
amenities | A JSON-formatted string representing a list of amenities. | ["Projector", "Whiteboard"] |
photo_url | A full URL to a photo of the room. | https://images.unsplash.com/... |
Sources: generate_sample_data.py:42, README.md:162
timetable.xlsx
This file contains the schedule for all classes.
| Column | Description | Example |
|---|---|---|
Course | The course code. | CS101 |
Subject | The full name of the subject. | Intro to Programming |
Day/Time | A formatted string for the schedule (e.g., Mon 09:00-10:30). | Tue 14:00-15:30 |
Room | The name of the room, matching a Room in rooms.xlsx. | AB101 |
Building | The building name, matching a Building in rooms.xlsx. | Apollo Building |
Lecturer | The name of the person teaching the class. | Dr. Smith |
Sources: generate_sample_data.py:69, finder/management/commands/load_timetable.py:25-31
Import Mechanisms
1. Web-Based Upload
A user-friendly interface is available for staff members to upload the Excel files directly through the web application.
Sources: finder/views.py:116-241, finder/templates/finder/upload_excel.html
UI and Form
The upload page is located at /upload/ and is protected, requiring staff member privileges. It features two drag-and-drop zones for the rooms.xlsx and timetable.xlsx files.
The ExcelUploadForm handles the file inputs. It validates that at least one file is uploaded and checks file extensions (.xlsx, .xls) and size limits (5MB for rooms, 10MB for timetable).
Processing Flow
The following sequence diagram illustrates the process when a staff member uploads the files via the web interface.
Sources: finder/views.py:117-241
2. Management Command
For automated deployments or server-side administration, the load_timetable management command can be used. A similar command, load_rooms_excel, exists for rooms.
Sources: finder/management/commands/load_timetable.py, README.md:163-164
Execution
The command is run from the project's root directory:
python manage.py load_timetable
It reads the timetable.xlsx file from the sample_data/ directory.
Sources: finder/management/commands/load_timetable.py:10, 16
Processing Logic
The command first clears all existing ClassSchedule entries. It then iterates through each row of the Excel file, parsing and validating the data before creating new ClassSchedule objects in the database.
This flowchart shows the logic for processing each row in timetable.xlsx.
Data Parsing and Validation
A critical part of the import process is parsing the Day/Time string from timetable.xlsx. The system uses a regular expression to extract the day, start time, and end time.
Day/Time Parsing
The following regex is used to handle formats like "Mon 08:00–09:30" and "Tuesday 8:00 AM-9:30 AM".
# finder/management/commands/load_timetable.py:42-44
match = re.match(
r"(?P<day>\w+)\s+(?P<start>\d{1,2}:\d{2}\s*(?:AM|PM)?)[-–](?P<end>\d{1,2}:\d{2}\s*(?:AM|PM)?)",
day_time)
Sources: finder/management/commands/load_timetable.py:42-44, finder/views.py:182-186
After a successful match, the day string (e.g., "Monday") is mapped to its three-letter abbreviation (e.g., "Mon"). The time strings are then parsed into datetime.time objects, correctly handling both 12-hour (AM/PM) and 24-hour formats.
# finder/management/commands/load_timetable.py:68-77
start_time = timezone.datetime.strptime(
start_time_str,
"%I:%M %p").time() if "AM" in start_time_str or "PM" in start_time_str else timezone.datetime.strptime(
start_time_str,
"%H:%M").time()
end_time = timezone.datetime.strptime(
end_time_str,
"%I:%M %p").time() if "AM" in end_time_str or "PM" in end_time_str else timezone.datetime.strptime(
end_time_str,
"%H:%M").time()
Sources: finder/management/commands/load_timetable.py:68-77, finder/views.py:200-211
Summary
The data import system is a robust and essential component for initializing the Room Finder application. By supporting both a user-friendly web interface and a scriptable management command, it offers flexibility for different use cases. The process includes clearing old data, parsing structured Excel files, and performing validation to ensure data integrity before populating the Room and ClassSchedule database tables. This provides the foundational data upon which all of the application's room searching and availability visualization features are built.