FIELD GUIDE / STRONG
CSV cleaning for provider-directory teams
Spreadsheet imports add whitespace, strip digits, display scientific notation, and mix missing values with malformed values. This guide applies the work to directory refresh files and publishable locations.
CHECK YOUR ROSTER
Upload your directory refresh file 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
Directories accumulate stale locations, taxonomy drift, duplicate profiles, and Type 1/Type 2 confusion. For csv cleaning, Spreadsheet imports add whitespace, strip digits, display scientific notation, and mix missing values with malformed values.
In csv cleaning for provider-directory teams, publishable locations changes how a result should be interpreted. A public location does not prove that a provider currently accepts patients there.
| Roster input to retain | Public NPPES evidence to append |
|---|---|
| specialty | last updated date |
| display_name | entity type |
| source row ID | lookup status |
| source name | normalized NPI |
FICTIONAL OPERATIONAL EXAMPLE
CSV cleaning in a fictional directory refresh file
During a fictional csv cleaning review, a directory refresh file contains 4,200 clinician and facility listings. One row for Morgan Rivera, DDS / North Mesa Dental reaches review because facility brand matched to an individual NPI.
For this csv cleaning review, the source retains specialty, display_name, specialty for traceability.
Keep raw and normalized values side by side; every cleaning rule should be reversible. The reviewer also checks publishable locations.
Common errors in this workflow
- 01duplicate row deleted before context is retained
- 02scientific notation saved by spreadsheet software
- 03facility brand matched to an individual NPI
- 04mailing address published as a practice location
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 location_id 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 publishable locations as a separate consideration for provider-directory teams.
Formatting repair does not prove the identifier belongs to the input provider. A public location does not prove that a provider currently accepts patients there.
QUESTIONS
What reviewers usually need to know
What should provider-directory teams do first?
Preserve the source row, normalize locally, and keep directory_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. A public location does not prove that a provider currently accepts patients there.
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.