163 columns. How to turn an employee census into a carrier’s enrollment file without retyping it.

Why does a census file take so long?

A census file takes so long because the data you need is already somewhere, just in the wrong shape. Our process started with an Excel workbook. We had to sift through it to find the three sheets that held all the data we needed. Then we filled out one spreadsheet with about 11 columns. Then we turned around and filled out a spreadsheet with 163 columns.

The source doesn’t help. The export we worked from repeated a person’s row for every health condition they listed. Dates came through as Excel serial numbers. States were spelled out when the carrier wanted two letters.

What do most agencies try first?

Most agencies try asking the client to fill out the carrier’s census template. That works until the client has already typed the same information into an online application.

We realized asking the client to fill out census information twice, once in the health application portal and once for one of the spreadsheets, was one too many. So we took it in house. That fixed the client’s experience and moved the 163 columns onto our desk.

Wanna fix it?

Retyping an employee census gets fixed with a tool that reads the census export, cleans it up, fills each carrier’s own template, and lists what a person still has to finish.

1. Start from the newest export. The tool grabs the latest census file in the folder. I run mine by telling Claude “Map the census.”
2. Clean it up. It removes the duplicate rows, groups each employee with their spouse and children, and converts dates, state names, phone numbers, gender, and relationship codes into what the carrier expects.
3. Fill the carrier’s own template. It writes two carrier enrollment files from each carrier’s template and keeps the template’s dropdown menus working.
4. Set sensible defaults. Full time and 30 hours unless told otherwise. It handles up to 10 children per employee and warns if there are more.
5. Hand a person the rest. It prints the counts of employees, spouses, and children, plus the list of fields left to fill.

What does a person still fill in?

A person still fills in the fields a census can’t know: the plan code, salary and pay frequency, class and title, every voluntary election, and dependent coverage types. Those are judgment calls or agency information, and they’re where I want a human looking anyway. I ran this on real groups in April and August 2026.

What goes in an employee census template?

An employee census template needs one row per person with name, date of birth, gender, relationship to the employee, home address, state, phone, hire date, hours, and coverage tier. The carrier templates I’ve filled also ask for salary, class, and plan elections. Use the carrier’s own template when it has one. Its column order and dropdowns are what their system reads.

What’s an 834 file, and do you need one?

An 834 file is the HIPAA standard electronic format for benefit enrollment, formally the ASC X12 834 Benefit Enrollment and Maintenance transaction. Employers and plan sponsors use it to add, change, or end coverage for employees and dependents.

My tools don’t make 834 files. They fill carriers’ census spreadsheets, or push enrollments straight into a carrier’s enrollment system through its connection for outside software. That push shows a preview, and a person types the group name to confirm before anything goes. One thing I learned testing it: errors can come back inside a reply that says success, so the tool reads the whole reply. If a carrier asks you for an 834, ask what else it accepts before you build anything.

Can you do this yourself?

Yes, if you’re patient in Excel. Build one cleanup sheet with lookup formulas for states and relationship codes, remove duplicates, and paste the result into the carrier’s template. Check the dropdowns still work afterward, because copying can break them.

It stops making sense when you’re doing it for a new group every week. One group a quarter, do it in Excel. A group a week, build the tool.

Quick answers

What is an employee census?

A spreadsheet of every employee and dependent, with the details a carrier needs to quote or enroll them.

What is a census file?

The employee census, laid out in the format a specific carrier asks for.

What is an 834 file?

The HIPAA standard electronic enrollment format, ASC X12 834.

Does DoBetter build 834 files?

Not today. My tools fill carrier census spreadsheets or submit to a carrier’s enrollment system.

What does a person still fill in?

Plan code, salary and pay frequency, class and title, voluntary elections, and dependent coverage types.

How many columns was the carrier sheet?

163.