如何将指定格式的CSV文件转换为JSON文件

首先看你提供的CSV内容，格式比较特殊——它不是常规的表头+多行数据结构，而是以Name作为每条记录的起始标记，每行都是单个键值对：

Name,abc
Title,teacher
Email,abc.edu
Phone,000-000-0000
Office,21building
About,"abc is teacher"
Name,def
Title,plumber
Email,plumber@plumber.com
Phone,111-111-1111
Office,22building
About,"The best plumber in the town"
Name,ghi
Title,producer
Phone,333-333-3333
Office,33building
About,"The best producer"

下面提供两种实用的转换方法：

方法一：Python脚本转换 #

用Python处理这种自定义格式的CSV最灵活，还能自动处理带引号的字段：

import csv
import json

def csv_to_json(csv_file_path, json_file_path):
    data_list = []
    current_record = {}
    
    # 读取CSV文件，用csv.reader处理带引号的字段
    with open(csv_file_path, 'r', encoding='utf-8') as csv_file:
        csv_reader = csv.reader(csv_file)
        for row in csv_reader:
            if not row:
                continue  # 跳过空行
            key, value = row[0], row[1]
            if key == 'Name':
                # 遇到新的Name，先把之前的记录存入列表（如果存在）
                if current_record:
                    data_list.append(current_record)
                # 初始化新记录
                current_record = {'Name': value}
            else:
                # 把键值对加入当前记录
                current_record[key] = value
        # 把最后一条记录加入列表
        if current_record:
            data_list.append(current_record)
    
    # 写入格式化后的JSON文件
    with open(json_file_path, 'w', encoding='utf-8') as json_file:
        json.dump(data_list, json_file, indent=2, ensure_ascii=False)

# 替换成你的实际文件路径
csv_to_json('input.csv', 'output.json')

运行后得到的JSON格式如下：

[
  {
    "Name": "abc",
    "Title": "teacher",
    "Email": "abc.edu",
    "Phone": "000-000-0000",
    "Office": "21building",
    "About": "abc is teacher"
  },
  {
    "Name": "def",
    "Title": "plumber",
    "Email": "plumber@plumber.com",
    "Phone": "111-111-1111",
    "Office": "22building",
    "About": "The best plumber in the town"
  },
  {
    "Name": "ghi",
    "Title": "producer",
    "Phone": "333-333-3333",
    "Office": "33building",
    "About": "The best producer"
  }
]

方法二：命令行工具组合（awk + jq） #

如果习惯用命令行，可以用awk拼接JSON结构，再用jq格式化输出：

awk -F, '
    BEGIN {print "["}
    $1 == "Name" {
        if (record != "") print record ","
        record = "{\"Name\":\"" $2 "\""
    }
    $1 != "Name" {
        gsub(/"/, "\\\"", $2)  # 转义字段中的双引号，避免JSON语法错误
        record = record ",\"" $1 "\":\"" $2 "\""
    }
    END {
        print record "}"
        print "]"
    }
' input.csv | jq . > output.json

这个命令会先通过awk把每条记录的键值对拼接成JSON对象，再用jq修正格式并确保JSON结构合法，最终输出到output.json文件。

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