Getting Data Out of Spreadsheets Without Losing Your Sanity

Someone emails you an Excel file. Your application needs JSON. This should be simple.

It never is.

The spreadsheet has merged cells. Or dates formatted weirdly. Or that one column where someone typed "N/A" in what should be a number field. Every conversion becomes a debugging session.

Here's how to make it less painful.

CSV: The Universal Translator

Almost every spreadsheet app exports to CSV. Almost every system can read CSV. It's the lowest common denominator of data formats.

CSV is just text: values separated by commas, rows separated by newlines.

name,email,age
John,john@example.com,32
Jane,jane@example.com,28

Simple. Until it isn't.

The comma problem. What if a value contains a comma? "Smith, John" as a name breaks parsing. Solution: wrap values in quotes. Good CSV tools handle this automatically. Bad ones don't.

The newline problem. What if a value contains a line break? Same solution: quotes. Same caveat: not all tools handle it.

The encoding problem. Excel on Windows defaults to a different character encoding than Excel on Mac. Special characters get corrupted. Export as "CSV UTF-8" when possible.

JSON: What APIs Actually Want

Web applications prefer JSON:

[
  {"name": "John", "email": "john@example.com", "age": 32},
  {"name": "Jane", "email": "jane@example.com", "age": 28}
]

Converting from CSV to JSON is usually straightforward. The first row becomes property names, subsequent rows become objects.

Issues arise when:

Types get lost. CSV is all strings. "32" could be a number or text. JSON conversion has to guess. Most tools assume: looks like a number, probably a number.

Nulls are ambiguous. Empty cell = null? Empty string? Omit the property entirely? Different tools decide differently.

Arrays don't exist. CSV is flat. If your JSON needs nested arrays, you're doing manual work.

Common Workflow

1. Export from Excel/Sheets as CSV
2. Open in a text editor, check for obvious problems
3. Convert to JSON
4. Validate the JSON structure
5. Spot check a few records

The spot check matters. Automated conversion can silently corrupt data. A date becomes a number. A zip code loses its leading zero. "O'Brien" becomes "O'Brien". Verify before trusting.

Handling Weird Data

Dates. Excel stores dates as numbers internally. Export can produce "44927" instead of "2023-01-15". Know your date format before converting.

Numbers as text. Zip codes, phone numbers, IDs—these look like numbers but shouldn't be treated as numbers. Leading zeros matter. Convert to string explicitly.

Unicode. Names with accents, data in non-Latin scripts. Make sure your pipeline handles UTF-8 end to end.

Going the Other Way

JSON to CSV loses information. Nested objects flatten awkwardly. Arrays become... what, exactly? Multiple rows? Concatenated strings?

For flat JSON with consistent structure, conversion works fine. For complex nested data, you're making decisions about how to flatten it. Document those decisions.

YAML and XML

YAML is JSON's hipster cousin. More readable, same data structure, easy to convert between.

XML is the enterprise version. Verbose, widely supported by older systems, requires more parsing effort.

Both convert to JSON and back. The main headache is attributes vs elements in XML—JSON doesn't have that distinction.

---

Data conversion is about knowing what can go wrong and checking for it. Automate the conversion, verify the results, and never trust that "it just works."