Convert JSON to CSV for Excel Viewing: Structure Requirements and Common Issues Guide

Only JSON in object array format can be flattened into a CSV table. For this type of JSON, the top level is an array, each element in the array is an object with similar structure, the key of each object can be used to generate a corresponding table column, and the value is used to generate the corresponding cell content.

JSON that is not in object array format cannot be directly converted into a two-dimensional table. For example, JSON with a single object at the top level and JSON with a pure value array at the top level do not conform to the structural logic of a two-dimensional table, and cannot generate a standard table.

When a JSON object array contains nested objects or arrays, most conversion tools expand nested objects using dot-separated key names. For example, the city field under an address object will generate a column name such as "address.city".

There is usually a boundary for processing nested arrays: only first-level nesting can retain comma-concatenated original values and store them in the corresponding cell. For nested arrays deeper than one level, the original JSON format string is stored in the cell to avoid damaging the two-dimensional structure of the table.

When Excel 2016 and earlier Windows versions directly open a CSV file encoded in UTF-8, they will parse the content with GBK encoding by default. Chinese characters will appear garbled due to encoding mismatch.

Adding a 3-byte UTF-8 BOM mark at the head of a UTF-8 encoded file allows Excel on Windows systems to automatically recognize the UTF-8 encoding, so that Chinese characters can be parsed correctly and the garbled text problem can be eliminated.

The default delimiter of a CSV file must match the Excel regional settings to display correctly in separate columns. The default list separator for Chinese Windows systems is a comma, and in most cases selecting comma as the delimiter allows normal column separation.

The default delimiter for systems in some European regions is a semicolon. If all content is squeezed into one column after opening the CSV, you can switch to the corresponding delimiter and regenerate the CSV file.

Excel enables scientific notation by default for numbers longer than 11 digits, and automatically pads trailing zeros for numbers longer than 15 digits, so the original number string cannot be retained. This has a large impact on data such as ID numbers and order numbers.

When converting to CSV, adding half-width double quotes around long number fields and adding a half-width single quote at the start can force Excel to recognize the content as text format, and retain the complete content of the original long number.