如何正确将无表头CSV批量转换为指定格式JSON？

问题背景 #

需要将大量无表头的CSV文件转换为JSON，每行对应一个TTF字体的属性数据。现有代码生成结果不符合预期，核心问题包括：

• 错误将CSV第一行数据作为表头
• 重复写入JSON内容
• 未按文件路径:属性名的格式生成键

CSV示例 #

Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf,Roboto,Bold,Roboto Bold,Roboto-Bold,Version 2.137; 2017,Roboto Bold,Google,Christian Robertson,Google.com,Roboto is a trademark of Google.,Copyright 2011 Google Inc. All Rights Reserved.,http://www.apache.org/licenses/LICENSE-2.0,,GOOG
Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf,Roboto Medium,Regular,Roboto Medium,Roboto-Medium,Roboto,,Version 2.137; 2017,Roboto Medium,Google,Christian Robertson,Google.com,Roboto is a trademark of Google.,Copyright 2011 Google Inc. All Rights Reserved.,http://www.apache.org/licenses/LICENSE-2.0,,GOOG

当前错误结果 #

[
    {
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf": "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf",
        "Roboto": "Roboto Medium",
        "Bold": "Regular",
        "Roboto Bold": "",
        "Roboto-Bold": "Roboto-Medium",
        "Version 2.137; 2017": "Roboto",
        "Google": "Version 2.137; 2017",
        "Christian Robertson": "Roboto Medium",
        "Google.com": "Google",
        "Roboto is a trademark of Google.": "Christian Robertson",
        "Copyright 2011 Google Inc. All Rights Reserved.": "Google.com",
        "http://www.apache.org/licenses/LICENSE-2.0": "Roboto is a trademark of Google.",
        "": "Copyright 2011 Google Inc. All Rights Reserved.",
        "GOOG": "http://www.apache.org/licenses/LICENSE-2.0"
    }
][{"Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf": "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf", "Roboto": "Roboto Medium", "Bold": "Regular", "Roboto Bold": "", "Roboto-Bold": "Roboto-Medium", "Version 2.137; 2017": "Roboto", "Google": "Version 2.137; 2017", "Christian Robertson": "Roboto Medium", "Google.com": "Google", "Roboto is a trademark of Google.": "Christian Robertson", "Copyright 2011 Google Inc. All Rights Reserved.": "Google.com", "http://www.apache.org/licenses/LICENSE-2.0": "Roboto is a trademark of Google.", "": "Copyright 2011 Google Inc. All Rights Reserved.", "GOOG": "http://www.apache.org/licenses/LICENSE-2.0"}]

预期JSON格式 #

[
    {
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:Family": "Roboto",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:Subfamily": "Bold",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:Full name": "Roboto Bold",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:PostScript name": "Roboto-Bold",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:Preferred family": "Version 2.137; 2017",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:Preferred subfamily": "Roboto Bold",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:Mac font menu name": "Google",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:Version": "Christian Robertson",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:Manufacturer": "Google.com",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:Designer": "Roboto is a trademark of Google.",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:Designer URL": "Copyright 2011 Google Inc. All Rights Reserved.",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:Trademark": "http://www.apache.org/licenses/LICENSE-2.0",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:Copyright": "",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Bold_0.ttf:License URL": "GOOG"
    },
    {
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:Family": "Roboto Medium",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:Subfamily": "Regular",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:Full name": "Roboto Medium",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:PostScript name": "Roboto-Medium",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:Preferred family": "Roboto",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:Preferred subfamily": "",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:Mac font menu name": "Version 2.137; 2017",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:Version": "Roboto Medium",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:Manufacturer": "Google",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:Designer": "Christian Robertson",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:Designer URL": "Google.com",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:Trademark": "Roboto is a trademark of Google.",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:Copyright": "Copyright 2011 Google Inc. All Rights Reserved.",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:License URL": "http://www.apache.org/licenses/LICENSE-2.0",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:License": "",
        "Assets/Lakeside/Resources/Graphics/font_ttf/Roboto-Medium_0.ttf:Vendor ID": "GOOG"
    }
]

代码修正说明 #

1. 移除未使用的依赖库，精简代码
2. 定义属性名列表，与CSV列一一对应（需根据实际需求调整）
3. 使用csv.reader读取无表头CSV，避免将第一行误作为表头
4. 按文件路径:属性名格式构造每个字体的属性键
5. 修复重复写入JSON的问题，仅写入一次格式化后的内容

修正后的代码 #

import csv
import json
from pathlib import Path

# 定义与CSV列对应的属性名（需根据实际需求调整顺序和名称）
PROPERTY_NAMES = [
    "Family",
    "Subfamily",
    "Full name",
    "PostScript name",
    "Preferred family",
    "Preferred subfamily",
    "Mac font menu name",
    "Version",
    "Manufacturer",
    "Designer",
    "Designer URL",
    "Trademark",
    "Copyright",
    "License URL",
    "License",
    "Vendor ID"
]

def read_CSV(file, json_file):
    font_objects = []
    with open(file, 'r', encoding='utf-8') as csvfile:
        reader = csv.reader(csvfile)
        for row in reader:
            if not row:
                continue  # 跳过空行
            file_path = row[0]
            font_attrs = {}
            # 遍历属性名和对应的值
            for idx, prop_name in enumerate(PROPERTY_NAMES):
                if idx < len(row) - 1:  # 确保不越界
                    value = row[idx + 1].strip()
                else:
                    value = ""
                key = f"{file_path}:{prop_name}"
                font_attrs[key] = value
            font_objects.append(font_attrs)
        convert_write_json(font_objects, json_file)

def convert_write_json(data, json_file):
    with open(json_file, 'w', encoding='utf-8') as f:
        json.dump(data, f, ensure_ascii=False, indent=4)

# 遍历指定文件夹下的所有CSV文件
target_folder = "/users/user/CSVtoGD/"
csv_files = Path(target_folder).glob('*.csv')

for csv_file in csv_files:
    json_file_path = str(csv_file.with_suffix('.json'))
    read_CSV(csv_file, json_file_path)

注意事项 #

• 请根据实际CSV列的含义调整PROPERTY_NAMES列表的顺序和名称，确保与CSV数据一一对应
• 代码添加了空行跳过逻辑，避免处理无效行
• 使用utf-8编码读写文件，避免中文或特殊字符乱码

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