FIELD GUIDE / STRONG
CSV cleaning for home health operators
Spreadsheet imports add whitespace, strip digits, display scientific notation, and mix missing values with malformed values. This guide applies the work to agency and clinician rosters and agency Type 2 records.
CHECK YOUR ROSTER
Upload your agency and clinician roster and clean NPI values before NPPES lookup.
The same validator used on the homepage: local checksum checks, live public NPPES lookup, cautious differences, and clean export.
What the check needs to separate
Agency organizations and clinicians coexist while service areas may not match one public practice address. For csv cleaning, Spreadsheet imports add whitespace, strip digits, display scientific notation, and mix missing values with malformed values.
In csv cleaning for home health operators, agency Type 2 records changes how a result should be interpreted. NPPES does not verify agency certification, employment, or service authorization.
| Roster input to retain | Public NPPES evidence to append |
|---|---|
| agency_npi | entity type |
| branch_code | practice address |
| raw NPI | lookup status |
| source row ID | — |
FICTIONAL OPERATIONAL EXAMPLE
CSV cleaning in a fictional agency and clinician roster
During a fictional csv cleaning review, a agency and clinician roster contains 65 agencies and 1,200 field clinicians. One row for Quinn Harper, RN / Fictional Hearth Home Health reaches review because service area compared as NPPES address.
For this csv cleaning review, the source retains agency_npi, branch_code, agency_npi for traceability.
Keep raw and normalized values side by side; every cleaning rule should be reversible. The reviewer also checks agency Type 2 records.
Common errors in this workflow
- 01whitespace around an NPI
- 02blank represented as zero
- 03service area compared as NPPES address
- 04agency NPI assigned to a clinician
A defensible workflow
- 01
Preserve every original row and raw identifier.
- 02
Trim display separators without inventing digits.
- 03
Classify blanks, malformed values, and duplicates separately. Retain branch_code as operational context.
- 04
Run the checksum before NPPES requests.
- 05
Append results without overwriting source columns.
REVIEW GUIDANCE
Use the result as evidence, not a verdict.
Keep raw and normalized values side by side; every cleaning rule should be reversible. Preserve agency Type 2 records as a separate consideration for home health operators.
Formatting repair does not prove the identifier belongs to the input provider. NPPES does not verify agency certification, employment, or service authorization.
QUESTIONS
What reviewers usually need to know
What should home health operators do first?
Preserve the source row, normalize locally, and keep agency_npi before comparing public fields.
Should a difference be corrected automatically?
Usually not. Keep raw and normalized values side by side; every cleaning rule should be reversible.
What does an NPPES match establish?
It confirms public fields returned at lookup time. NPPES does not verify agency certification, employment, or service authorization.
Primary references: CMS National Provider Identifiers and the NPI Registry API documentation. Public provider-reported data should be read with its source date and limitations.