JSON that converts cleanly
The easiest shape is an array of objects with consistent keys. In [{"name":"Baro","score":95}], each object becomes a row and keys become columns.
Different keys create blank cells. Normalize required keys and value types where possible before conversion.
CSV headers and quoting
The first CSV row normally contains column names. A value containing a comma, quote, or line break must be quoted, and quotes inside the value are doubled.
Splitting text on every comma will break addresses and sentences, so use a parser that follows CSV quoting rules.
- First row: column names
- Comma or line break: quote the value
- Quote inside a value: double it
Nested objects and arrays
One CSV cell cannot directly represent hierarchy. Preserve an object or array as a JSON string in one cell, or flatten only the fields you need.
If the CSV must become JSON again, document which columns are numbers, booleans, or nested JSON. CSV values may otherwise return as strings.
Checks before opening in a spreadsheet
Spreadsheets may remove leading zeros from postal or product codes and convert long numbers to scientific notation.
Do not upload sensitive source data without confirming local processing. After conversion, compare row count, header count, and representative samples with the original.
Check structure and encoding before conversion
Determine whether JSON is one object or an array and what becomes one CSV row. A flat object array with consistent keys is safest. Decide whether differing keys expand the header or cause an error, and how numbers, booleans, null, and empty strings are represented.
Use UTF-8 for Korean text and confirm whether the target spreadsheet expects a BOM. Delimiter, decimal separator, and date interpretation can vary by locale. Preserve the source and confirm that sensitive data is handled locally or under the required policy.
- Define row unit and required keys
- Document null, empty, and type conversion
- Confirm UTF-8, delimiter, and locale
Flatten nested objects and arrays deliberately
A nested field can become user.address.city or remain an address JSON string in one cell. Variable-length arrays may stay as JSON or move to a child CSV linked by a shared ID. Expanding arbitrary array positions into columns creates unstable structure.
For round trips, retain the flattening separator, array method, and type metadata. Escape original keys containing the separator. When using parent and child files, validate unique, nonempty IDs so relationships survive.
- Use dotted fields or JSON cells for objects
- Consider child tables for variable arrays
- Preserve type, separator, and ID rules
Handle commas, quotes, newlines, and spreadsheet coercion
Values containing a comma, quote, or newline must be quoted, with internal quotes doubled. A simple split on comma breaks addresses and descriptions. Re-read representative exported rows to confirm the original strings return.
Leading-zero codes, long integers, and values such as 1-2 may be converted to numbers, scientific notation, or dates by spreadsheets. Import those columns as text or use a typed format such as XLSX. Untrusted values beginning with formula characters also need protection against CSV injection.
- Use standard CSV quoting
- Import codes and long numbers as text
- Protect against formula injection
Validate rows, headers, and round trips
Compare total rows, unique keys, header count, null count, and numeric totals. Inspect first, middle, last, and special-character rows. For large files, record processed row count and test on a small copy first.
When possible, convert JSON to CSV and back and compare normalized structures. CSV can lose types and nesting, so document accepted differences. Label final files with date, version, and encoding, preserve the original, and test imports before production use.
For system imports, define the key that distinguishes new records from updates. Blank or duplicate keys can create duplicates or overwrite the wrong row, so review a validation report before applying data.
- Compare counts, keys, nulls, and totals
- Inspect representative special rows
- Back up and test before production import