Payroll CSV: Columns, Template, and Import Tips

Column Guide

Use ISO dates (YYYY‑MM‑DD), 24‑hour times, and include unpaid break minutes.

Template

Common columns: date, shift_index, clock_in, clock_out, unpaid_break_minutes, overnight, day_total_hours.

Import Tips

Save as UTF‑8, confirm delimiter, and validate sample rows before a full upload.

Validation

Check totals against the calculator’s summary for the same period.

Reference CSV Schema

date,shift_index,clock_in,clock_out,overnight,unpaid_break_minutes,day_total_hours,notes
2025-01-02,1,08:00,12:00,false,15,3.75,"front counter"
2025-01-02,2,16:00,20:00,false,0,4.00,"stock"
        

Validation Steps

1. Import one week for one employee as a test.
2. Compare totals to the calculator summary.
3. Resolve discrepancies before bulk import.

CSV Validation Rules (Quick Checks)

• Dates: strict YYYY‑MM‑DD; reject other formats.
• Times: HH:MM 24‑hour; ensure clock_out logically follows clock_in (with overnight flag allowed).
• Breaks: non‑negative integers only.

Rollback Plan

Always run a small pilot import first. If totals mismatch, revert and correct mapping before importing for all employees.

Deeper Guide: Designing a CSV That Imports Every Time

Payroll tools are picky: they expect consistent headers, strict date/time formats, and clean delimiters. Standardize on ISO dates (YYYY‑MM‑DD) and 24‑hour times so imports don’t fail silently. Keep the overnight flag explicit, even when a shift clearly crosses midnight—this removes ambiguity during validation.

Include a free‑text notes column that carries context like job codes, sites, or “night diff”. Even if your payroll tool ignores it, that context is invaluable when you audit months later.

Import Tips (Expanded)

• Delimiter sanity: Open the CSV in a plain text editor to verify commas or semicolons. Some locales default to semicolons, which confuses US‑centric payroll systems.
• Encoding check: Save as UTF‑8 without BOM so special characters in names don’t appear as question marks.
• Pilot first: Import a single employee for one week and validate totals before any bulk action.
• Mapping screenshot: Take a screenshot of your column mapping the first time—it becomes your playbook when you repeat the process.

Validation (Expanded)

After import, compare the payroll system’s computed totals with the calculator’s weekly summary for the same period. Mismatches usually trace back to break minutes omitted, an overnight flag missing, or a time formatted in AM/PM instead of 24‑hour. Fix the row at the source, re‑export, and re‑import; avoid editing inside the payroll UI because those edits are hard to reproduce next time.

Step‑by‑Step: Bulletproof CSV Creation

1. Export data and ensure columns match your mapping exactly.
2. Scan for empty cells in required fields (date, in, out, breaks).
3. Confirm 24‑hour times and ISO dates; fix anomalies immediately.
4. Run a pilot import for one person; compare totals to the calculator.
5. Archive the pilot and mapping screenshot before the full import.

Consistency across periods matters more than the “perfect” structure. Small, repeatable steps prevent most payroll headaches.

FAQ (New)

Why does my import pass but totals are wrong?

Mapping mismatches (e.g., break minutes vs hours) cause silent errors. Validate a single known week by hand.

Do I need the overnight flag if times clearly cross midnight?

Yes—some systems require it to allocate hours correctly. Keep the flag explicit.

Case Study: Import Fails on Hidden Characters

A CSV exported from a spreadsheet contained non‑breaking spaces in the header row. Payroll rejected the file without a clear error. Fix by opening the raw CSV in a text editor, removing hidden characters, and ensuring headers exactly match the expected names. Keep a canonical template file that you copy for each pay period to avoid drifting headers.

Advanced Tip: Add a “Version” Column

Include a simple version tag (e.g., v3) in a non‑imported column. When two files look similar, you can identify the latest logic instantly.

Operational Playbook: CSV Creation to Import

1. Start from a known‑good template with locked headers.
2. Paste data as values to avoid stray formulas.
3. Validate ISO dates and 24‑hour times with a sheet rule.
4. Run a pilot import; attach mapping screenshot to the archive.
5. Archive both the pilot and final CSV with version tags.

Email Template: Requesting Mapping Confirmation

Subject: CSV mapping confirmation for {{PERIOD}}
Hi Payroll,
Attached is our CSV and the mapping screenshot. Please confirm the fields, especially unpaid_break_minutes and overnight. We’ll proceed with the full import after your OK.
Thanks,
{{NAME}}
    

Data Hygiene: Make Bad Imports Impossible

• Lock header names and forbid extra spaces with sheet validation.
• Coerce all times to HH:MM; reject AM/PM with a rule.
• Force non‑negative integers in break minutes; disallow blanks.

These guardrails prevent 90% of mapping errors before they reach payroll.

Mini‑Template: Mapping Notes Block

Mapping v4 (2025‑Q4):
date → Work Date (YYYY‑MM‑DD)
clock_in → Start (HH:MM)
clock_out → End (HH:MM)
unpaid_break_minutes → Unpaid (m)
overnight → Overnight (true/false)