CSV and JSON conversion without losing data
Handle headers, quotes, types, nested values and spreadsheet risks when moving tabular data between CSV and JSON.
Understand the two different data models
CSV represents rows and columns. A first row commonly supplies field names, but CSV does not inherently preserve types, nested objects or arrays. JSON can represent strings, numbers, booleans, null, arrays and nested objects. Converting between them therefore requires decisions that cannot always be reversed automatically.
The SableApps CSV-to-JSON direction treats the first row as unique property names and produces an array of objects. Cell values remain strings so leading zeroes, long identifiers and date-like text are not silently changed. JSON-to-CSV collects object keys as columns and serializes nested values into text inside a cell.
Check commas, quotes and line endings
RFC 4180 documents a commonly used CSV form: records appear on separate lines, fields are comma-separated, and fields containing commas, quotation marks or line breaks are enclosed in double quotes. A quotation mark inside a quoted field is doubled. Real systems also use semicolons, tabs and regional conventions, so identify the source dialect before conversion.
Inspect records containing addresses, notes and multi-line descriptions. A parser that simply splits on commas will shift columns and corrupt these rows. Confirm that every row has the expected number of values and that duplicate or blank headers are resolved before creating JSON properties.
Keep JSON interoperable
RFC 8259 defines JSON as a text format with objects, arrays, numbers, strings, true, false and null. Property names are strings, and interoperable JSON avoids extensions such as comments, trailing commas, NaN or Infinity. Very large numbers may lose precision when opened by software that represents all numbers as floating point.
When turning nested JSON into a flat file, decide whether to serialize a nested value into one cell, expand selected properties into columns or create related tables. SableApps uses serialized text for nested values because guessing a schema could discard structure. A receiving spreadsheet will still see that serialized object as text.
Validate output and spreadsheet safety
Compare record count, column names and several edge rows before importing the result elsewhere. Preserve the source file and use a new output name. Choose UTF-8 when the next system asks for an encoding, then verify accented and Indian-language text rather than assuming every application detects it correctly.
CSV opened in spreadsheet software can interpret cells beginning with formula characters as active formulas. Do not open untrusted exports casually, and follow the target system's import and formula-neutralization rules. Conversion changes representation; it does not sanitize hostile content or validate whether business data is correct.
Worked example: export customer records to an API
A CSV contains customer_id, name and note. One identifier begins with zero, one name contains a comma and one note spans two lines. Convert it and confirm that customer_id remains a string, the quoted name remains one field and the line break stays inside the note. Count JSON objects against non-header CSV records before sending anything.
If the API expects numbers or booleans, transform those fields under an explicit schema after conversion rather than guessing from appearance. An empty cell, the word null and JSON null may mean different things. Run a small test import, inspect rejected records and retain the source-to-output mapping. For a reverse export, open the CSV as data and check that formula-like text is handled under the recipient's spreadsheet policy.
Quick checklist
- Identify the CSV delimiter and header row.
- Test commas, quotes and multiline fields.
- Preserve identifiers and leading zeroes as text.
- Choose a policy for nested JSON.
- Compare row counts and edge records.
- Treat untrusted spreadsheet cells as potentially active content.
Primary references
Standards and platform support change. Use these authoritative sources to check the current specification and migration guidance.