DEV Community

WTSolutions
WTSolutions

Posted on

Understanding Excel Data Formats - What Excel to JSON Supports

Welcome to part 5 of our Excel to JSON series! 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 Excel data formats Excel to JSON supports and how to prepare your data for conversion.

The Excel Data Requirements

Excel to JSON is designed to handle a wide variety of Excel data 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, Excel to JSON requires:

  1. Tab-Separated or Comma-Separated Data: Input must be in Excel format (tab-separated) or CSV format (comma-separated)
  2. At Least Two Rows: First row is treated as header, subsequent rows as data
  3. No Empty Header Cells: Header row cannot contain empty cells
  4. Consistent Data Types: Each column should have consistent data types

Supported Data Sources

Excel to JSON accepts data from multiple sources:

1. Direct Excel Data (Tab-Separated)

This is the most common input format. When you copy data from Excel, it's tab-separated:

Example:

Name    Age Company
John Doe    25  WTSolutions
Jane Smith  30  Microsoft
Enter fullscreen mode Exit fullscreen mode

Characteristics:

  • Separated by tab characters (\t)
  • First row becomes JSON keys
  • Subsequent rows become JSON values
  • Preserves data types (numbers, booleans, dates, strings)

2. CSV Data (Comma-Separated)

Excel to JSON also accepts comma-separated values:

Example:

Name,Age,Company
John Doe,25,WTSolutions
Jane Smith,30,Microsoft
Enter fullscreen mode Exit fullscreen mode

Characteristics:

  • Separated by commas (,)
  • First row becomes JSON keys
  • Subsequent rows become JSON values
  • Handles quoted values with commas inside

3. Excel Files (.xlsx)

When using the API or Excel/WPS add-ins with file loading, you can convert entire Excel files:

Characteristics:

  • Each sheet is converted to a separate JSON object
  • Each JSON object has sheetName and data properties
  • data property contains array of row objects
  • Supports multiple sheets in single conversion

Supported Data Types

Excel to JSON handles all standard Excel data types:

1. Strings

Text values in Excel are converted to JSON strings:

Excel:
| Name |
|------------|
| John Doe |

JSON:

{
  "Name": "John Doe"
}
Enter fullscreen mode Exit fullscreen mode

2. Numbers

Numeric values in Excel are converted to JSON numbers:

Excel:
| Age |
|-----|
| 25 |

JSON:

{
  "Age": 25
}
Enter fullscreen mode Exit fullscreen mode

3. Booleans

Excel boolean values (TRUE/FALSE) are converted based on your settings:

Excel:
| IsStudent |
|-----------|
| TRUE |

JSON (using JSON true/false format):

{
  "IsStudent": true
}
Enter fullscreen mode Exit fullscreen mode

JSON (using Number 1/0 format):

{
  "IsStudent": 1
}
Enter fullscreen mode Exit fullscreen mode

JSON (using String format):

{
  "IsStudent": "TRUE"
}
Enter fullscreen mode Exit fullscreen mode

4. Dates

Date values in Excel can be converted in multiple ways:

Excel:
| Birthday |
|-----------|
| 1995-05-15 |

JSON (using Number of Days format):

{
  "Birthday": 34834
}
Enter fullscreen mode Exit fullscreen mode

JSON (using ISO 8601 format):

{
  "Birthday": "1995-05-15T00:00:00.000Z"
}
Enter fullscreen mode Exit fullscreen mode

Note: To use ISO 8601 format, add $date$ suffix to your column header:
| Birthday$date$ |
|---------------|
| 1995-05-15 |

5. Empty Cells

Empty cells can be handled in three ways:

Excel:
| Name | Age |
|------|-----|
| John | 25 |
| Jane | |

JSON (using Empty String format):

[
  {
    "Name": "John",
    "Age": 25
  },
  {
    "Name": "Jane",
    "Age": ""
  }
]
Enter fullscreen mode Exit fullscreen mode

JSON (using JSON Null format):

[
  {
    "Name": "John",
    "Age": 25
  },
  {
    "Name": "Jane",
    "Age": null
  }
]
Enter fullscreen mode Exit fullscreen mode

JSON (using Exclude format):

[
  {
    "Name": "John",
    "Age": 25
  },
  {
    "Name": "Jane"
  }
]
Enter fullscreen mode Exit fullscreen mode

Header Options

Excel to JSON offers two ways to identify headers:

Option 1: First Row as Header (Default)

The first row is treated as header row:

Excel:
| Name | Age | Company |
|------------|-----|-------------|
| John Doe | 25 | WTSolutions |
| Jane Smith | 30 | Microsoft |

JSON:

[
  {
    "Name": "John Doe",
    "Age": 25,
    "Company": "WTSolutions"
  },
  {
    "Name": "Jane Smith",
    "Age": 30,
    "Company": "Microsoft"
  }
]
Enter fullscreen mode Exit fullscreen mode

Option 2: First Column as Header (Pro Feature)

The first column is treated as header column:

Excel:
| Attribute | John Doe | Jane Smith |
|----------|----------|-----------|
| Name | John | Jane |
| Age | 25 | 30 |
| Company | WTSolutions | Microsoft |

JSON:

{
  "Name": "John",
  "Age": 25,
  "Company": "WTSolutions"
}
Enter fullscreen mode Exit fullscreen mode

Valid Excel Data Examples

Example 1: Simple Flat Data

Excel:
| Name | Age | Company |
|------------|-----|-------------|
| John Doe | 25 | WTSolutions |
| Jane Smith | 30 | Microsoft |

✅ Valid - Simple structure with consistent data types

Example 2: Nested Column Headers

Excel:
| id | student.name | student.familyname | student.age |
|----|--------------|--------------------|-------------|
| 1 | Meimei | Han | 12 |
| 2 | Lily | Jaskson | 15 |

✅ Valid - Column headers indicate nested structure (use Nested JSON Mode)

Example 3: Multiple Data Types

Excel:
| Name | Age | IsStudent | Birthday |
|------------|-----|-----------|------------|
| John Doe | 25 | TRUE | 1995-05-15 |

✅ Valid - Mix of strings, numbers, booleans, and dates

Invalid Excel Data Examples

Example 1: Single Row

Excel:
| Name | Age |
|------|-----|

❌ Invalid - Only one row (need header + data)

Example 2: Empty Header Cell

Excel:
| Name | | Age |
|------|---|-----|
| John | 25 | 30 |

❌ Invalid - Header row contains empty cell

Example 3: No Data

Excel:
| Name | Age |
|------|-----|

❌ Invalid - Only header row, no data rows

Preparing Your Excel Data for Conversion

Step 1: Verify Your Data

Before converting, ensure your Excel data meets requirements:

  1. Check Row Count: At least two rows (header + data)
  2. Check Header Row: No empty cells in header row
  3. Check Data Types: Consistent data types in columns
  4. Check Structure: Proper tab or comma separation

Step 2: Organize Your Data

Organize your Excel data for best results:

  1. Use Descriptive Headers: Clear, meaningful column names
  2. Consistent Formatting: Uniform date formats, number formats, etc.
  3. Remove Unnecessary Rows: Delete blank rows or summary rows
  4. Check for Merged Cells: Unmerge cells before conversion

Step 3: Handle Special Cases

Prepare for special data scenarios:

  1. Nested Structures: Use dot notation in headers (e.g., student.name)
  2. Date Columns: Add $date$ suffix for ISO 8601 format
  3. Boolean Columns: Ensure consistent TRUE/FALSE values
  4. Empty Values: Decide how to handle empty cells

Best Practices

1. Consistent Naming

Use consistent naming conventions:

  • CamelCase: firstName, lastName
  • Snake_case: first_name, last_name
  • PascalCase: FirstName, LastName

2. Data Type Consistency

Maintain consistent data types in columns:

  • Don't mix numbers and text in same column
  • Use consistent date formats
  • Standardize boolean values (TRUE/FALSE)

3. Clean Data

Clean your Excel data before conversion:

  • Remove leading/trailing spaces
  • Standardize text case (uppercase/lowercase)
  • Fix spelling errors
  • Remove duplicate rows

4. Test with Sample Data

Test conversion with sample data first:

  • Convert a small subset of your data
  • Verify the JSON output
  • Adjust Excel data if needed
  • Convert full dataset once satisfied

Common Issues and Solutions

Issue 1: Wrong Data Type Detection

Problem: Numbers are being converted to strings.

Solution: Ensure cells are formatted as numbers in Excel, not text.

Issue 2: Date Format Issues

Problem: Dates are not converting correctly.

Solution:

  • Check Excel date format
  • Use $date$ suffix for ISO 8601 format
  • Verify date is not before 1900-01-01

Issue 3: Empty Cells in Header

Problem: Conversion fails due to empty header cells.

Solution: Fill in all header cells or remove empty columns.

Issue 4: Inconsistent Data Types

Problem: Mixed data types in same column.

Solution: Standardize data types or use text format for mixed columns.

Next Steps

Now that you understand what Excel data formats are supported, you're ready to dive deeper into 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 Excel data? Visit the Excel to JSON Web App to try it out!

Top comments (0)