Data & spreadsheets · CSV Cleaner
How Header Normalisation Turns Messy Export Column Names Into Clean Keys
· How it works
csv json data-cleaning
Column names like 'Customer E-mail (Primary) ' break scripts, databases and JSON keys. This post explains what header normalisation changes, why duplicate and blank headers are the real danger, and when names must be left alone to match a schema.
A script that fails on a column it cannot find — how trailing spaces, capitalisation and punctuation in headers cause silent mismatches
A script asking for customer_email will not find a key written as Customer E-mail (Primary), and a trailing space can make a visually identical label different. CSV itself provides no registry of preferred names. The exact first-row text is therefore part of the interface between the exporting system and every consumer.
ToolAcre exposes that interface rather than silently redesigning it. The CSV-to-JSON path trims surrounding header whitespace while computing keys, fills blank names and suffixes repeats. It does not otherwise translate punctuation or capitalization, so you can see which assumptions belong in a deliberate mapping.
What a clean header looks like — lowercase, underscores instead of spaces, ASCII where possible, and unique within the file
Lowercase snake_case is a useful convention in some databases, but it is not a universal definition of a clean header and it is not implemented automatically here. Characters outside ASCII remain, spaces inside a name remain, and a dot such as user.name stays a literal dot in the JSON key.
This restraint avoids breaking a re-import that expects the producer’s exact labels. If your destination demands another convention, use the column editor on the toolkit index or a schema-aware import step. Record the mapping so next month’s export receives the same intentional names rather than a fresh set of guesses.
The converter preserves names rather than applying a lowercase-and-underscore convention
Blank and repeated names are the cases where object conversion could lose data. The converter names an empty first column column_1 and an empty third column column_3. When status appears twice, the second key becomes status_2 and a third would become status_3, preserving each positional value.
Those generated names are collision controls, not semantic repairs. Code expecting a meaningful field will not magically know that column_3 contains a region. Rename the source header before integration, then regenerate JSON and confirm every key. A deterministic placeholder is safer than overwriting a column, but it still asks for review.
Headers that are really data — detecting a title line or a repeated header row left over from concatenated exports
The tool always treats the first parsed row as the header. It does not detect a report title above the table, nor remove a header repeated halfway through concatenated exports. A headerless file donates its first data record to key names, exactly as the configuration warns.
Preview the row and column counts before conversion. If the first visible row is a title, remove it in an appropriate editor or regenerate the export; if repeated header rows appear later, treat them as data until you explicitly remove them. Automatic detection would risk deleting a legitimate record whose values resemble labels.
The first row is always treated as the header; title lines and repeated headers are not auto-detected
Imagine a CRM header of ` Customer E-mail (Primary) ,Notes,,Notes`. Conversion computes Customer E-mail (Primary), Notes, column_3 and Notes_2. The internal spaces, capitalization, hyphen and parentheses survive. Nothing becomes customer_email_primary unless a person chooses and applies that rename.
The resulting keys reveal both genuine labels and structural defects. An application can consume them as written, but a database loader may reject punctuation or unexpected placeholders. Resolve those requirements before import and compare the final header with the destination schema rather than assuming “normalization” has one safe meaning.
When not to normalise — files that must match an external schema or be re-imported into the system that produced them
Sometimes exact names are contractual. A vendor’s re-import, a recurring script or an external schema may require spaces, case and punctuation exactly as supplied. Automatic beautification would produce a cleaner-looking file that no longer joins the established workflow, which is a more serious failure than an awkward label.
Work from a copy and keep the original header available for comparison. When renaming is appropriate, change only the names required by the consumer and leave row values alone. The index page’s editor renames by column position, making duplicate starting names manageable without pretending their meanings are known.
What this does not cover — mapping columns between different systems or translating header language
This route does not map columns between systems, translate labels or infer that Email and E-mail are equivalent. It also does not inspect data values to invent semantic names. Those jobs require domain knowledge, and a generic parser cannot obtain it from punctuation or sample values without introducing risky guesses.
Likewise, generated suffixes are not a durable enterprise naming policy. They are a loss-prevention measure during JSON conversion. Use them to discover the collision, then decide whether each column should be renamed, removed or retained under a documented schema before building production code around the output.
Fix names once at the file boundary — how the ToolAcre CSV Cleaner's header tidying prepares an export for scripts and JSON conversion
Fixing names at a file boundary is useful only when the fix is explicit. ToolAcre’s converter guarantees unique keys for blank and duplicate headers; the separate toolkit index offers manual renaming and column selection. The dedicated cleaner route focuses on rows and does not claim to normalize headers automatically.
Confirm the first row, inspect generated keys and test the receiving system with a small copy. That sequence turns an invisible mismatch into a reviewable mapping. It also preserves the option to keep producer-defined labels whenever re-import compatibility matters more than stylistic consistency.