Export Fixer

A report is not a table

Most HR systems export reports: a title and a run date above the header, the header again at the top of every printed page, a subtotal under each department, a grand total and “Page 3 of 3” at the bottom. Put that into a pivot table and every one of those lines is a person, and the grand total counts the whole company a second time. The real header is found, and the rest of the report’s furniture comes out, each line listed with the reason.

What a spreadsheet does to an export

Opening a CSV in Excel and saving it again is enough to damage it. Employee ID 004521 becomes 4521, and ZIP code 02134 becomes 2134. Dates become numbers such as 45123. An accented name saved in one encoding and opened in another turns into “José”. Each of those can be put back: the zeros where the file itself shows what length the codes should be, the dates in the format the rest of the column uses, and the accents only when the damaged text decodes cleanly, so a real accent is never touched.

One kind of damage cannot be undone. A long number such as a twelve-digit payroll ID becomes 1.20046E+11, and the digits after the sixth are gone from the file. Those are flagged, never guessed, with the lines to fix in a fresh export.

Your call, not the tool’s

A department printed once and left blank under it usually means “same as above”, and filling it down is offered, column by column. Codes of different lengths might have lost their zeros or might be meant that way, so padding them is offered too. Neither is done until you choose it.

What this does not do

It fixes the file’s shape and the damage done to it. Tidying spacing, capitals and date formats is the Employee Data Cleaner’s job, and checking a file against what another system will accept is the HRIS Import Validator’s.