CSV and JSON round trips without breaking rows or types

Moving between CSV and JSON is routine until a comma inside a name, an empty cell, or a nested object breaks the shape. Teams that work with developer tools on Utilido often hit this when a spreadsheet export must become API JSON, or when a JSON fixture must land back in finance as a table. The conversion step can run in your browser on Utilido, which helps when you are cleaning exports locally and do not want to paste customer rows into a random online converter.

This guide covers what each format expects, how to run a faithful round trip, and where spreadsheets quietly lie to you.

Why round trips fail even when the file “looks fine”

CSV is a text table. JSON is a typed tree or list of objects. Converters bridge the gap, but they cannot recover information that was never in the file, or that Excel already mangled.

Common failure modes:

• Unquoted commas inside a cell split one field into two columns.
• Headers renamed on export (spaces, BOM characters, duplicate column names).
• Type loss: numbers become strings in CSV; dates become ambiguous text.
• Nested JSON flattened into awkward column names like address.city.
• Empty cells interpreted as null, "", or missing keys depending on the tool.

A round trip means: CSV to JSON, edit or validate, JSON back to CSV, then open the sheet and confirm row count and column order. If you skip the last step, you only tested half the pipeline.

CSV expectations every converter assumes

Most tools treat the first row as headers. Each following row is one record. If your export has title rows, subtotals, or blank lines above the header, strip them before conversion or those lines become garbage data.

Quoting rules matter when a cell contains commas, double quotes, or line breaks. Standard CSV wraps the cell in double quotes and escapes internal quotes by doubling them:

name,role,notes
Ada,"Engineer, backend","Said ""ship Friday"""
Bob,Designer,

Open the raw file in a text editor when Excel hides quoting. A cell that shows as one column in the grid may be two fields on the wire.

Trailing commas on the last row sometimes create an extra empty column. UTF-8 BOM at the start of a file can make the first header key \ufeffid instead of id, which breaks joins in code.

Empty trailing columns often become keys with blank names or __EMPTY style names from spreadsheet tools. Delete unused columns before export when you can.

JSON expectations before you convert to CSV

JSON to CSV works best on an array of similar objects:

[
  { "id": 1, "name": "Ada", "active": true },
  { "id": 2, "name": "Bob", "active": false }
]

One top-level object becomes a single row. A bare string or number at the root is usually not what table tools expect.

Deep nesting may need flattening first. Some converters emit parent.child column names; others skip nested branches. Know your tool behavior before you promise finance a flat sheet.

Arrays inside a row (tags, phone numbers) may stringify as JSON text in one cell, or expand into multiple columns, depending on settings. Document the choice if downstream scripts parse the CSV.

Numbers and booleans should stay typed in JSON. In CSV everything is text once written. "00123" and 123 are not the same after a round trip if leading zeros matter.

Use CSV to JSON when the source is a table file. Use JSON to CSV when the source is structured JSON. The direction switcher on each tool page beats guessing URLs during an incident.

A repeatable bidirectional workflow

1. Freeze a sample: five to twenty rows that include edge cases (commas in text, empty cells, long numbers, time zones).
2. Export CSV from the spreadsheet as UTF-8 when the app offers it.
3. Convert to JSON and inspect keys on the first and last object.
4. Validate with the JSON formatter: trailing commas, smart quotes, and single quotes are common paste errors.
5. Edit JSON if needed (API shape, rename keys, drop columns).
6. Convert back to CSV and open in the sheet: column order, row count, and spot-check IDs and dates.
7. Only then run the full file.

Keep the sample under version control. Diff JSON in git when the API changes; diff CSV when the business rules change.

Excel and Google Sheets quirks

Spreadsheets are where round trips go to die, usually before JSON exists.

• Scientific notation on long numeric IDs (1.23E+10) destroys identifiers. Format the column as text before paste, or prefix with an apostrophe in Excel.
• Date serial numbers export as numbers, not ISO strings. Convert to explicit YYYY-MM-DD in the sheet if your API expects strings.
• Locale separators: some locales use ; instead of , as the delimiter. Your converter may need a semicolon mode or a pre-normalized file.
• Merged cells do not export cleanly. Unmerge before export.
• Formulas export as displayed values or as =... text depending on settings. Confirm which you need.

If the API rejects the payload after conversion, paste the JSON into the JSON formatter and fix the reported line first. For a structured pass after API or config changes, follow the JSON debug checklist after API changes.

Headers, types, and API contracts

Align CSV headers with JSON keys your API already accepts. Renaming in the sheet without updating the consumer causes silent partial updates.

Concern	In JSON	After CSV round trip
Integer IDs	number	often string; watch leading zeros
Booleans	true/false	”TRUE”/“FALSE” or “true”/“false” text
Null	null	empty cell or literal “null” string
Arrays	[1,2]	one cell or exploded columns

When the consumer is strict, add a schema check in your app or tests, not only eyeballing the sheet.

Nested and wide data

If each row carries nested objects, decide upfront:

• Flatten to dotted keys for reporting CSV.
• Keep JSON in one column for machines, not humans.
• Split into multiple files (parent table + child table) for relational imports.

Round tripping nested graphs through a single flat CSV often loses structure. That is not a converter bug; it is a format mismatch.

When a round trip is the wrong test

Do not expect CSV to preserve JSON features there is no column for: undefined, non-enumerable keys, or duplicate keys in one object. Do not use CSV as your source of truth for hierarchical config; use JSON or YAML and export CSV only for humans.

If you only need one direction (export for analytics), test that direction alone and skip the return path.

Encoding in logs and configs

Sometimes the “CSV” step is actually a Base64 blob inside YAML or a log line. Decode and inspect before you treat it as a table. See Base64 in config files and logs for padding and URL-safe variants so you do not chase the wrong alphabet.

What I check after exporting CSV from Excel

I always open the raw CSV in a text editor before any conversion. Excel has convinced me a column is fine while an unquoted comma split Engineer, backend into two fields. I keep a five-row fixture with a comma in a name, an empty middle cell, and a thirteen-digit ID formatted as text. If that fixture survives CSV to JSON to CSV with the same keys and row count, I trust the big export. One minute in Notepad or VS Code has prevented more bad deploys than any amount of grid staring.

FAQ

Does CSV support nested objects like JSON?

Not natively. Converters flatten nesting into column names or put JSON text in a cell. Plan flattening or multi-file exports before you promise a flat sheet to stakeholders.

Why did my JSON keys get a weird first character?

A UTF-8 BOM on the CSV can prefix the first header. Re-save without BOM or strip \ufeff in code. The first row must be headers only, not report titles.

Can I round trip through Utilido without uploading my file?

The convert step runs in your browser for that transform. Loading the page still uses the network like any website. For sensitive rows, use the sample workflow and keep production extracts off shared machines.

Why are all my numbers strings after JSON to CSV?

CSV has no number type. Consumers must coerce if they need arithmetic. If leading zeros matter, keep IDs as strings end to end and format spreadsheet columns as text before export.

What if my file uses semicolons instead of commas?

European exports often use ; as the delimiter. Normalize to comma-separated UTF-8 before tools that expect RFC-style CSV, or use a converter that accepts the delimiter you have.

Should I validate JSON before converting back to CSV?

Yes. Syntax errors block conversion. Shape errors (array vs object) block predictable columns. The JSON formatter catches paste and editor issues early.