CSV to JSON Tutorial: Structuring Tabular Data

Why Convert CSV to JSON

CSV is a tabular data format and the default export format from Excel. JSON is a program-friendly format and the standard for API transmission. Converting CSV to JSON lets tabular data be consumed directly by programs.

Common scenarios:

• Importing Excel data into a website database
• Connecting tabular data to an API
• Passing data analysis results to the front end for rendering
• Migrating configurations from spreadsheets to JSON files

Differences Between CSV and JSON

CSV Format

name,age,city
Tom,25,Beijing
Jerry,30,Shanghai

• Plain text, comma-separated
• First row is the header
• One record per row
• No types (everything is a string)
• No nested structures

JSON Format

[
  {"name": "Tom", "age": 25, "city": "Beijing"},
  {"name": "Jerry", "age": 30, "city": "Shanghai"}
]

• Structured, key-value pairs
• Has types (string, number, boolean)
• Supports nesting
• Directly parseable by programs

Conversion Principles

Header Mapping

The first row of CSV is the header. When converting to JSON, headers become object keys:

• Header: name, age, city
• First data row: Tom, 25, Beijing
• Converted: {"name": "Tom", "age": "25", "city": "Beijing"}

Data Type Recognition

All CSV values are strings. When converting to JSON, types can be intelligently recognized:

• Number: "25" → 25 (number)
• Boolean: "true" → true (boolean)
• Empty: "" → null (null)
• String: stays a string

Without type recognition, everything is treated as a string.

Array Generation

Each CSV row becomes one object, and all rows form an array:

[
  {object1},
  {object2}
]

Nested Structures

The Problem

CSV is a flat table, while JSON supports nesting. How do you convert flat CSV into nested JSON?

Method 1: Dot Notation in Keys

Use dots in CSV headers to indicate hierarchy:

name,address.city,address.zip
Tom,Beijing,100000

Converted to JSON:

{
  "name": "Tom",
  "address": {
    "city": "Beijing",
    "zip": "100000"
  }
}

Method 2: Array Index

name,tags[0],tags[1]
Tom,A,B

Converted to JSON:

{
  "name": "Tom",
  "tags": ["A", "B"]
}

Method 3: Semicolon Separation

name,tags
Tom,"A;B;C"

Converted to JSON:

{
  "name": "Tom",
  "tags": ["A", "B", "C"]
}

Conversion Steps

1. Prepare the CSV

Ensure the CSV format is correct:

• First row is the header
• Fields are comma-separated
• Fields containing commas are wrapped in double quotes
• Double quotes within fields are escaped with two double quotes

2. Upload or Paste

Paste the CSV content into the conversion tool, or upload a CSV file.

3. Configure Options

• Whether to recognize data types
• How to handle nested structures
• Output format (array or line-by-line objects)

4. Convert

The tool converts automatically and outputs JSON.

5. Validate

Check whether the JSON result meets expectations:

• Are the fields correct?
• Are the types correct?
• Is the nesting correct?

Common Issues

Fields Containing Commas

CSV fields containing commas must be wrapped in double quotes:

name,desc
Tom,"Hello, world"

Without wrapping, the field is treated as two fields, causing a conversion error.

Chinese Character Garbling

CSV should use UTF-8 encoding. Excel may default to GBK—choose UTF-8 when exporting.

Empty Value Handling

Should empty CSV cells become empty strings "" or null in JSON?

• Default: empty string ""
• Smart recognition: null
• Depends on the tool's options

Number Precision

Large numbers may lose precision when converted to JSON. For example, an 18-digit ID number becomes scientific notation when treated as a number. ID numbers should be handled as strings (add a leading quote in CSV or specify string type during conversion).

Date Formats

CSV date formats are inconsistent (2024-01-01, 2024/1/1, Jan 1 2024). Keep them as strings in JSON and parse on the front end.

BOM Header

CSV exported from Excel may contain a BOM header (\uFEFF), causing an invisible character before the first field name. The conversion tool should automatically remove the BOM.

Conversion Tips

Preprocess the CSV

Clean up in Excel before converting:

• Delete empty rows
• Unify date formats
• Check field completeness

Selective Conversion

Only convert the columns you need. Delete unnecessary columns from the CSV to reduce JSON size.

Batch Conversion

Very large CSV files may lag when converted all at once. Convert in batches of a few hundred rows each.

Validate Results

After conversion, beautify with a JSON formatting tool and manually spot-check a few entries to confirm correctness.

Convert with DocsAll

CSV to JSON tool:

1. Upload a CSV file or paste CSV content
2. The tool automatically parses headers and data
3. Configure conversion options (type recognition, etc.)
4. Generate JSON
5. Copy or download the result

Conversion runs in the browser—CSV data is not uploaded to a server, making it suitable for sensitive data.

Reverse Conversion: JSON to CSV

Sometimes you need the reverse—converting JSON to CSV:

• API returns JSON, export to Excel for analysis
• Export database JSON fields to a spreadsheet

Challenges of JSON to CSV:

• Nested structures need to be flattened
• Array fields need handling (concatenation or expansion to multiple rows)
• Missing fields need to be padded with empty values

Summary

The keys to CSV to JSON conversion are header-to-key mapping, data type recognition, and nested structure handling. Watch out for fields with commas, Chinese encoding, empty values, and large number precision. DocsAll CSV to JSON tool runs in the browser—no data leaves your device.