Guide · Data Quality
How to clean spreadsheet data before sending it to an API
Spreadsheets are designed for people. APIs are designed for structured data. Cleaning the values between those two worlds prevents invalid payloads and unreliable imports.
Why spreadsheet data needs cleanup
A spreadsheet cell can contain currency symbols, thousands separators, labels, extra whitespace or placeholders and still look perfectly reasonable to a person.
An API generally expects a more precise representation. A JSON number cannot contain a currency symbol. A JSON boolean is true or false, not Yes, Enabled or Active.
1. Remove presentation formatting from numbers
Values such as these commonly appear in exported spreadsheets:
If the target field is numeric, remove only the presentation characters that are not part of the underlying value. The result should be something that can be converted safely to a number.
2. Normalize boolean labels
Business spreadsheets often use labels instead of literal booleans:
Do not assume every label means the same thing in every dataset. Confirm the intended semantics before normalizing domain-specific values.
3. Trim accidental whitespace
Leading and trailing spaces are easy to miss visually and can cause equality checks, enums or downstream matching to fail.
4. Be careful with case normalization
Converting values to uppercase or lowercase can be useful, but it is not universally safe. Some APIs treat identifiers and enum values as case-sensitive.
Use case transformations only when you know the target representation. Mixed casing by itself is not enough evidence that all values should be changed.
5. Decide how to handle missing values
Empty cells can mean different things: unknown, optional, not applicable or accidentally missing. Before replacing an empty value with a default, determine what the API expects.
Possible API behaviors include:
- omit the property,
- send an empty string,
- send null,
- provide a defined default,
- reject the record because the field is required.
6. Validate transformations across every row
This is one of the most important steps. A transformation that converts₹3,83,000successfully may still leave$85,000invalid.
A safer cleanup workflow
- 01Identify the target API field and data type.
- 02Inspect the actual source values.
- 03Apply the smallest transformation needed.
- 04Preview the transformed result.
- 05Validate the rule across the whole dataset.
- 06Review any remaining failures.
- 07Only then generate the API payload.
Preview cleanup before generating your request
PayloadForge lets you build transformation rules, inspect dataset-wide impact and review smart cleanup suggestions before generating JSON or cURL.