CSV to JSON Guide: Convert Spreadsheet Rows into API-Friendly Data

CSV is convenient for spreadsheets and exports. JSON is usually easier for APIs and application code.

A simple CSV:

name,email,active
Isaac,[email protected],true
May,[email protected],false

can become an array of JSON objects.

Duck Cloud's CSV to JSON converts delimited rows into JSON locally in the browser.

Headers become object keys

The first row commonly defines field names:

name,email,active

Those become JSON keys.

Clean headers before conversion.

Avoid accidental spaces such as:

" email"

because that may create a key containing whitespace.

CSV quoting matters

CSV is more than splitting each line on commas.

A field can contain a comma when quoted:

name,company
Isaac,"Duck Cloud, Inc."

A correct CSV parser understands quoting rules.

Do not parse arbitrary CSV with a simple split(",") in production code.

Delimiters vary

Not every file uses commas.

Exports may use:

• semicolons;
• tabs;
• pipes;
• locale-specific separators.

Check the source and configure the converter accordingly.

Use the CSV Viewer first when you need to inspect whether rows and columns are being interpreted correctly.

JSON data types

CSV does not carry rich type information in the same way JSON does.

A cell containing:

00123

might be:

• an ID that must remain a string;
• the number 123;
• a postal code where leading zeros matter.

Likewise:

true

might be intended as a boolean or simply text.

Define type conversion rules explicitly instead of guessing.

Empty values

CSV rows can contain empty fields:

name,email,phone
Isaac,[email protected],

When converted to JSON, your application must decide whether an empty cell becomes:

• an empty string;
• null;
• an omitted property.

Different choices affect validation and downstream APIs.

Duplicate headers

A CSV with duplicate column names is ambiguous:

name,email,email

Which value should become email in the JSON object?

Detect and resolve duplicate headers before conversion.

Spreadsheet formatting surprises

Spreadsheet software can silently transform data.

Examples include:

• long numbers into scientific notation;
• dates into locale-specific formats;
• leading zeros removed;
• formulas evaluated;
• identifiers reformatted.

Before conversion, inspect the raw CSV rather than relying only on how the spreadsheet visually displays it.

Validate the resulting JSON

After conversion, use the JSON Validator and JSON Viewer to inspect the structure.

If you are comparing a new export with an old API payload, use JSON Diff / Compare.

Importing into an API

Before sending converted JSON to an API:

1. check required fields;
2. convert types deliberately;
3. validate email/date/ID formats;
4. decide how blanks map;
5. batch large imports;
6. handle API rate limits;
7. record failed rows safely.

Do not assume syntactically valid JSON satisfies the API contract.

CSV-to-JSON checklist

• Confirm delimiter.
• Verify the header row.
• Detect duplicate headers.
• Preserve leading zeros where needed.
• Decide type conversion rules.
• Decide how empty cells map.
• Inspect quoted fields.
• Validate the JSON.
• Test a small import before processing the entire dataset.

CSV-to-JSON conversion is easy for simple tables. Reliable conversion comes from being explicit about headers, types, blanks, and spreadsheet behavior.