
Moving from a training spreadsheet to a TMS: data cleanup steps
To move a training spreadsheet into a training management system (TMS), treat it as several tables packed into one: learners, sessions, enrollments, trainers and customers. Inventory what each tab holds, normalize the values, match people on a stable key, test every row against written rules, then dry-run the load before cutover.
Most failed imports start with a sheet that was never a clean table: three spellings of one course, one learner under two emails, dates stored as text. This guide gives the order of work and a tested pandas script that turns a messy export into clean tables and a rejects file.
Key takeaways
- Decide what one row means in each tab before touching a value.
- Export to CSV once, keep the original untouched, and read every column as text.
- Match people on a normalized email address; send every other match to a human.
- Give each validation rule an outcome: fix, reject to the owner, or flag for review.
- Dry-run into a scratch database or sandbox, and reconcile every row before cutover.
How do you inventory a training spreadsheet before cleaning it?
List every workbook, tab and owner, then write one sentence per tab saying what a single row means: a course, a session, a learner, or one learner in one session. That row grain decides which TMS records you will create, and it often differs between tabs that look alike.
Record these for each tab:
- Who owns it and who reads it (finance may rely on the invoice tab).
- What one row represents, and which columns identify it.
- Where meaning hides outside the cells: colors, comments, strikethrough, or a status kept in email.
Hidden conventions matter most. If a green row means “confirmed” today, it must become an explicit status value before it can move.
Which training spreadsheet columns become which TMS records?
Most sheets pack several records into every row. Split them by asking what each column describes: a learner, a session, an enrollment, a trainer or a customer. The table uses the vocabulary of our TMS data model; your vendor may use different words for session and enrollment.
| Sheet column | Becomes | Typical cleanup |
|---|---|---|
| Course name | Course (catalog entry) | Many spellings of one course; map each to one code |
| Start date, “3-4 Nov” | Session and its dated meetings | Text dates, ranges in one cell, “TBC”, no time zone |
| Trainer | Instructor and assignment | Initials versus full names; one name per session or per day |
| Room, “Zoom” | Venue, room or delivery mode | Place and delivery mode typed into one cell |
| Delegate, email, employee ID | Learner | Duplicates, shared inboxes, lost leading zeros |
| Company, PO number | Customer organization | Name variants; one company, many spellings |
| Status: Paid, Cxl, TBC | Enrollment status | Free text; map to a closed list |
| Fee, discount | Price on the enrollment or invoice line | Currency symbols, numbers stored as text |
Two layouts need reshaping first. Calendar grids, with dates across the top, must be unpivoted into one row per session, and merged cells need their value filled down before you split rows.
How do you stop Excel from damaging IDs and dates?
Export the sheet to CSV once, store that file untouched, and read every column as text in your script. Excel converts data when a file is opened, so a casual open-and-save can turn 00123 into 123, shorten long numbers to 15 digits, or read a code such as JAN1 as a date.
Microsoft documents each case. Excel for Microsoft 365 and Excel 2024 drop leading zeros, truncate numbers to 15 significant digits and convert strings such as JAN1 to dates unless the automatic conversion options are off. Those options do not directly affect Power Query imports. Once a converted value is saved, the original digits are gone, so fix the setting before anyone opens the file.
Dates need an explicit rule. In pandas, dayfirst defaults to False and the “mixed” format option is documented as risky, so 03/11/2026 reads as March 11 unless you say otherwise (checked in the sandbox). Code cannot tell which the owner meant, so ask, parse with named formats, and fail loudly if a value breaks the rule.
How do you match the same person across rows and sheets?
Use a lowercase, trimmed email address as the match key, and let a person decide every other case. Identical addresses become one learner. Name-only matches go to review, because fuzzy name matching merges strangers and splits colleagues.
Importers use the same key. Adobe’s guidance for Learning Manager says each email must be unique within the uploaded file and flags a duplicate when the address already exists, which it ties to data arriving from more than one source system. Apply these rules in order:
- Same email, same name: one learner. Keep the most complete spelling.
- Same email, different names: review. It may be a shared inbox.
- Different emails, same name and company: review. One person or two.
- No email: reject to the owner. Without an address a learner cannot be matched later.
Which validation rules should reject a row?
Write each rule as a yes-or-no test with a stated outcome: fix automatically, reject to the data owner, or flag for review. Reject instead of guessing when a wrong guess would put a learner in the wrong session or send an invoice to the wrong company.
| Rule | Example in the sheet | Outcome |
|---|---|---|
| Email present and well formed | Blank, or “mo.haddad@northwind” | Reject to owner |
| Course and trainer found in the alias table | “First Aid” and “First Aid at Work” | Map known names; reject unknown ones |
| Date parses with an agreed format | “3-Nov-2026”, “TBC” | Parse known formats; reject the rest |
| Status is in a closed list | “Cxl”, blank | Map known values; reject blanks |
| Fee is numeric | “$450”, “n/a” | Strip symbols; reject the rest |
| One enrollment per person per session | Paid row and cancelled row for the same person | Drop exact repeats; review conflicts |
Decide the target of every status before you code. If the sheet tracks waitlisted or cancelled rows, check how the waitlist and cancellation rules in your new system work, so those rows land as the right state and not as confirmed seats.
Pro tip: Have the owners sign the lookup tables. The script is the easy part. The course, trainer and status tables encode business meaning, such as whether “TBC” means unconfirmed or cancelled. Have each tab’s owner approve them in writing before the dry load.
What does a training spreadsheet cleaning script look like?
It reads everything as text, normalizes values through lookup tables, tests each row against the rules, and writes two outputs: clean tables and a rejects file with a reason for every row. The example below was run for this post with pandas 3.0.5 on Python 3.13. The data is invented and illustrative.
import io
import sqlite3
import pandas as pd
# Illustrative data only: names, companies and fees are invented.
RAW = """course,start,trainer,delegate,email,employee_id,company,status,fee
Excel Basics,03/11/2026,J. Tan,Priya Nair,Priya.Nair@acme.example ,00123,ACME Pte Ltd,Paid,$450
excel basics,3-Nov-2026,Jay Tan,P. Nair,priya.nair@acme.example,00123,Acme Pte. Ltd.,paid,450.00
Excel Basics,2026-11-03,jay tan,Tom Ellis,tom.ellis@brightco.example,0457,BrightCo,Confirmed,"$450"
Excel Basics,03/11/2026,J. Tan,Lena Fox,,0991,BrightCo,Paid,$450
First Aid,10/11/2026,M. Ortiz,Sam Okoye,sam.okoye@northwind.example,2210,Northwind,Cxl,$180
First Aid at Work,10/11/2026,M. Ortiz,Ana Silva,ana.silva@northwind.example,2214,Northwind,Paid,$180
First Aid,TBC,M. Ortiz,Raj Patel,raj.patel@globex.example,0318,Globex,Paid,$180
Excel Basics,17/11/2026,J. Tan,Tom Ellis,tom.ellis@brightco.example,0457,BrightCo,,$450
Leadership 101,24/11/2026,A. Kim,Dee Wong,dee.wong@globex.example,0322,Globex,Paid,$600
First Aid,10/11/2026,M. Ortiz,Ana Silva,ana.silva@northwind.example,2214,Northwind,Cancelled,$180
First Aid,10/11/2026,M. Ortiz,Mo Haddad,mo.haddad@northwind,2230,Northwind,Paid,$180
"""
COURSES = {"excel basics": "XL-101", "first aid": "FA-201", "first aid at work": "FA-201"}
TRAINERS = {"j. tan": "Jay Tan", "jay tan": "Jay Tan", "m. ortiz": "Maria Ortiz"}
STATUSES = {"paid": "confirmed", "confirmed": "confirmed",
"cxl": "cancelled", "cancelled": "cancelled"}
EMAIL_RE = r"^[^@\s]+@[^@\s]+\.[^@\s]+$"
# 1. Read everything as text so "00123" stays "00123"; keep the sheet row number.
df = pd.read_csv(io.StringIO(RAW), dtype=str, keep_default_na=False)
df = df.apply(lambda col: col.str.strip())
df.insert(0, "sheet_row", df.index + 2) # header is row 1
# 2. Normalize. Dates are day-first by agreement with the sheet owner.
slash = df.start.str.extract(r"^(\d{1,2})/(\d{1,2})/\d{4}$").astype(float)
assert not (slash[1] > 12).any(), "month-first dates found: agree a rule first"
start = pd.to_datetime(df.start, format="%Y-%m-%d", errors="coerce")
for fmt in ("%d/%m/%Y", "%d-%b-%Y"):
start = start.fillna(pd.to_datetime(df.start, format=fmt, errors="coerce"))
df["start"] = start
df["email"] = df.email.str.lower()
df["course_code"] = df.course.str.lower().map(COURSES)
df["trainer"] = df.trainer.str.lower().map(TRAINERS)
df["status"] = df.status.str.lower().map(STATUSES)
df["fee"] = pd.to_numeric(df.fee.str.replace(r"[^\d.]", "", regex=True), errors="coerce")
# 3. Validate: every failed rule is recorded, not just the first.
rules = {
"course not in alias table": df.course_code.isna(),
"trainer not in alias table": df.trainer.isna(),
"start date unreadable": df.start.isna(),
"email missing or malformed": ~df.email.str.match(EMAIL_RE),
"status blank or unmapped": df.status.isna(),
"fee not numeric": df.fee.isna(),
}
failed = pd.DataFrame(rules)
df["reject_reason"] = failed.apply(lambda r: "; ".join(r.index[r]), axis=1)
# 4. Dedupe on the natural key of an enrollment: same person, course and start date.
key = ["email", "course_code", "start"]
ok = df[df.reject_reason == ""]
dupe = ok.duplicated(key, keep="first")
same = ok.status == ok.groupby(key).status.transform("first")
df.loc[ok.index[dupe & same], "reject_reason"] = "duplicate of an earlier row"
df.loc[ok.index[dupe & ~same], "reject_reason"] = "conflicting duplicate: status differs"
rejects = df[df.reject_reason != ""]
clean = df[df.reject_reason == ""].copy()
# 5. Split into entities. One learner per email; one session per course, date, trainer.
learners = (clean.groupby("email", as_index=False)
.agg(name=("delegate", "first"), employee_id=("employee_id", "first"),
company=("company", "first")))
learners.insert(0, "learner_id", range(1, len(learners) + 1))
sessions = clean[["course_code", "start", "trainer"]].drop_duplicates().reset_index(drop=True)
sessions.insert(0, "session_id", range(1, len(sessions) + 1))
enrollments = (clean.merge(learners[["learner_id", "email"]], on="email")
.merge(sessions, on=["course_code", "start", "trainer"])
[["session_id", "learner_id", "status", "fee"]])
# 6. Dry run: load into a scratch database with the constraints switched on.
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE learners (learner_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE,
name TEXT, employee_id TEXT, company TEXT);
CREATE TABLE sessions (session_id INTEGER PRIMARY KEY, course_code TEXT NOT NULL,
start TEXT NOT NULL, trainer TEXT NOT NULL);
CREATE TABLE enrollments (session_id INTEGER NOT NULL REFERENCES sessions,
learner_id INTEGER NOT NULL REFERENCES learners,
status TEXT NOT NULL, fee REAL,
UNIQUE (session_id, learner_id));
""")
learners.to_sql("learners", db, if_exists="append", index=False)
sessions.assign(start=sessions.start.dt.strftime("%Y-%m-%d")).to_sql(
"sessions", db, if_exists="append", index=False)
enrollments.to_sql("enrollments", db, if_exists="append", index=False)
# 7. Reconcile: every input row must be accounted for.
print(f"input rows: {len(df)} | loaded enrollments: {len(enrollments)} | rejected: {len(rejects)}")
assert len(df) == len(enrollments) + len(rejects)
print(db.execute("SELECT COUNT(*) FROM learners").fetchone()[0], "learners,",
db.execute("SELECT COUNT(*) FROM sessions").fetchone()[0], "sessions")
print()
print(learners.to_string(index=False))
print()
print(sessions.to_string(index=False))
print()
print(enrollments.to_string(index=False))
print()
print(rejects[["sheet_row", "delegate", "reject_reason"]].to_string(index=False))
rejects.to_csv("rejects.csv", index=False)
Here is the output from that run:
input rows: 11 | loaded enrollments: 4 | rejected: 7
4 learners, 2 sessions
learner_id email name employee_id company
1 ana.silva@northwind.example Ana Silva 2214 Northwind
2 priya.nair@acme.example Priya Nair 00123 ACME Pte Ltd
3 sam.okoye@northwind.example Sam Okoye 2210 Northwind
4 tom.ellis@brightco.example Tom Ellis 0457 BrightCo
session_id course_code start trainer
1 XL-101 2026-11-03 Jay Tan
2 FA-201 2026-11-10 Maria Ortiz
session_id learner_id status fee
1 2 confirmed 450.0
1 4 confirmed 450.0
2 3 cancelled 180.0
2 1 confirmed 180.0
sheet_row delegate reject_reason
3 P. Nair duplicate of an earlier row
5 Lena Fox email missing or malformed
8 Raj Patel start date unreadable
9 Tom Ellis status blank or unmapped
10 Dee Wong course not in alias table; trainer not in alias table
11 Ana Silva conflicting duplicate: status differs
12 Mo Haddad email missing or malformed
Eleven rows went in. Four enrollments, four learners and two sessions came out, and seven rows went to the rejects file, each with its reason. Row 3 repeats row 2 under a different name spelling; normalizing the email (capitals and a trailing space in row 2) is what made the match possible. Row 10 failed two rules at once, so the owner sees both problems in one pass. Employee ID 00123 kept its leading zeros because the file was read as text.
Row 11 is the one to watch. Ana Silva appears as paid, then cancelled, for the same session. Keeping the first row would silently drop the cancellation, so the script flags the conflict for a person.
This is a teaching sample, not production code. It keeps the first company spelling it sees, has no review queue for name variants, and loads into a stand-in schema, not any vendor’s. Tune the policy too: it rejects domains without a dot.
How do you dry-run the load and cut over without losing data?
Load the cleaned tables into a scratch copy or the vendor’s sandbox with every constraint on, reconcile, fix the rules, and repeat until the rejects stop changing. Then cut over: freeze the sheet, load a final export, reconcile again, and import late changes as a delta.
Vendor guidance shows why order and rehearsal matter. Docebo’s migration guide requires users and courses to exist before enrollments, rejects enrollments for people already enrolled, recommends a sandbox pilot and a delta import for later enrollments, and notes that the sandbox and production are not synced. It also says completed enrollments imported through the interface receive the import date as the completion date, so check how your vendor treats historical dates.
Reconcile in four checks:
- Every input row is either loaded or in the rejects file; the script above asserts this.
- Enrollments per session match the counts the sheet owner expects.
- Confirmed fees per session and per company match the sheet’s own totals.
- A sample of sessions, chosen by a person, looks right in the new system’s screens.
Cutover then follows a fixed sequence:
- Make the sheet read-only at an agreed time, export it, and run the same script.
- Load in dependency order: people and courses, then sessions, then enrollments.
- Reconcile, get owner sign-off, then import changes made during the window as a delta. Keep the frozen sheet as the archive.
Scope the history deliberately: future sessions, open enrollments and what reports or auditors need, with the rest archived. Moving fewer personal records also leaves less to protect; see the privacy duties for training platforms.
Pro tip: Reconcile with money, not just counts. Row counts can match while fees land on the wrong sessions. Compare confirmed revenue per session and per company with the sheet’s own totals, and chase any difference before sign-off.
What should you do next?
Write the one-sentence row meaning for each tab, then run the six rules from the table above on a 200-row sample from your busiest one. Count rejects by reason: that count shows where the real effort sits, and it is owner decisions, not code.
Then ask each vendor on your shortlist for import templates, a sandbox, their rule for duplicate learners, and how historical dates are stored. If you are comparing platforms, SimpliTrain is Mundrisoft’s training management platform for professional training companies, and you can request a demo to put those questions to the team directly.
Frequently asked questions
Can you import a training spreadsheet into a TMS directly?
Usually through the vendor’s import tool, not by uploading the sheet as it is. Docebo’s guide, for example, asks for one CSV file per data type, built from its templates. Ask each vendor for templates and a sandbox, then shape your cleaned tables to match.
How long does spreadsheet data cleanup take?
It depends on the number of distinct problems, not the number of rows. The script runs in seconds; the delay comes from owners resolving rejects and approving lookup tables. Expect more than one dry run, and schedule around people’s answers.
Should you migrate all historical training records?
Usually not. Migrate what you need to operate and report: future sessions, open enrollments and history that certificates or auditors require. Vendors differ here; Docebo’s guide supports up to two historical records per user per course through enrollment imports. Keep the rest in a read-only archive.
What if one person has two email addresses in the sheet?
Do not merge them automatically. Put the pair on a review list, confirm with the learner or their manager which address they use, and load one learner under it. Then ask your vendor whether alternate addresses are supported, so future bookings do not create a second profile.
Do you need Python, or can Excel do the cleanup?
Excel with Power Query can handle most of these steps. A script adds repeatability: you can re-run it on the final export at cutover, and it always writes a rejects file with reasons. Whatever you use, keep the original export untouched and treat identifiers as text.
