SimpleWebUtils
ENKO

Document Workflow

Convert CSV to JSON for spreadsheet data cleanup

Convert spreadsheet CSV exports to JSON without losing quoted cells, multiline text, leading-zero IDs, duplicate headers, or empty values.

Written and tested by Published: Reviewed:

How this workflow was checked

The CSV to JSON and JSON to CSV Converter check used the exact input from “CSV rows converted to JSON objects”. After “Choose the delimiter and header mode” and “Preserve cell text unless trimming is intentional”, we matched the resulting values or file against the documented output and inspected the risks described by “Ignoring commas inside quoted cells” and “Leaving spreadsheet formulas in exported rows”.

The two CSV records became two JSON objects with string IDs and unchanged email and plan values; quoted delimiters stayed inside their source field.

Open CSV to JSON and JSON to CSV Converter | Local and Loss-Aware

Problem

CSV exports are easy to move between spreadsheets, but APIs, test fixtures, import previews, and debugging tools usually expect JSON. A naive split can break quoted commas and multiline cells, while automatic typing can change leading-zero IDs or large account numbers. This workflow selects the actual delimiter, validates every row against the expected column count, keeps cell text unchanged by default, and verifies warnings before the result is downloaded or used elsewhere.

Sources and standards

These authoritative references define the formats or security boundaries used in this workflow. Tool-specific verification is documented separately above.

When to use this

  • A spreadsheet export needs to be pasted into an API request.
  • A product or customer list needs to become a JSON fixture.
  • A CSV file should be checked before importing it into an internal tool.
  • A sheet from Excel, Google Sheets, or a regional export needs delimiter and encoding checks before conversion.

Steps

  1. Step 1

    Choose the delimiter and header mode

    Match the file's comma, tab, semicolon, or pipe delimiter. Leave First row is header enabled when the first row contains field names; turn it off when every row is data and you need JSON arrays instead of objects.

  2. Step 2

    Inspect and clean the header row

    Header whitespace is trimmed by default. Empty headers stop conversion, while duplicate headers are renamed with numbered suffixes and reported as a warning so values are not silently overwritten. Rename those generated keys when the receiving API requires specific field names.

  3. Step 3

    Preserve cell text unless trimming is intentional

    Cell whitespace is preserved by default, including spaces that may be meaningful in codes or notes. Enable Trim cell whitespace only when the destination contract explicitly ignores leading and trailing spaces. Empty cells are emitted as empty strings.

  4. Step 4

    Use type inference only when the schema allows it

    The default output keeps every CSV cell as a JSON string. Enable Infer safe JSON types only when booleans, null, and valid JSON numbers should become scalar values. Leading-zero identifiers and risky numeric text that could lose precision remain strings and produce a warning.

  5. Step 5

    Check metrics, warnings, and edge rows

    Compare the reported row and column counts with the source sheet. Review duplicate-header, trimming, inference, and preserved-number warnings, then inspect rows containing quoted commas, multiline text, non-English characters, blank cells, and the largest identifiers.

  6. Step 6

    Download and verify the destination behavior

    Choose pretty output for review or minified output for compact transport, download the JSON, and reopen it in the API client, test runner, or import preview that will consume it. Confirm required keys and data types before processing the full dataset.

Example

CSV rows converted to JSON objects

Input

id,email,plan
42,dev@example.com,pro
43,ops@example.com,starter

Output

[
  {
    "id": "42",
    "email": "dev@example.com",
    "plan": "pro"
  },
  {
    "id": "43",
    "email": "ops@example.com",
    "plan": "starter"
  }
]

Common mistakes

Using human labels as API keys

Rename headers like Customer Email to stable keys such as customerEmail or email before generating fixtures. Automatic duplicate suffixes prevent overwrites, but they cannot know the field names required by your API.

Ignoring commas inside quoted cells

Addresses, names, and descriptions may include commas. A converter should respect quoted CSV cells instead of splitting blindly.

Leaving spreadsheet formulas in exported rows

CSV exports may contain calculated values or literal formulas depending on how the sheet was saved. Review formula columns before converting them into JSON fixtures or API examples.

Forgetting the CSV dialect used by the export

Delimiter, line endings, and encoding can vary between Excel, Google Sheets, and regional exports. Match the available delimiter setting to the file and correct the source encoding if characters are already broken before pasting.

Importing private spreadsheet columns

Exports often include notes, internal IDs, hidden columns, reviewer comments, or temporary QA fields. Remove columns that the API or fixture does not need before converting the file, especially when the JSON will be pasted into a ticket, shared with a vendor, or committed as test data.

FAQ

Should CSV numbers become JSON numbers?

It depends on the API. IDs, postal codes, and account numbers are often safer as strings because leading zeros can matter.

What if my CSV uses semicolons?

Some spreadsheet exports use semicolons based on locale settings. Pick the delimiter that matches the file before converting.

Can CSV represent nested JSON?

CSV is flat by default. Nested JSON usually requires a mapping rule or a later transform after the basic conversion.

How should I handle duplicate CSV headers?

The converter renames duplicates with deterministic numbered suffixes and reports the changes, so both values survive. Rename the source headers to explicit names such as billingEmail and shippingEmail when the destination schema requires meaningful keys.

How should blank cells be represented in JSON?

This converter preserves blank CSV cells as empty strings so every row keeps the table shape. If an API requires null or omitted fields, apply that schema-specific transform after conversion and verify it separately.

How do I verify the converted JSON before importing it?

Check the reported row and column counts, required keys, data types, and every warning before using the JSON. Review rows with blank cells, quoted commas, multiline values, non-English text, and leading-zero IDs, then reopen the downloaded result in its destination.