Excel-to-JSON Add-in Pro Features: Enhanced Excel Data Conversion

Introduction

Converting between Excel and JSON formats is a common requirement in data processing and system integration. The Excel-to-JSON add-in offers a powerful set of professional features to handle various complex data conversion scenarios. This article will detail these pro features, their use cases, and implementation methods.

Subscription Information

Before diving into the features, here’s a brief overview of the subscription details:

• 7-day free trial available
• Multiple currency options:
  • USD: $2.66/month
  • EUR: €2.36/month
  • CNY: ¥19.90/month
  • HKD: HK$21.80/month
• Each Pro Code supports up to 10 devices
• Valid for both Excel-to-JSON and JSON-to-Excel add-ins

Core Features

1. Nested JSON Structure Support

Use Cases

When dealing with complex data structures, you often need to convert Excel data into multi-level JSON objects. For example:

• User Information Management: Including basic info, contact details, address
• Product Catalogs: Categories, specifications, pricing
• Organizational Structure: Departments, teams, members

Implementation

The add-in supports three delimiters for defining JSON hierarchy:

• Dot (.) - Example: user.name, user.contact.phone
• Underscore (_) - Example: user_name, user_contact_phone
• Forward Slash (/) - Example: user/name, user/contact/phone

Practical Example

Consider building a JSON structure containing user information:

Resulting JSON:

[{
    "id": 1,
    "user": {
        "name": "John",
        "contact": {
            "phone": "13800138000"
        },
        "address": {
            "city": "New York"
        }
    }
}]

2. Empty Cell Handling

Use Cases

• Data Cleaning: Processing incomplete datasets
• System Integration: Meeting different systems’ empty value requirements
• Data Analysis: Controlling how null values affect analysis

Three Processing Options

1. Convert to empty string
2. Convert to null
3. Completely exclude from JSON

3. Boolean Format

Use Cases

• Configuration File Generation: Requiring strict true/false format
• Data Visualization: Using 1/0 for status representation
• System Integration: Meeting various system format requirements

Supported Formats

1. JSON boolean (true/false)
2. String (“true”/“false”)
3. Number (1/0)

4. Date Format Conversion

Use Cases

• Cross-system Data Migration: Unifying date formats
• International Applications: Handling different timezone dates
• Data Analysis: Ensuring date consistency

Conversion Options

1. Days Format: Number of days since 1900-01-01
2. ISO 8601 Format: Standard datetime string

Tips and Best Practices

1. Data Preparation

   • Check Excel data format consistency before conversion
   • Plan JSON structure with appropriate delimiters
   • Add $date$ suffix for date columns

2. Format Selection

   • Choose empty cell handling based on target system requirements
   • Consider data readability when selecting boolean format
   • Pay attention to date format compatibility

3. Efficiency Improvement

   • Save commonly used conversion configurations as templates
   • Batch process similar data conversions
   • Utilize preview feature to validate conversion results

Video Demonstration

Official Documentation

https://excel-to-json.wtsolutions.cn/en/latest/profeatures.html

Conclusion

The pro features of the Excel-to-JSON add-in provide a flexible and powerful solution for data conversion. By properly utilizing these features, you can significantly improve data processing efficiency and accuracy.