How to build a pay file from your payroll system
The P50 team
Published
To build a pay file, export an employee list from your payroll or HRIS system with three columns: job title, base pay, and work location. Then strip out anything personal, clean up a few common problems, and save it as a spreadsheet. For most companies it takes an afternoon. Below we walk through each step, with a checklist at the end.
What a pay file is
A pay file is a spreadsheet that lists what you pay, one row at a time. Most surveys want one row per employee. A few want one row per job with a headcount, but the per-employee version is simpler to build and works almost everywhere, so start there.
The survey takes your file, matches each row to a job in its catalog, combines it with everyone else's, and reports market rates back (see what a salary survey is). Your file does not need to be pretty. It needs to be accurate and complete.
The three fields every survey needs
If your file has these three columns, you can join almost any survey.
Job title. The title as it appears in your system. Do not translate it to the survey's catalog, and do not clean it up to sound more impressive. "Accounting Clerk II" is better than "Finance Associate" because it tells the survey what the person actually does.
Base pay. Either an annual salary or an hourly rate, but be clear which one. Base pay means the regular wage before bonus, overtime, commission, or shift differential. If your system stores a pay rate and a pay basis (annual, hourly, weekly), export both columns.
Work location. City and state, or ZIP code. This should be where the person works, not where they live and not where your headquarters is. If someone is fully remote, use the city and state they work from. Pay varies a lot by location, so a blank or wrong location is the fastest way to make a row useless.
The helpful extras
These fields are not required, but each one makes your results more useful.
| Field | Why it helps |
|---|---|
| Employee ID | Lets you trace a row back to a person without putting a name in the file |
| Bonus or other cash pay | Lets the survey report total cash, not just base |
| Full time or part time | Keeps part-time rates from being read as full-time |
| Hire date | Helps the survey see tenure and spot new hires |
| Department | Helps match jobs with vague titles ("Coordinator") |
| Exempt or non-exempt | Tells the survey whether the job is salaried or hourly by law |
If these are already in your system, include them. If not, do not build them by hand. The three required fields matter far more.
What to strip out before the file goes anywhere
Payroll exports often include everything. Before you send a file to any survey (or to anyone outside your company), remove these columns:
- Employee names (first, last, preferred)
- Social Security numbers or any tax ID
- Bank account or routing numbers
- Home addresses
- Dates of birth
- Any field you would not want on a printed page left in a conference room
Use an employee ID instead of a name if you want to look rows up later. If you deleted columns in a spreadsheet, check for hidden columns and extra sheets before you save. We cover what P50 does with your file on our data page.
How to export from your payroll or HRIS system
Most payroll and HRIS systems have a built-in report that covers this. Look for a report called something like "employee census," "compensation report," "employee roster," or "pay rate report." Those names vary by vendor, but the idea is the same: one row per employee, with job, pay, and location, exported to a spreadsheet or CSV.
A few tips that apply across systems:
- Filter for active employees only before you export, if the report allows it.
- Choose the columns you want at export time rather than exporting everything and deleting later.
- Export the pay rate and the pay basis as separate columns if you can.
- If your location field is a "work site" or "office" code, export the lookup table too so you can turn codes into city and state.
If you cannot find the report, your payroll provider's help center almost always has a page on exporting employee data. Search their help site for "census" or "export employees."
Common cleanup steps
Open the export in a spreadsheet and work through these.
One row per person. Some exports create a row for every pay line, so a person with a base rate and a shift differential shows up twice. Keep one row per employee with their base pay only.
Consistent pay basis. Make sure every row is either annual or hourly, and that you have a column that says which. Mixing a salary of 65,000 and an hourly rate of 31.25 in the same column with no label will confuse any survey.
Hourly to annual. If a survey wants annual pay for everyone, multiply hourly rates by 2,080 hours. That is 40 hours a week for 52 weeks. It is the same convention the Bureau of Labor Statistics uses in its wage estimates: the OEWS technical notes say annual rates are "calculated by multiplying the hourly wage rate by a typical work year of 2,080 hours" (BLS OEWS technical notes). Keep the original hourly rate in a separate column so nothing is lost.
Remove terminated employees. Exports often include people who left this year. Delete those rows, or filter them out before export.
Fix blank locations. Sort by location and look at the blanks. Usually a handful of people have no work site assigned. Fill them in from your records. If you truly cannot place someone, leave the row out rather than guessing.
Check for obvious errors. Sort by pay and look at the top and bottom. A salary of 4,500 is probably a monthly figure. An hourly rate of 85,000 is probably an annual salary in the wrong column.
A short checklist
| Step | Done? |
|---|---|
| Exported active employees from payroll or HRIS | |
| File has job title, base pay, and work location | |
| Pay basis (annual or hourly) is clear for every row | |
| Hourly rates converted to annual with 2,080 hours, if needed | |
| Names, SSNs, bank details, home addresses, and birth dates removed | |
| Terminated employees removed | |
| Blank locations filled in or rows removed | |
| One row per employee, no duplicates | |
| Hidden columns and extra sheets checked | |
| Saved as .xlsx or .csv |
Where P50 fits
P50 accepts any layout as long as the three required fields are there, so you do not need to reshape your export to match a template. Names are dropped the moment your file arrives, and our AI matches your job titles to survey jobs, so there is no catalog to match against by hand (see how we calculate). If you already built a file for another survey this year, send us the same file. Our data collection runs March 1 to May 3, 2027, so a file built in February is ready to go, and our schedule article covers how that lines up with other surveys.
P50 is a free salary survey for employers. Registration for the 2027 survey is open now, and data collection runs March 1 to May 3, 2027. Register for free.