From a spreadsheet export to useful JSON
A successful conversion preserves meaning, not just rows and columns. Spend a moment checking what a spreadsheet exported before treating the resulting JSON as application data.
Start with a tiny representative sample
Copy the header and a few rows that include the awkward cases: an empty cell, a comma inside a name, a code with leading zeroes, and a field with quotation marks. A table that works only for simple rows may still fail on the real file. Use invented data while learning the workflow. Keep the original export unchanged so you can return to it if the conversion reveals a problem.
Identify the delimiter
Look at the characters between the columns. Comma, semicolon, and tab exports are all common. If a row that should have five fields appears as one field, the wrong delimiter is a likely explanation. Do not replace every comma with another separator: a comma may belong inside a quoted field. Choose the correct delimiter in the converter and let the parser handle quoting.
Give each column one clear name
With header mode enabled, the first row becomes the property names of each JSON object. Names must be non-empty and unique. Two columns both called score are ambiguous; rename them to something useful such as quiz_score and exam_score before converting. Whitespace around headers is trimmed, but the converter does not infer your intended schema or rename duplicate columns automatically.
Keep identifiers separate from quantities
A product code such as 0012 is not necessarily the number twelve. Treating it as a number loses its leading zeroes. Daykit keeps all CSV fields as JSON strings so that this decision stays with you. The same caution applies to phone-like values, dates, and very large identifiers. Convert a specific field to a number only when the receiving application requires it and you have checked its allowed range.
Check structure before importing
A valid JSON array does not prove that your destination accepts the fields. Confirm expected names, required values, allowed types, and the meaning of an empty string. Daykit reports inconsistent row widths instead of silently dropping fields. If the receiving application requires nested data, the flat converter output is only a starting point. Make the additional transformation deliberately and keep a test import small.
Compare the counts and inspect a round trip
Count the data rows separately from the header. Check a few records near the start and end and one containing a quoted comma or line break. Download the result, open it in the JSON formatter, and confirm that it still parses. For a consequential dataset, validate against the destination’s documented schema and use the source system’s audit and backup process.
Read the editorial & corrections policy for the scope of our checks and authorship.