CSV JSON Cell Repair

Keep an entire JSON object in one CSV column

CSV and JSON both use commas. CSV uses them between cells. JSON also uses them between object members and array elements. A CSV reader needs a quoted field to distinguish nested comma characters from the next column.

Open CSV JSON Cell Repair

Repair a reading between an ID and a status

Input

ID,Reading,Status
007,{"value":26,"unit":"C"},ok

Output

ID,Reading,Status
007,"{""value"":26,""unit"":""C""}",ok

The row has three columns. Column 2 is the Reading value. The repaired file surrounds that value with CSV quotes and doubles each literal quote inside it. A CSV reader delivers the original JSON characters as one field. A JSON reader can then decode that field.

Check the result

  1. Count the intended columns from the schema or header and enter each JSON column number starting at 1.
  2. Check that the report identifies only columns intended to hold JSON. In a file with no header, clear the header checkbox.
  3. Load the export using a CSV parser. Check that the following column still has its original value and decode the JSON cell separately.

Common problems

Keep row width meaningful

Extra commas outside a selected JSON column can indicate a separate quoting problem. The tool stops on a row whose field count disagrees with the expected width.

Use valid JSON in the selected cell

The repair recognizes bare objects and arrays. It keeps valid existing CSV quotes and plain placeholders like NONE. Invalid JSON syntax and mixed CSV quoting will need their own source correction.

Preserve identifier types on import

The export keeps a value such as 007 exactly as supplied. Spreadsheet importers may still convert it to the number 7. Set identifier columns to text in the destination tool when those zeros are significant.

Using CSV JSON Cell Repair

Open the CSV

Paste its contents or open the original UTF-8 file.

Set the column positions

Enter the expected row width, the JSON column numbers and whether the first line is a header.

Review the repair

Check the changed cells, export the CSV and open it in your import tool.

Offline use

Open the tool once online and let it finish loading. Use the install option in your browser or Add to Home Screen on iPhone. Reopen the tool to process a new input offline.

References