DEV Community

WTSolutions
WTSolutions

Posted on

MCP Service Integration - Excel to JSON for AI and Automation

#ai #automation #mcp #tutorial

Welcome to part 9 of our Excel to JSON series! We've covered the API for programmatic access, and today we're exploring MCP Service - an advanced option for developers working with AI tools and automation platforms.

What is MCP?

MCP (Model Context Protocol) is an open protocol that enables AI assistants and automation tools to interact with external services and data sources. The Excel to JSON MCP Service allows AI models and automation platforms to convert Excel to JSON format seamlessly within their workflows.

Why Use MCP Service?

The MCP Service is ideal for:

  • AI Assistants: Enable Claude, GPT, and other AI models to convert Excel to JSON
  • Automation Platforms: Integrate with n8n, Make.com, and other automation tools
  • Custom Workflows: Build automated data processing pipelines
  • Developer Tools: Create custom integrations with MCP-compatible platforms
  • No-Code/Low-Code Solutions: Enable non-technical users to automate conversions

Getting Started with MCP Service

Installation

The Excel to JSON MCP Service is available as an npm package:

npm install -g @wtsolutions/excel-to-json-mcp
Enter fullscreen mode Exit fullscreen mode

GitHub Repository

Full documentation and source code are available at:
https://github.com/he-yang/excel-to-json-mcp

Requirements

  • Node.js 14 or higher
  • npm or yarn package manager
  • Valid Pro Code for advanced features

MCP Service Configuration

Basic Setup

After installation, you'll need to configure MCP Service for your specific use case. The configuration typically includes:

  1. Server Connection Details

    • Host and port settings
    • Authentication credentials (if required)

  2. Pro Code Configuration

    • Your email address as Pro Code
    • Enables Pro features like custom delimiters and format options

  3. Conversion Settings

    • Default conversion mode (flat/nested)
    • Default delimiter choice
    • Default header option (row/column)

Example Configuration 

{
  "mcpServers": {
    "excel-to-json": {
      "command": "npx",
      "args": [
        "-y",
        "@wtsolutions/excel-to-json-mcp"
      ],
      "env": {
        "PRO_CODE": "your-email@example.com"
      }
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Using MCP with AI Assistants

Claude Desktop Integration

Claude Desktop supports MCP servers natively. Here's how to integrate Excel to JSON:

Step 1: Configure Claude Desktop

  1. Open Claude Desktop settings
  2. Navigate to MCP Servers section
  3. Add Excel to JSON MCP Server configuration
  4. Enter your Pro Code if you have one

Step 2: Use in Conversation

Once configured, you can ask Claude to convert Excel to JSON:

User: I have this Excel data about sales:

| Product | Price | Quantity |
|---------|-------|----------|
| Laptop  | 999.99 | 50       |
| Mouse   | 29.99  | 200      |

Can you convert this to JSON format?

Claude: I'll use the Excel to JSON MCP server to convert this data for you.

[Processing...]

Done! I've converted your Excel data to JSON format. Here's the result:
Enter fullscreen mode Exit fullscreen mode


json
[
{
"Product": "Laptop",
"Price": 999.99,
"Quantity": 50
},
{
"Product": "Mouse",
"Price": 29.99,
"Quantity": 200
}
]

Enter fullscreen mode Exit fullscreen mode

Other AI Platforms

The MCP Service can be integrated with other AI platforms that support MCP:

  • Claude API: Direct integration in your applications
  • Custom AI Solutions: Build your own AI assistants with MCP support
  • Enterprise AI Platforms: Integrate with enterprise AI systems

Automation Workflows

n8n Integration

n8n is a powerful automation platform that supports MCP. Here's how to use Excel to JSON in n8n workflows:

Workflow Example: Excel to JSON to Database

  1. Webhook Trigger: Trigger on schedule or external event
  2. Excel Data Node: Read Excel file from local storage or HTTP
  3. MCP Server Node: Convert Excel to JSON using MCP Service
  4. Database Node: Save JSON data to database
  5. Notification Node: Send success notification

Configuration Steps

  1. Add MCP Server node to your n8n workflow
  2. Select "excel-to-json" as the server
  3. Configure input data (Excel file or data)
  4. Set conversion options (flat/nested, delimiter, etc.)
  5. Connect to output nodes (database, API, etc.)

Make.com Integration

Make.com (formerly Integromat) also supports MCP servers:

Scenario: Automated Report Generation

  1. Google Sheets: Read Excel data from Google Sheets
  2. MCP Server: Convert to JSON using Excel to JSON MCP Service
  3. Format Data: Apply formatting and calculations
  4. Email: Send formatted report as email
  5. Slack: Notify team that report is ready

Custom MCP Client Implementation

For developers who want to build custom MCP clients:

Python MCP Client 

import asyncio
from mcp import ClientSession, StdioServerParameters
from mcp.client.stdio import stdio_client

async def convert_excel_to_json(excel_data):
    # Create server parameters
    server_params = StdioServerParameters(
        command="npx",
        args=["-y", "@wtsolutions/excel-to-json-mcp"],
        env={"PRO_CODE": "your-email@example.com"}
    )

    # Connect to MCP server
    async with stdio_client(server_params) as (read, write):
        async with ClientSession(read, write) as session:
            # Initialize session
            await session.initialize()

            # Call conversion tool
            result = await session.call_tool(
                "convert_excel_to_json",
                arguments={
                    "data": excel_data,
                    "options": {
                        "jsonMode": "nested",
                        "delimiter": ".",
                        "header": "row"
                    }
                }
            )

            return result

# Usage
import json

excel_data = "Name\tAge\tCompany\nJohn Doe\t25\tWTSolutions\nJane Smith\t30\tMicrosoft"

result = asyncio.run(convert_excel_to_json(excel_data))
print(result)
Enter fullscreen mode Exit fullscreen mode

JavaScript MCP Client 

const { Client } = require('@modelcontextprotocol/sdk/client/index.js');
const { StdioClientTransport } = require('@modelcontextprotocol/sdk/client/stdio.js');

async function convertExcelToJson(excelData) {
  // Create transport
  const transport = new StdioClientTransport({
    command: 'npx',
    args: ['-y', '@wtsolutions/excel-to-json-mcp'],
    env: {
      PRO_CODE: 'your-email@example.com'
    }
  });

  // Create client
  const client = new Client({
    name: 'excel-to-json-client',
    version: '1.0.0'
  }, {
    capabilities: {}
  });

  try {
    // Connect to server
    await client.connect(transport);

    // Call conversion tool
    const result = await client.callTool({
      name: 'convert_excel_to_json',
      arguments: {
        data: excelData,
        options: {
          jsonMode: 'nested',
          delimiter: '.',
          header: 'row'
        }
      }
    });

    return result;
  } finally {
    await client.close();
  }
}

// Usage
const excelData = 'Name\tAge\tCompany\nJohn Doe\t25\tWTSolutions\nJane Smith\t30\tMicrosoft';

convertExcelToJson(excelData)
  .then(result => console.log(result))
  .catch(error => console.error(error));
Enter fullscreen mode Exit fullscreen mode

Advanced MCP Features

Custom Tool Definitions

The Excel to JSON MCP Service exposes tools that can be called by MCP clients:

Tool: convert_excel_to_json

Description: Converts Excel data to JSON format

Parameters:

  • data (string, required): Excel data to convert
  • options (object, optional): Conversion options
    • jsonMode (string): "flat" or "nested"
    • header (string): "row" or "column"
    • delimiter (string): ".", "", "_", or "/"
    • emptyCell (string): "emptyString", "null", or "exclude"
    • booleanFormat (string): "trueFalse", "10", or "string"
    • jsonFormat (string): "arrayOfObject" or "2DArray"
    • singleObjectFormat (string): "array" or "object"

Returns: JSON data or conversion result

Error Handling

The MCP Service provides detailed error information:

{
  "success": false,
  "error": {
    "code": "INVALID_DATA",
    "message": "The provided Excel data is not valid",
    "details": "At least 2 rows are required"
  }
}
Enter fullscreen mode Exit fullscreen mode

Logging and Debugging

Enable detailed logging for troubleshooting:

DEBUG=mcp:* npx -y @wtsolutions/excel-to-json-mcp
Enter fullscreen mode Exit fullscreen mode

Use Cases

Use Case 1: AI-Powered Data Analysis

Scenario: Use Claude to analyze Excel data and create JSON for APIs

Workflow:

  1. User provides Excel data to Claude
  2. Claude uses MCP Service to convert to JSON
  3. Claude analyzes the JSON data
  4. Claude provides insights and recommendations
  5. User exports JSON for API integration

Benefits:

  • Seamless AI integration
  • No manual conversion steps
  • Faster data-to-insights pipeline
  • Better data understanding

Use Case 2: Automated Data Pipeline

Scenario: Daily automated conversion of Excel reports to JSON for downstream systems

Workflow:

  1. Cron job triggers at scheduled time
  2. Script reads Excel file from network share
  3. MCP Service converts to JSON
  4. JSON file is uploaded to cloud storage
  5. Downstream systems process JSON data
  6. Team receives notification

Benefits:

  • Fully automated process
  • No manual intervention required
  • Reliable, scheduled execution
  • Integration with existing systems

Use Case 3: Real-Time Data Processing

Scenario: Convert Excel data in real-time as it arrives from multiple sources

Workflow:

  1. Webhook receives Excel data
  2. MCP Service immediately converts to JSON
  3. JSON data is processed by downstream systems
  4. Results are sent back to originator

Benefits:

  • Near-instant conversion
  • Scalable for high-volume data
  • Integrates with existing systems
  • Real-time data flow

Use Case 4: Multi-Platform Data Aggregation

Scenario: Aggregate Excel data from multiple sources and convert to unified JSON format

Workflow:

  1. Fetch Excel data from Source A, B, C
  2. Use MCP Service to convert each to JSON
  3. Merge JSON data from all sources
  4. Apply business logic and transformations
  5. Distribute unified JSON to stakeholders

Benefits:

  • Centralized data processing
  • Consistent output format
  • Easy to maintain and update
  • Better data governance

Best Practices

1. Security Considerations

  • Protect Pro Codes: Don't expose Pro Codes in client-side code
  • Validate Input: Always validate Excel data before conversion
  • Use HTTPS: Ensure secure communication channels
  • Limit Access: Restrict MCP Service access as needed

2. Performance Optimization

  • Batch Processing: Convert multiple Excel objects together when possible
  • Caching: Cache conversion results for repeated data
  • Async Operations: Use asynchronous processing for better performance
  • Resource Management: Monitor and manage server resources

3. Error Handling

  • Graceful Degradation: Handle errors without crashing workflows
  • Retry Logic: Implement retry mechanisms for transient failures
  • Logging: Log errors for debugging and monitoring
  • User Feedback: Provide clear error messages to users

4. Testing

  • Unit Tests: Test MCP client implementations thoroughly
  • Integration Tests: Test with actual MCP Service
  • Edge Cases: Test with various Excel structures
  • Load Testing: Test performance under high load

Troubleshooting

Common Issues

Issue 1: Connection Failed

  • Cause: MCP Service not running or incorrect configuration
  • Solution: Verify server is running and check configuration

Issue 2: Invalid Pro Code

  • Cause: Pro Code is incorrect or expired
  • Solution: Verify Pro Code and ensure subscription is active

Issue 3: Conversion Timeout

  • Cause: Large Excel data or server overload
  • Solution: Split data into smaller chunks or retry later

Issue 4: Invalid Excel Format

  • Cause: Input Excel data is malformed
  • Solution: Validate Excel data before sending to MCP Service

Debug Mode

Enable debug mode for detailed troubleshooting:

DEBUG=excel-to-json:* npx -y @wtsolutions/excel-to-json-mcp
Enter fullscreen mode Exit fullscreen mode

MCP vs API: Which to Choose?

Feature MCP Service API
AI Integration ✅ Native ❌ Requires wrapper
Automation Platforms ✅ Native support ❌ Requires HTTP calls
Custom Applications ✅ Standard protocol ✅ Simple HTTP
Learning Curve ⚠️ Moderate ✅ Simple
Flexibility ✅ High ✅ High
Real-time Capabilities ✅ Excellent ✅ Good

Choose MCP Service when:

  • Working with AI assistants
  • Using automation platforms with MCP support
  • Building custom AI-powered solutions
  • Need standardized protocol integration

Choose API when:

  • Building simple web applications
  • Need straightforward HTTP integration
  • Working with traditional development stacks
  • Prefer direct control over requests

Next Steps

Now that you understand MCP Service integration, you have a complete picture of all Excel to JSON tools available. In our final post, we'll explore real-world use cases and practical examples that demonstrate how organizations are using Excel to JSON to solve actual business problems.

Ready to integrate with MCP? Check out the GitHub repository for detailed documentation and examples!

Top comments (0)