如何用Python将CSV/Excel转换为指定格式的JSON

输入数据示例 #

目标JSON格式 #

{
    "Category":"Phone",
    "listofproducts" :[
        {
            "code": "234",
            "brand": "Samsung",
            "model": "Galaxy S21",
            "size": "S",
            "capacity": "500 GB"
        },
        {
            "code": "442",
            "brand": "Apple",
            "model": "iPhone 12",
            "size": "P",
            "capacity": "256 GB"
        },
        {
            "code": "112",
            "brand": "Apple",
            "model": "iPhone 13",
            "size": "P",
            "capacity": "256 GB"
        },
        {
            "code": "453",
            "brand": "Apple",
            "model": "iPhone 14",
            "size": "P",
            "capacity": "256 GB"
        }
    ]
}

方法一：用Pandas处理（支持CSV和XLSX） #

Pandas可同时处理两种格式文件，先安装依赖：

pip install pandas openpyxl

完整代码：

import pandas as pd
import json

def convert_file_to_json(input_path, output_path):
    # 根据文件后缀读取数据
    if input_path.endswith('.csv'):
        df = pd.read_csv(input_path)
    elif input_path.endswith('.xlsx'):
        df = pd.read_excel(input_path, engine='openpyxl')
    else:
        raise ValueError("仅支持.csv或.xlsx格式")
    
    # 提取分类（假设所有行分类一致）
    category = df['Category'].iloc[0]
    
    # 构造产品列表，将code转为字符串类型
    product_list = []
    for _, row in df.iterrows():
        product = {
            "code": str(row['code']),
            "brand": row['brand'],
            "model": row['model'],
            "size": row['size'],
            "capacity": row['capacity']
        }
        product_list.append(product)
    
    # 组装目标JSON结构并写入文件
    result = {
        "Category": category,
        "listofproducts": product_list
    }
    
    with open(output_path, 'w', encoding='utf-8') as f:
        json.dump(result, f, indent=4, ensure_ascii=False)

# 调用示例
convert_file_to_json('products.csv', 'output.json')
# convert_file_to_json('products.xlsx', 'output.json')  # 处理Excel文件

方法二：用原生CSV模块处理CSV文件 #

若仅需处理CSV，用Python内置模块更轻量：

import csv
import json

def csv_to_target_json(csv_path, output_path):
    with open(csv_path, 'r', encoding='utf-8') as f:
        reader = csv.DictReader(f)
        rows = list(reader)
    
    if not rows:
        raise ValueError("CSV文件无数据")
    
    category = rows[0]['Category']
    product_list = []
    for row in rows:
        product = {
            "code": row['code'],
            "brand": row['brand'],
            "model": row['model'],
            "size": row['size'],
            "capacity": row['capacity']
        }
        product_list.append(product)
    
    result = {
        "Category": category,
        "listofproducts": product_list
    }
    
    with open(output_path, 'w', encoding='utf-8') as f:
        json.dump(result, f, indent=4, ensure_ascii=False)

# 调用示例
csv_to_target_json('products.csv', 'output.json')

注意事项 #

• 若CSV文件分隔符不是逗号，可在pd.read_csv中添加sep='分隔符'参数（如sep='\t'处理制表符分隔）
• 若Excel文件包含多个工作表，需在pd.read_excel中指定sheet_name='工作表名'
• 若数据存在缺失值，可提前用df.fillna('')填充空字符串，避免JSON出现null

内容的提问来源于stack exchange，提问作者MIMI