Case Conversion for API Data Cleanup — Normalizing Messy Exports

The webhook payload looked fine until someone grouped by status and got twelve buckets for what should have been three. ACTIVE, active, Active, and active (trailing space) all counted as different values. Nobody had a data bug. They had a casing bug — the kind case conversion fixes in minutes if you know where to apply it.

API integrations inherit decades of naming sins. CRM exports scream in UPPERCASE. Legacy mainframes deliver Customer_Name. Mobile clients send productId while the warehouse expects product_id. Case conversion won't solve every schema mismatch, but it's the fastest first pass before regex, mapping tables, and migration scripts.

Quick answer

The webhook payload looked fine until someone grouped by status and got twelve buckets for what should have been three. ACTIVE, active, Active, and active (trailing space) all counted as different values. Nobody had a data bug. They had a casing bug — the kind case conversion fixes in minutes if you know where to apply it.

The two-layer model: canonical vs display

Production systems should separate:

Canonical keys — lowercase or consistent convention for storage, joins, and API matching

Display labels — any case the user or brand requires

Converting NEW YORK → new york for a grouping key is smart. Converting McSorley's → mcsorley's for a shipping label is not.

Case Converter handles the mechanical transform. You supply the judgment about which fields get which treatment.

Common API casing messes

| Source | Example field | Problem | |--------|---------------|---------| | Legacy SQL export | PRODUCT_NAME | All caps constants | | .NET API | ProductName | PascalCase properties | | JavaScript client | productName | camelCase | | Python service | product_name | snake_case | | CSV header row | Product Name | Spaces and title case | | Enum values | PENDING, Pending | Duplicate logical states |

Case convert is step one. Structural renames (spaces → underscores) are step two.

Workflow: cleaning a JSON export

Scenario: Partner sends nightly JSON with inconsistent enum casing.

1. Validate JSON — JSON Validator catches syntax errors before you touch values
2. Identify enum fields — status, type, country_code
3. Extract unique values — paste column into a text file
4. Lowercase for inventory — Case Converter → see true distinct count
5. Build mapping table — PENDING → pending, Active → active
6. Apply in ETL — script the map; don't manual-paste production volumes
7. Validate output — JSON Formatter for spot checks

Lowercasing exposed that active and active differ — trim whitespace before case normalization.

Field names vs field values

Field names (keys) — standardize to one convention:

ProductName → productName (camelCase API)
PRODUCT_NAME → product_name (snake_case warehouse)

Case convert handles letter case. Inserting underscores between words needs regex or camelCase splitters:

productName → product_name  (not just lowercase → productname)

Field values — case sensitivity depends on domain:

| Value type | Normalize? | |------------|------------| | Enum / status | Yes → lowercase canonical | | Email | Often lowercase local part | | Country code | Uppercase ISO (US) | | Password hash | Never change case | | Display name | Preserve user input | | SKU / product code | Case-sensitive — don't convert |

Email and username normalization

Many systems lowercase emails before storage to prevent duplicate accounts:

User@Example.COM → user@example.com

Document this policy. Passwords remain case-sensitive — never run case convert on credentials. See Password Security Guide.

Spreadsheet and CSV imports

CRM exports arrive with inconsistent City columns:

NEW YORK
new york
New York

For pivot tables and vlookup:

1. Paste column into Case Converter
2. Lowercase entire column
3. Re-import as city_normalized
4. Keep original column for display if needed

Pair with Word Counter when cleaning title fields with length limits.

API design: prevent the mess upstream

If you control the API:

• Document casing in OpenAPI spec
• Reject unknown enum casing with 400 + clear error
• Emit lowercase enums in JSON responses
• Use linters on schema definitions

If you consume third-party APIs:

• Normalize on ingest, not on every read
• Store both raw_status and status_normalized during migration
• Log unmapped values instead of silently dropping

Locale traps

Turkish dotted/dotless I breaks naive lowercasing:

Istanbul.toLowerCase() → istanbul  (wrong in Turkish locale)
İstanbul → requires toLocaleLowerCase('tr')

German ß → SS in uppercase round-trips imperfectly.

For English-only SKU cleanup, simple converters work. For international customer names, use locale-aware APIs in production code — browser Case Converter is a preview tool, not i18n infrastructure.

Case conversion + JSON structure

Changing keys changes object shape:

{ "ProductName": "Widget" }

after key lowercasing without structure fix:

{ "productname": "Widget" }

not:

{ "product_name": "Widget" }

Automate key renames with jq, Python dict comprehensions, or migration scripts. Case convert on values pasted as plain text; use code for key transforms.

Read JSON vs JSONL when exports arrive as line-delimited batches — normalize per line in streaming ETL.

Regex after case: underscore insertion

Pattern for screaming snake → camel:

1. Lowercase: STATUS_CODE → status_code (already done)
2. Or convert to camel: split on _, capitalize segments → statusCode

Case Converter won't insert underscores. Chain with search-replace or code.

Testing normalization

Build a fixture table:

| Input | Expected canonical | |-------|-------------------| | ACTIVE | active | | Active | active | | active | active (after trim) | | McDonald | mcdonald OR preserve — document choice |

Assert in unit tests. One shared test UUID or constant across tests hides integration bugs — same discipline as How UUIDs Work recommends for IDs.

When case conversion isn't enough

• Synonyms — USA vs US vs United States need lookup tables
• Typos — actve won't match active after lowercasing
• Encoding — mojibake from wrong charset needs fix before any text transform
• Semantic duplicates — cancelled vs canceled

Case normalize first; fuzzy match second; human review third.

Related articles

• Case Converter Uses — general writing and SEO workflows
• Common JSON Formatting Errors — structural fixes after casing
• JSON Formatting Guide — inspect cleaned payloads

Related tools

• Case Converter — Transform letter casing in bulk
• JSON Formatter — Pretty-print normalized JSON
• JSON Validator — Verify syntax before cleanup
• Word Counter — Check field lengths after title case

Key takeaways

• Should API field names be camelCase or snake_case: Pick one convention per API and document it.
• Is it safe to lowercase all API data: No.
• How do I fix mixed-case enums from a legacy system: Build a mapping table from legacy values to canonical values.

Conclusion

API data cleanup starts with separating canonical keys from display values, then applying case conversion where enums and matching fields need consistency. Lowercase for grouping, preserve case for brands and passwords, trim before converting, and follow with mapping tables for exceptions. Case Converter accelerates the first pass; production pipelines script the rules you discover in that pass.