Merging Multiple Excel Files and Converting Excel Data to JSON with Python pandas

In Python, the pandas library provides convenient functions such as merge and concat for combining multiple Excel files. The following example shows how to use concat to merge several workbooks into one DataFrame and save the result.

import pandas as pd
import glob

# Locate all Excel files in the target folder
excel_files = glob.glob("folder_path/*.xlsx")

# Read each file into a DataFrame
excel_data = [pd.read_excel(file) for file in excel_files]

# Concatenate all DataFrames
merged_data = pd.concat(excel_data)

# Write the combined data to a new Excel file
merged_data.to_excel("merged_data.xlsx", index=False)

The glob module matches every .xlsx file in the specified directory, while pd.read_excel loads each workbook. The pd.concat function stacks the DataFrames vertically, and the to_excel method writes the final DataFrame to a new file; the index=False argument prevents row indices from being saved.

When merging all Excel files within a folder, you can also build the combined DataFrame incrementally:

import pandas as pd
import glob

excel_folder_path = "path/to/excel/folder"
excel_files = glob.glob(excel_folder_path + "/*.xlsx")

merged_data = pd.DataFrame()
for file in excel_files:
    excel_data = pd.read_excel(file)
    merged_data = pd.concat([merged_data, excel_data], ignore_index=True)

merged_data.to_excel("path/to/output/file.xlsx", index=False)

To merge multiple sheets from a single Excel workbook, first obtain the sheet names with pd.ExcelFile, read each sheet, and concatenate them:

import pandas as pd

excel_file = pd.ExcelFile("path/to/excel_file.xlsx")
sheet_names = excel_file.sheet_names
excel_data = [pd.read_excel(excel_file, sheet_name=name) for name in sheet_names]
merged_data = pd.concat(excel_data)
merged_data.to_excel("path/to/output/file.xlsx", index=False)

Finally, to convert Excel data to JSON, read the workbook into a DataFrame, transform it to a list‑of‑records dictionary, and dump it as JSON:

import pandas as pd
import json

df = pd.read_excel("path/to/excel_file.xlsx")
# Convert DataFrame to list of dicts (records orientation)
data_dict = df.to_dict(orient="records")
json_data = json.dumps(data_dict)
print(json_data)

Key points to remember: use glob for file discovery, pd.read_excel for loading, pd.concat (with ignore_index=True when needed) for merging, and to_excel or json.dumps for exporting the final result.