CSV Export Mistakes That Break a Payroll Import

You export the hours, open the file in your payroll tool, hit import, and it throws 40 errors. Rows skipped. A column mapped to the wrong field. Now you are cleaning a spreadsheet by hand at 9pm on payroll day. The failures are not random. They come from a short list of predictable problems in how the file is built, and most of them trace back to an export that changes shape from one run to the next.

Here are the mistakes that break imports, ordered from the ones you will hit most often to the rarer ones, with what each looks like and how to stop it.

The column order or column names change between exports

This is the most common and the most damaging. In week one your export has worker, date, hours. In week three someone added a tag, so now there is an extra column in the middle, and your saved import mapping points hours at the wrong field. Payroll reads tag text as an hours value, or drops the row entirely.

Why it hurts: payroll imports match by position or by exact header name. Either one breaks the moment the file shifts. You will not always get an error. Sometimes it imports garbage silently, which is worse.

The fix is a file whose columns never move and never get renamed. A tool built around a stable CSV export column schema gives you the same headers in the same order every single pay period, so a mapping you set up once keeps working. New tags and groups show up inside existing columns, not as new ones that reorder everything.

Time stored in one zone, read in another

A shift that starts at 11:30pm looks fine on your screen but imports as the next day, or an hour off, because the export wrote a local time with no zone and payroll assumed UTC (or the reverse). Now a Sunday-night shift lands in the wrong week and your weekly totals are wrong by a full shift.

Why it hurts: off-by-one-day errors are hard to spot. The total looks plausible. You only catch it when a worker says their hours are short.

The fix: times stored in UTC and shown to each viewer in their own local time, so a recorded clock-in is unambiguous. When the underlying record is one canonical time, the export can present it consistently instead of guessing.

Mixing decimal hours and HH:MM in the same column

One row says 7.5, the next says 7:30. Both mean seven and a half hours. Payroll expects one format and chokes on the other, or reads 7:30 as 7 hours and 30 hours-worth of nonsense.

Why it hurts: the file looks readable to you, so you trust it. The import silently rounds or rejects half your rows.

The fix: pick the format your payroll wants and keep the whole column in it. If you need to switch, use a tool that converts between decimal and HH:MM before import instead of hand-editing cells. Most systems want decimal; check yours first.

Forgotten clock-outs left as blank or open rows

Someone forgot to clock out Friday. The export shows a start time and an empty end time, or a duration that ran until you pulled the report at noon Monday, giving one person 64 logged hours. Payroll either rejects the blank cell or pays the inflated number.

Why it hurts: a single missed clock-out can add dozens of phantom hours. If you do not catch it, you overpay. If you do catch it, you are guessing when the person actually left.

The fix: a rule that closes a stale entry at its start time plus a cutoff, not at the current moment. That is what auto-close does for forgotten clock-outs: it caps the entry instead of letting it grow, so the file never ships a 64-hour shift. You still review flagged entries, but you never import runaway totals.

Extra header rows, totals rows, and merged cells

The file opens with a title row, a blank line, then the headers, and a "Total" row glued to the bottom. Payroll expects row one to be headers and every following row to be data. The title row becomes a fake worker. The totals row imports as an employee named "Total" with 312 hours.

Why it hurts: these rows look helpful in a spreadsheet and destructive in an import. You have to strip them by hand every time.

The fix: export a flat file, one header row and one row per entry, no decoration. Keep summaries in a separate view. If you want a human-readable summary for a funder or a manager, generate it separately with a monthly hours summary rather than baking totals into the payroll file.

Ambiguous or duplicated worker names with no clean count

Two people named Chris, no last name, no ID. Payroll merges their hours or splits one person's hours across two records. Or you simply cannot tell how many distinct people worked an event, which grant reports often require.

Why it hurts: merged workers get paid wrong. A missing head count means you refill the grant form by counting rows by hand.

The fix: consistent worker identifiers and an export that includes a distinct-worker count, so the file answers "how many people" without you tallying. Worker groups let you filter by program or role before you export, which keeps the Chris problem from ever landing in one file.

What a clean file looks like before you import

Run through this quickly on every export and most import failures disappear:

• Headers in row one, identical to last period, in the same order.
• One row per entry, no title rows, no totals row, no merged cells.
• One time format in the hours column, matching what payroll wants.
• No blank end times and no shift longer than a real day.
• Every worker uniquely identifiable, plus a distinct-worker count.

If you are still assembling that file by hand from sign-in sheets, the checklist is a lot of work every period. A workspace that produces the same clean file each time removes most of the steps. You can start a free workspace and pull a real export before your next pay run to see whether it imports without edits.