Timesheet Data From Excel Into a Database Without Loss

Every week, forty people send an Excel timesheet. Each looks a little different: some have the week across the top and projects down the side, some have a row per day, one has hours typed as "7h30" and another as 7:30. Payroll, billing and project reports all depend on these numbers, and the work of turning them into rows in a table is done by hand by someone who dreads Fridays. This article describes a pipeline that does it without loss: a template that fits the loader, a conversion from the wide layout to long rows, validation that catches the usual mistakes, a load that keeps approvals and a reconciliation that proves nothing was dropped. It applies Excel reports that finance teams rely on to time data and builds on building Excel templates that import cleanly.

The target table

Start with what the database needs.

CREATE TABLE timesheet_entries (
  person_id     text NOT NULL,
  work_date     date NOT NULL,
  project_code  text NOT NULL REFERENCES projects(code),
  activity      text NOT NULL DEFAULT 'work',
  hours         numeric(4,2) NOT NULL CHECK (hours > 0 AND hours <= 24),
  note          text,
  submission_id bigint NOT NULL REFERENCES timesheet_submissions(id),
  PRIMARY KEY (person_id, work_date, project_code, activity)
);

CREATE TABLE timesheet_submissions (
  id           bigserial PRIMARY KEY,
  person_id    text NOT NULL,
  week_start   date NOT NULL,
  file_sha256  text NOT NULL,
  submitted_at timestamptz NOT NULL DEFAULT now(),
  status       text NOT NULL DEFAULT 'submitted' CHECK (status IN ('submitted','approved','rejected')),
  approved_by  text,
  approved_at  timestamptz,
  UNIQUE (person_id, week_start)
);

One entry per person, date, project and activity. Hours are a numeric(4,2): two decimals up to 99.99, with a check for sanity. A submission represents one person's week and carries its status and approver. The constraints do some of the validation for free: a duplicate row or an unknown project code cannot get in.

The template, in two shapes

People like to see a week at a glance, and loaders like rows. Offer the layout that people like, and convert it, or give them the loader's layout and make it easy.

Wide (week across):

with the person and the week start in fixed cells above the table. Convenient to fill. It needs the conversion described below.

Long (row per entry): date, project_code, activity, hours, note. Easy to load, a little slower to type, and more flexible when people work on many projects.

If you use the wide layout, make the person ID and the week start cells locked and pre-filled (generate one file per person per week) so that identity cannot be mistyped. Name the weekday columns by date (2026-10-26, ...) in the generated header so that the loader reads dates and not day names.

Hours are numbers of hours

The most common timesheet error is a time of day in an hours column. Excel treats 7:30 typed in a General cell as a time, which is the fraction 0.3125 of a day. The cell may display "7:30" and the loader reads 0.3125. A cell formatted as a number with the value 7.5 means seven and a half hours. They look alike and differ by a factor of 24.

Decide the unit and enforce it. For decimal hours: format the column 0.00, validate as a decimal between 0 and 24, and tell people "7.5 means 7 hours 30 minutes". In the loader, reject any value that is a datetime.time or a datetime.timedelta object, or a value less than 1 that looks like a fraction of a day when other values are whole. Alternatively accept durations and convert explicitly: a timedelta of 7 hours 30 minutes becomes 7.5, and say so. Do not let both through unannounced.

Mixed entry styles can be repaired for known patterns: "7h30" and "7:30" to 7.5, but only with a documented rule and a warning in the report. A strict loader that rejects "7h30" with the fix ("enter 7.5") teaches people faster than a clever one that guesses.

Convert wide to long

With pandas, melt the weekday columns:

import pandas as pd

raw = pd.read_excel("timesheet_alvarez_2026-10-26.xlsx", sheet_name="Data", header=0)
id_cols = ["project", "activity"]
day_cols = [c for c in raw.columns if c not in id_cols]
long = raw.melt(id_vars=id_cols, value_vars=day_cols, var_name="work_date", value_name="hours")
long["work_date"] = pd.to_datetime(long["work_date"]).dt.date
long = long[long["hours"].fillna(0) > 0]

melt turns each weekday cell into a row; rows with zero or blank hours are dropped. The header cells must be real dates (or ISO strings) for to_datetime to work. Add the person and week from the fixed cells. Check that every date falls inside the declared week; a date outside means the sender used last week's file.

The conversion loses nothing when the sums are checked: the sum of the long table's hours must equal the sum of the wide table's cells, row by row and in total.

Validate before loading

Checks that catch the usual problems:

Identity. The person in the file exists and matches the sender (when the upload is authenticated), and the week start is a Monday (or the policy's first day).

Codes. Each project code exists in projects and is open for the week; each activity is on the allowed list. Report unknown codes by row.

Hours. Each value is a number greater than zero and at most 24; the total per day is at most a limit you choose (say 16) with a warning above 12; the weekly total is within a plausible range for the person's contract.

Duplicates. The same project and activity on the same date twice in the file: reject, or sum with a warning, per policy.

Dates. Within the week; not in the future beyond the allowed submission window; not on a locked period.

Completeness. If the policy requires hours for each working day, flag weekdays with none and no leave entry.

errors = []
for i, r in long.iterrows():
    if r["project"] not in valid_projects:
        errors.append((i + 2, "project", f"unknown project {r['project']}"))
    if not (0 < float(r["hours"]) <= 24):
        errors.append((i + 2, "hours", f"{r['hours']} is outside 0 to 24"))
daily = long.groupby("work_date")["hours"].sum()
errors += [(None, "hours", f"{d}: {h} hours in one day") for d, h in daily.items() if h > 16]

Return all errors in one report with the sheet row numbers (checking an incoming workbook against a template by code shows a reusable pattern).

Load in one transaction

Re-submissions are normal: someone corrects Wednesday and sends again. The load must replace the earlier version of that person's week, atomically.

import psycopg

with psycopg.connect("dbname=hr") as conn, conn.cursor() as cur:
    cur.execute("""
      INSERT INTO timesheet_submissions (person_id, week_start, file_sha256)
      VALUES (%s, %s, %s)
      ON CONFLICT (person_id, week_start)
      DO UPDATE SET file_sha256 = EXCLUDED.file_sha256, submitted_at = now(), status = 'submitted',
                    approved_by = NULL, approved_at = NULL
      RETURNING id""", (person, week_start, sha))
    sid = cur.fetchone()[0]
    cur.execute("DELETE FROM timesheet_entries WHERE submission_id = %s", (sid,))
    with cur.copy("COPY timesheet_entries (person_id, work_date, project_code, activity, hours, note, submission_id) FROM STDIN") as cp:
        for r in long.itertuples():
            cp.write_row([person, r.work_date, r.project, r.activity, r.hours, None, sid])

The upsert resets the approval on a changed submission, so a corrected week must be approved again. The delete and the copy happen in the same transaction, so a failure leaves the previous version intact. Refuse the replacement if the period is locked for payroll or the submission is already approved and paid, and route it to a correction process instead of overwriting history.

Never load part of a file silently. If validation fails, nothing loads and the sender gets the report.

Approvals

Approvals are the point of the exercise for billing and payroll. Keep them in the database: the approver, the time, and the status. The manager sees the week as a small report (hours by project and day, total, flags) and approves through a form or a query-driven page; an Excel sign-off column loaded back is weaker, because it can be edited. An approved week is frozen, and a change creates an audit record. Reports for billing read only approved entries.

Reconcile after loading

Prove the load. Compare, per person and in total, the hours in the file with the hours in the table for that submission.

SELECT person_id, sum(hours) AS db_hours
FROM timesheet_entries WHERE submission_id = 4821 GROUP BY person_id;

The number must equal the sum in the source file, which the loader recorded before it began. Record both in the submission log. A weekly report that lists people who did not submit, people whose hours are far from their contract and submissions with warnings makes the process visible to the managers who need to act (validating row counts after an Excel import).

Time zones, leave and special cases

Date-only entries have no time zone, which suits timesheets: the date is the date the person worked, in their location. Be careful with night shifts that cross midnight; decide whether hours belong to the start date or are split. Leave and public holidays are better as entries of their own activity types than as blank cells, since a blank is ambiguous. Part-time and overtime rules belong in the payroll rules downstream, not in the loader; the loader's job is faithful capture.

When timesheets in Excel should be replaced

Excel timesheets are workable for small teams and short periods. They stop being so when people work across many projects with changing codes, when approvals need a workflow, when late changes have to be tracked, or when the data drives billing that customers audit. A purpose-built timekeeping tool or a simple web form over the same tables does the same job with live validation and a history. The pipeline in this article is also what you need to migrate: the same table, loaded by form instead of by file. See importing Excel into PostgreSQL step by step for a general migration, and the PostgreSQL manual on INSERT ... ON CONFLICT and COPY for the load statements used above.