Python Script To Convert Excel To Json

In today's data-driven world, seamlessly moving information between different formats is crucial. One common challenge is transitioning data from the familiar spreadsheet world of Excel to the versatile realm of JSON. This blog post dives into a simple yet powerful Python script that accomplishes this transformation with ease.

Introduction:

Excel and JSON are two ubiquitous data formats, each serving distinct purposes. Excel excels at organizing and visualizing tabular data, while JSON shines in its lightweight, human-readable, and machine-friendly structure. Situations often arise where data needs to be transferred from Excel to JSON, be it for feeding APIs, building web applications, or simply sharing data with colleagues. Manually copying and pasting can be tedious and error-prone, making automation a desirable solution.

 

The Script:

Here's a Python script utilizing the popular Pandas library to convert an Excel file to JSON:

Python

import pandas as pd

# Define the input and output file paths
excel_file = "data.xlsx"
json_file = "data.json"

# Read the Excel file as a Pandas DataFrame
df = pd.read_excel(excel_file)

# Specify the desired JSON orientation (records for rows, columns for columns)
orient = "records"

# Convert the DataFrame to a JSON string
json_string = df.to_json(orient=orient, indent=4)

# Write the JSON string to a file
with open(json_file, "w") as f:
    f.write(json_string)

print(f"Excel file converted to JSON and saved as '{json_file}'.")

 

Code Explanation:

1. Import libraries: We import the Pandas library for data manipulation and file reading/writing capabilities.
2. Define file paths: Specify the paths to the input Excel file and the desired output JSON file.
3. Read Excel file: Use pd.read_excel to read the Excel file and store the data in a Pandas DataFrame object.
4. Set JSON orientation: Choose the desired structure of the JSON data. The records option creates an array of objects with each row as an object, while columns creates an object with each column as a key and its values as an array.
5. Convert to JSON: Use the DataFrame's to_json method to convert it to a JSON string, specifying the chosen orientation and optional indentation for readability.
6. Write to file: Open the output JSON file in write mode and write the generated JSON string to it.
7. Print confirmation: Print a message confirming the successful conversion and output file name.

 

Applications:

This script has numerous practical applications:

• Data exchange: Easily share Excel data with applications or APIs that require JSON input.
• API development: Build JSON-based APIs by extracting data from existing Excel datasets.
• Data analysis: Convert Excel data for further processing in Python or other data analysis tools.
• Web development: Integrate Excel data into web applications through JSON APIs.

 

Conclusion:

Converting Excel to JSON with Python is a simple yet powerful task. This script provides a robust foundation for automating your data transformation needs, freeing you from tedious manual work and enabling seamless data flow between different formats. So, unleash the power of Python and let your spreadsheets sing in the language of JSON!