DEV Community

WTSolutions
WTSolutions

Posted on

Understanding JSON Formats - What JSON to Excel Supports

Welcome to part 5 of our JSON to Excel series! So far, we've covered the various tools available: Web App, Excel Add-in, and WPS Add-in. Today, we're diving into the technical side by exploring exactly what JSON formats JSON to Excel supports and how to ensure your data is ready for conversion.

The JSON Format Requirements

JSON to Excel is designed to handle a wide variety of JSON structures, but it does have specific requirements. Understanding these requirements will help you prepare your data and avoid conversion errors.

Core Requirements

At its core, JSON to Excel requires:

  1. Valid JSON: The input must be syntactically correct JSON
  2. Array or Object: The top-level structure must be either an array of objects or a single object
  3. Object Properties: Each object should contain key-value pairs
  4. Data Types: Values can be strings, numbers, booleans, null, arrays, or nested objects

Supported JSON Structures

Structure 1: Array of Objects (Most Common)

This is the most common and recommended format for JSON to Excel:

[
  {
    "name": "John Doe",
    "age": 30,
    "email": "john@example.com"
  },
  {
    "name": "Jane Smith",
    "age": 25,
    "email": "jane@example.com"
  }
]
Enter fullscreen mode Exit fullscreen mode

Characteristics:

  • Wrapped in square brackets []
  • Contains 1 to 1000 objects
  • Each object represents one row in Excel
  • Each property becomes a column in Excel

Requirements:

  • Minimum: 1 object
  • Maximum: 1000 objects
  • Each object: 1 to 100 properties
  • Objects cannot be empty

Structure 2: Single Object

JSON to Excel also supports a single object:

{
  "name": "John Doe",
  "age": 30,
  "email": "john@example.com"
}
Enter fullscreen mode Exit fullscreen mode

Characteristics:

  • Wrapped in curly braces {}
  • Contains 1 to 100 properties
  • Converted to a single row in Excel

Requirements:

  • Minimum: 1 property
  • Maximum: 100 properties
  • Object cannot be empty

Supported Data Types

JSON to Excel handles all standard JSON data types:

1. Strings 

{
  "name": "John Doe",
  "city": "New York"
}
Enter fullscreen mode Exit fullscreen mode
  • Converted to text cells in Excel
  • Preserves formatting and special characters

2. Numbers 

{
  "age": 30,
  "price": 99.99,
  "quantity": 5
}
Enter fullscreen mode Exit fullscreen mode
  • Converted to numeric cells in Excel
  • Integers and decimals are both supported
  • Can be used in Excel calculations

3. Booleans 

{
  "active": true,
  "verified": false
}
Enter fullscreen mode Exit fullscreen mode
  • Converted to TRUE/FALSE in Excel
  • Can be used in logical formulas

4. Null Values 

{
  "middleName": null,
  "phone": null
}
Enter fullscreen mode Exit fullscreen mode
  • Converted to blank cells in Excel
  • Useful for missing or optional data

5. Arrays 

{
  "tags": ["javascript", "python", "sql"],
  "scores": [85, 92, 78]
}
Enter fullscreen mode Exit fullscreen mode
  • Converted to string representation: ["javascript","python","sql"]
  • Not automatically expanded (use Nested JSON Mode for complex structures)

6. Nested Objects 

{
  "user": {
    "name": "John",
    "contact": {
      "email": "john@example.com",
      "phone": "555-1234"
    }
  }
}
Enter fullscreen mode Exit fullscreen mode
  • In Flat Mode: Converted to string: {"name":"John","contact":{"email":"john@example.com","phone":"555-1234"}}
  • In Nested Mode: Flattened with delimiters: user.name, user.contact.email, user.contact.phone

Valid JSON Examples

Example 1: Simple Flat Array 

[
  {"id": 1, "name": "Product A", "price": 19.99},
  {"id": 2, "name": "Product B", "price": 29.99},
  {"id": 3, "name": "Product C", "price": 39.99}
]
Enter fullscreen mode Exit fullscreen mode

✅ Valid - Simple array of objects with consistent properties

Example 2: Objects with Different Properties 

[
  {"id": 1, "name": "John", "age": 30},
  {"id": 2, "name": "Jane", "city": "New York"}
]
Enter fullscreen mode Exit fullscreen mode

✅ Valid - Objects can have different properties; Excel will have all possible columns

Example 3: Nested Structures 

[
  {
    "id": 1,
    "customer": {
      "name": "John",
      "address": {
        "street": "123 Main St",
        "city": "New York"
      }
    }
  }
]
Enter fullscreen mode Exit fullscreen mode

✅ Valid - Nested objects work with Nested JSON Mode

Example 4: Mixed Data Types 

[
  {
    "id": 1,
    "name": "John",
    "active": true,
    "score": 95.5,
    "tags": ["vip", "premium"],
    "metadata": null
  }
]
Enter fullscreen mode Exit fullscreen mode

✅ Valid - All data types are supported

Invalid JSON Examples

Example 1: Not Wrapped in Array 

{"name": "John"},{"name": "Jane"}
Enter fullscreen mode Exit fullscreen mode

❌ Invalid - Must be wrapped in array [] or be a single object

Example 2: Empty Array 

[]
Enter fullscreen mode Exit fullscreen mode

❌ Invalid - Array must contain at least one object

Example 3: Array with Non-Object Elements 

[1, 2, 3]
Enter fullscreen mode Exit fullscreen mode

❌ Invalid - Array elements must be objects

["a", "b", "c"]
Enter fullscreen mode Exit fullscreen mode

❌ Invalid - Array elements must be objects

Example 4: Array with Empty Object 

[{}]
Enter fullscreen mode Exit fullscreen mode

❌ Invalid - Objects cannot be empty

Example 5: Array with Null 

[null]
Enter fullscreen mode Exit fullscreen mode

❌ Invalid - Array elements must be objects

Example 6: Mixed Types in Array 

[{"name": "John"}, "text"]
Enter fullscreen mode Exit fullscreen mode

❌ Invalid - All array elements must be objects

JSON Schema

For developers who want to validate their JSON programmatically, here's the JSON Schema that JSON to Excel follows:

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "oneOf": [
    {
      "type": "array",
      "minItems": 1,
      "maxItems": 1000,
      "items": {
        "type": "object",
        "minProperties": 1,
        "maxProperties": 100,
        "additionalProperties": true
      },
      "not": {
        "contains": {
          "anyOf": [
            {"type": "array"},
            {"type": "null"},
            {"type": "string"},
            {"type": "number"},
            {"type": "boolean"},
            {"type": "object", "maxProperties": 0}
          ]
        }
      }
    },
    {
      "type": "object",
      "minProperties": 1,
      "maxProperties": 100,
      "additionalProperties": true,
      "not": {
        "anyOf": [
          {"type": "array"},
          {"type": "null"},
          {"type": "string"},
          {"type": "number"},
          {"type": "boolean"},
          {"type": "object", "maxProperties": 0}
        ]
      }
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

Preparing Your JSON for Conversion

Step 1: Validate Your JSON

Before converting, ensure your JSON is valid:

  1. Check Syntax: Verify brackets, braces, and commas are correct
  2. Use a Validator: Use online tools like jsonlint.com
  3. Preview in JSON to Excel: The Web App shows a preview before conversion

Step 2: Check Structure Requirements

Verify your JSON meets the requirements:

  • Array of objects or single object
  • 1-1000 objects (for arrays)
  • 1-100 properties per object
  • No empty objects

Step 3: Handle Edge Cases

Missing Properties: If some objects lack properties that others have, that's fine - Excel will have blank cells for those values.

Nested Objects: Decide whether to use Flat or Nested mode based on your needs.

Arrays in Values: These will be converted to strings. If you need them expanded, consider restructuring your JSON.

Common Issues and Solutions

Issue 1: Too Many Objects

Problem: Your JSON has more than 1000 objects.

Solution: Split your JSON into multiple files, each with 1000 or fewer objects, then use batch processing.

Issue 2: Too Many Properties

Problem: Your objects have more than 100 properties.

Solution: Consider whether all properties are necessary. You might be able to restructure your data or split it into multiple related JSON files.

Issue 3: Deeply Nested Structures

Problem: Your JSON has very deep nesting (more than 20 levels).

Solution: Use the Max Depth setting to control how deep the conversion goes, or restructure your JSON to be less nested.

Issue 4: Invalid JSON

Problem: Your JSON has syntax errors.

Solution: Use a JSON validator to find and fix syntax errors before attempting conversion.

Best Practices

1. Consistent Property Names

Use consistent property names across objects for better Excel column organization.

2. Appropriate Data Types

Choose the right data types for your values (numbers for numeric data, booleans for true/false, etc.).

3. Reasonable Nesting

Avoid excessive nesting. If you have more than 5-6 levels, consider restructuring.

4. Clear Property Names

Use descriptive property names that will make sense as Excel column headers.

5. Validate Before Conversion

Always validate your JSON before attempting conversion to save time and avoid errors.

Next Steps

Now that you understand what JSON formats are supported, you're ready to dive deeper into the conversion modes. In our next post, we'll explore the differences between Flat and Nested JSON conversion modes and when to use each one.

Ready to convert your JSON? Visit the JSON to Excel Web App to try it out!

Top comments (0)