Cleaning and importing the employee master

01

Build one source file from many

Start by pulling every file that holds employee data into one working file, with a column recording where each value came from. Payroll sheets, the joining tracker, the bank details file, the statutory register. Do not clean anything yet; just assemble.

Then pick a key. An employee code is best if one exists and is unique; if not, assign one now, before the import, so every later file can refer to it. Names are not keys: two people can share one, and one person can be spelt three ways.

02

Resolve duplicates and conflicts

Sort by name, date of birth and PAN and look for the same person appearing twice. Rehires, transfers between entities and people who changed their name after marriage are the usual causes. Decide whether a rehire is one record with two periods of service or two records, and apply the decision consistently, because gratuity and leave both count from the joining date.

Where two files disagree on the same field, record which source wins for that field and why. Bank account from the latest payroll file, joining date from the appointment letter, and so on. That rule set is worth keeping: it answers the questions that come up after go-live.

Watch for itJoining dates are the field most worth checking against a document. A wrong one does not show up for years, until somebody's gratuity or long-service eligibility is calculated from it.

03

Standardise the codes every report depends on

Departments, designations, locations, grades and cost centres are usually typed freely in spreadsheets, which is how one department ends up with five spellings. Build a master list for each, map every existing value to it, and load the masters before the employees.

This step decides whether your reports are trustworthy. A headcount by department is only as good as the department field, and fixing it after import means editing every record.

04

Check the fields payroll will rely on

Some fields can be corrected later without consequence. Others feed statutory filings and bank transfers, and errors in them cost money or create notices. Check these field by field, against a document where possible.

• PAN, in the right format and matching the name on record
• UAN for employees covered by EPF, and ESI number where applicable
• Bank account number and IFSC, ideally confirmed against a cancelled cheque or bank letter
• Date of birth and date of joining, in one date format throughout
• Work location and state, which decide professional tax and the holiday calendar
• Reporting manager, as an employee code rather than a name

05

Load in order, and test with a small batch

Load the masters first: entities, locations, departments, designations, grades. Then a small batch of employees chosen to include the awkward cases. Check them on screen, check that the reporting lines resolve, and check that a sample payslip computes before loading the rest.

Reporting managers create a dependency: a manager must exist before an employee can report to them. Either load in hierarchy order or load everyone first and the reporting lines in a second pass.

06

Reconcile after the load

After the full load, count. Active headcount by entity, location and department in the new system should match the cleaned source file exactly. Total monthly gross by entity should match too. A difference means a row failed or loaded twice.

Keep the final import file, the source-precedence rules and the reconciliation as a dated record. They are the evidence of what you loaded and why, and they make the next import, an acquisition or a new entity, much faster.