CSV Payroll Import Guide: Hours From Time Clock to Payroll

A payroll CSV import works when the file matches what the receiving system expects: the right columns, in the right order, with clean numbers and the right employee identifiers. Get those four things right and hours move from your time clock into payroll in minutes. Get one wrong and the import fails, or worse, succeeds with bad numbers. This guide walks through preparing the file, mapping columns, importing, fixing errors and checking the result before anyone gets paid.

TL;DR

• Every payroll system defines its own CSV layout. Download its template or read its spec first, then build your file to match, never the other way around.
• Keep one row per employee per pay period (or per day, if the system asks), use plain numbers for hours and wages, and save as CSV (comma delimited).
• Map columns by matching each header in your file to a payroll field, and match employees by a stable ID, not by name.
• Check overtime by workweek, not by pay period total, before you approve the run.
• Payroll files hold sensitive data. Encrypt them, back them up and delete stray copies.

Table of Contents

• What a payroll CSV import actually does
• Step 1: Prepare the file
• Step 2: Map columns to payroll fields
• Step 3: Run the import
• Step 4: Fix common CSV import errors
• Step 5: Verify before you run payroll
• Our perspective: fix the data at the source
• Run the export from a free time clock
• Protect payroll files and keep the records
• Quick reference: import day routine
• FAQ
• Sources

What a payroll CSV import actually does

A CSV (comma-separated values) file is a plain text table. Each line is a row, and commas separate the columns. Payroll software, tax-agency portals and accounting tools all accept CSV because almost every system can export one, including your time clock.

The import itself does only three things: it reads each row, matches the row to an employee record, and drops the values into pay fields such as regular hours, overtime hours or tips. It does not decide whether those hours are right. That is why the work before and after the upload matters more than the upload itself.

Two kinds of CSV imports get confused:

• Hours imports bring time data from a time clock into a payroll run. This is the one most small employers need every pay period.
• Wage-file or employee-profile imports load other data, such as employee records at setup or quarterly wage reports to a state agency.

The steps below apply to both, but the examples lean on hours, because that is where small businesses lose the most time and make the most mistakes.

Step 1: Prepare the file

Start from the destination, not the source. Open the payroll system’s import screen and look for a downloadable template or a written file specification. Use it as your blueprint.

The strictness varies a lot. The Washington Employment Security Department, for example, requires wage files with eight columns in a fixed order, with no headings, totals, notes or extra columns. Other systems want a header row and let you reorder columns when you map them. Neither approach is wrong, but you cannot guess which one you are dealing with.

Once you know the layout, work through this checklist:

1. Export from your time clock for exactly one pay period. Overlapping dates are the most common cause of double payment.
2. Remove everything the system did not ask for. Title rows, blank lines, subtotal rows, a grand total at the bottom and notes in the margin all break strict imports.
3. Use one row per employee per pay period unless the template asks for daily or per-job rows.
4. Keep numbers plain. Write 42.5, not “42 hrs 30 min”. Skip currency symbols and thousands separators in wage columns, because a comma inside a number splits it into two columns.
5. Check rules for the numeric fields. In the Washington file, hours must be positive whole numbers and wages must be positive with up to two decimal places. Your system will have its own rules, so read them.
6. Save as CSV (comma delimited), not as a workbook. In Excel and Google Sheets, that is a separate save or download option.

Watch for spreadsheet side effects

Opening a CSV in a spreadsheet and saving it again can quietly damage it. Leading zeros disappear from IDs and Social Security numbers, long numbers turn into scientific notation, and dates flip between month-first and day-first formats. If you must edit in a spreadsheet, format ID columns as text before you paste anything in, and reopen the saved file in a plain text editor to confirm it looks the way you expect.

Step 2: Map columns to payroll fields

Mapping is the step where you tell the payroll system what each column means. Some systems skip it because they demand an exact template. Others show you a preview and ask you to assign each column to a field using a dropdown.

Here is a typical hours import and how each column is usually matched:

Three rules keep mapping painless:

• Match employees by a stable ID. Names change and repeat. An employee number or payroll ID does not.
• Decide where overtime is calculated. Either your time clock sends regular and overtime hours separately and payroll pays them as given, or it sends total hours and payroll computes overtime. Doing both, or neither, is how overtime goes missing or gets paid twice.
• Save the mapping if the system lets you. Reusing it next period removes the most error-prone step.

If your payroll system has no mapping screen, you do the mapping yourself by renaming and reordering the columns in your export until they match the template.

Step 3: Run the import

The sequence is nearly the same everywhere:

1. Open the import tool inside the pay run (or the employee or wage-report section, depending on what you are loading).
2. Choose the file. Confirm the system shows the number of rows you expect.
3. Review the preview. Spot-check three or four employees against your time clock, including one with overtime and one who worked at two locations or roles.
4. Confirm column mapping if prompted.
5. Import, then read the confirmation or error message in full.
6. Save the original file and the system’s confirmation together, labeled with the pay period.

Do the first import of a new setup on a test employee or a single department if you can. A small trial reveals format problems when they are cheap to fix.

Step 4: Fix common CSV import errors

Most systems validate data on import, and the practical advice is the same: read the message, correct the file and upload again. Washington’s guidance says the same thing for its wage-file system: errors are caught on validation, so you fix the import file and try again. Do not patch the data inside the payroll system and leave the source file wrong, or the same error returns next period.

If the file imports without errors, you are still not done. A clean import means the file was readable, not that it was right.

Step 5: Verify before you run payroll

Compare the imported totals with the source. Total hours in the payroll system should equal total hours in the time clock report for the same period. If they do not, find the gap before you do anything else.

Then check the things a total cannot reveal.

Test overtime by workweek

For employees covered by the Fair Labor Standards Act, overtime is judged week by week. The Department of Labor defines a workweek as 168 hours, seven consecutive 24-hour periods, and says overtime generally cannot be averaged across two or more workweeks. That matters for biweekly payroll: 40 hours one week and 50 the next is not 90 hours “over” a pay period of 80. It is 10 overtime hours in the second week. Check that your file carries enough detail, such as weekly or daily rows, for payroll to apply the rule. Our guides to how overtime is calculated after 40 hours and to how many hours make a work week cover the arithmetic. State rules, such as daily overtime in California, add another layer.

Check the records the law expects

The Department of Labor’s guidance lists what employers keep for covered nonexempt workers, and it doubles as a verification checklist for any import:

• Start of the workweek
• Hours worked each day and each week
• Straight-time earnings
• Regular rate in weeks with overtime
• Overtime pay
• Additions to and deductions from wages
• Total wages per pay period
• Date of payment and the pay period covered

Source: U.S. Department of Labor, Handy Reference Guide to the FLSA. If your import cannot produce one of these items, fix that before relying on it.

A 10-minute pre-approval pass

• Sort by hours, highest first. Anyone far above the norm deserves a look at their punches.
• Look for zeros: an employee who worked but shows no hours has probably failed to match.
• Confirm new hires and terminations are reflected.
• Scan for rounding. Rounding punches in the employer’s favor creates wage claims, and time rounding has its own FLSA rules.
• Confirm exempt and salaried staff are not accidentally earning hourly pay. If you are unsure who qualifies, see whether salaried workers can get overtime.

Our perspective: fix the data at the source

Most CSV import trouble is not an import problem. It is a punch problem that shows up at import time: a forgotten clock-out, a break that was never recorded, a manager who edited a time entry by hand. Fixing it in a spreadsheet the night before payday is the slowest and riskiest way to deal with it.

We built Kloqk around the opposite idea. Hours should leave the time clock already close to payroll-ready: overtime and break calculations applied, missing punches flagged for a manager, and the export written the same way every period. Photo verification and GPS geofencing help make sure the punch itself is genuine, which is the one thing no import checker can test. And because the core time tracking, overtime and break calculations, and payroll exports are free, small employers do not need a paid plan to stop retyping hours.

Our advice for any employer, whatever software you use: review exceptions weekly, not at period end. A manager who clears five odd punches on Friday has a much easier Monday than one who faces fifty on payday.

Saad

Run the export from a free time clock

If you currently copy hours from paper sheets or a spreadsheet into payroll, an automated export removes most of the steps above. Kloqk’s employee time tracking gives you mobile and kiosk punching, team dashboards and payroll-ready timesheets on the free plan, with unlimited employees. If you later want scheduling, PTO or more advanced tools, the Pro plan is $29 per month per location and Premium is $59 per month per location, as listed on the pricing page. Upgrades are optional, and the free plan keeps working.

Protect payroll files and keep the records

A payroll CSV usually contains names, wages and often Social Security numbers, and it tends to be left in a Downloads folder. The Cybersecurity and Infrastructure Security Agency lists employee and payroll records as sensitive data and recommends encrypting it at rest and in transit, along with encrypted backups and staff training. Practical habits that follow:

• Share files through your payroll provider or an encrypted channel, not ordinary email attachments.
• Delete working copies once the run is confirmed. Keep one archived original.
• Follow CISA’s backup guidance: three copies of important files, on two types of media, with one copy offsite.

On retention, the IRS says employment tax records should be kept for at least four years after the fourth quarter of the year in which the tax is filed. That includes wage payment amounts and dates, employee identifying information, Forms W-4 and tax deposits. Your archived CSV and its confirmation belong in that file. Some states and some job types, such as prevailing wage work, have their own rules, so confirm with your state labor agency and your accountant.

Quick reference: import day routine

1. Clear exceptions in the time clock and lock the pay period.
2. Export one period, and only that period.
3. Compare the file with the payroll template, column by column.
4. Save as CSV (comma delimited) and reopen it in a text editor.
5. Import, fix errors in the source file and re-upload.
6. Reconcile total hours, then check overtime by workweek.
7. Approve, archive the original and delete extra copies.

FAQ

Should my payroll CSV include a header row?

It depends on the destination. Many payroll tools expect a header row so they can map columns, while strict agency-style uploads, such as Washington’s wage file, want no labels at all. Download the system’s template and copy its structure.

Why did my import work but the pay look wrong?

A successful import only proves the file was readable. Common causes of wrong pay are the wrong match key, overlapping dates, breaks subtracted twice and overtime calculated on the pay period total instead of each workweek. Reconcile total hours with the time clock, then test a few employees by hand.

Can I import hours into payroll if my time clock does not export my payroll system’s exact format?

Usually yes. Export what you can, then rename and reorder columns to match the template. Use a spreadsheet carefully, with ID columns formatted as text, and save as CSV (comma delimited). A time clock with a payroll export feature saves you this cleanup every period.

How long should I keep payroll import files?

The IRS says employment tax records should be kept at least four years after the fourth quarter of the filing year. Keep the original file and the confirmation, and check your state’s requirements, which can be longer.

Sources

• Importing wage files in the Employer Account Management System, Washington Employment Security Department
• Handy Reference Guide to the Fair Labor Standards Act, U.S. Department of Labor
• Employment tax recordkeeping, Internal Revenue Service
• Encrypt Business Data, CISA
• Data Backup Options, CISA

Recommended

• Paychex Time Clock: Hours Ready for Paychex Flex
• Paychex Time and Attendance: Options and Setup Guide
• Daylight Saving Time Payroll: Spring Forward & Fall Back
• How Payroll Exports Work: A Guide for HR Professionals