JSON转CSV仅输出最后一行数据的问题求助

开发者阿泽

2026-4-7

嘿，我遇到个头疼的问题，写了个把JSON转CSV的脚本，但生成的CSV里只显示最后一行数据！我尝试加循环但好像没找对方法，希望能得到大家的帮助。

我的脚本代码如下：

import json
import csv
from pathlib import Path
import os

# Path
downloads = os.path.expanduser("~/Downloads")
json_path = os.path.join(downloads, "REB_ECC_THEMIS_DECISIONNEL_20250620020005.JSON")
csv_path = os.path.join(downloads, "filtered_output.csv")

# Load json file
with open(json_path, "r", encoding="utf-8") as f:
    data = json.load(f)

def flatten_json(obj, prefix=''):
    if isinstance(obj, dict):
        for k, v in obj.items():
            yield from flatten_json(v, f"{prefix}.{k}" if prefix else k)
    elif isinstance(obj, list):
        for i, item in enumerate(obj):
            yield from flatten_json(item, prefix)
    else:
        yield f"{prefix} = {json.dumps(obj)}"

# Transform the file into a list and dict after
liste = []
for line in flatten_json(data):
    liste.append(line)

rows = []
fields = set()
result = {}
for item in liste:
    key, value = item.split("=", 1)
    result[key.strip()] = value.strip()

# Send the data into a .csv
with open(csv_path, "w", newline="") as csvfile:
    writer = csv.DictWriter(csvfile, fieldnames=result.keys())
    writer.writeheader()
    for row in [result]:
        writer.writerow(row)

问题出在：生成的CSV只保留了字典里的最后一行数据，我想要的是第一行对应第一条数据，第二行对应第二条，以此类推。

附上我的JSON文件片段：

[{
    "vbrk":{
        "vbeln":"0900997737",
        "fkart":"ZF01",
        "knumv":"0003120391",
        "erdat":"2025-06-19",
        "kunrg":"0299072264",
        "kunag":"0087691105",
        "zzcdepayspv":"FR",
        "zztyppntvte":"",
        "zzcdiatapvs":""
    },
    "vbrp":[{
        "vbrp":{
            "vbeln":"0900997737",
            "posnr":"000010",
            "fkimg":1.000,
            "aubel":"1001418224",
            "aupos":"000010",
            "matnr":"PT00",
            "prodh":"00010001006000PTXX",
            "werks":"1000",
            "zzworkstationid":"87691105001",
            "zzbegindatetimestamp":"20250603091653",
            "zzdtdebuval":"2025-06-04",
            "zzdtfinvali":"2025-06-04",
            "zznurelzona":"",
            "zznumrelakm":"",
            "zzcdectrain":"",
            "zznumetrain":"843921",
            "zznbvoyageu":"000",
            "zzcdprestat":"",
            "zzcdprestar":"",
            "zztyprencha":"",
            "zzdiprencha":"",
            "zztypededsv":"",
            "zzcdcbezone":"",
            "zzemultmod":"",
            "zzcdecanefe":"",
            "zznumseg":"01",
            "zzcdiatapfr":"",
            "zznuvoiture":"",
            "zzcoulcalen":"",
            "zzcdiataptv":"",
            "zztyposvte":"Z10X",
            "zztymachine":"38",
            "zztypsuppor":"",
            "zzclasse":"02",
            "zzcdiatapdv":"",
            "zzcdreductn":"",
            "zzcdtarif":"",
            "zzcduicdest":"87342014",
            "zzcduicorig":"87313874",
            "zzdistance":"00064",
            "zzdtedepart":"2025-06-04",
            "zzdurvalide":"",
            "zzhredepart":"17:50:00",
            "zzhrearrive":"",
            "zzmtutilred":0,
            "zzmotifdsv":"",
            "zznbpervald":"",
            "zznbvoygrat":"000",
            "zzcustomercardtype":"ZPAY",
            "zzcustomercardvalidfrom":"",
            "zzcustomercardvalidto":"",
            "zzcdgot":"01",
            "zzcanaldis":"200",
            "zzcanaldist":"9",
            "zzpalieresa":"",
            "zzefattribu":"",
            "zzdteemisdr":"",
            "zzcdorgvoyg":"",
            "zzclassecat":"002",
            "zzefprestat":"1187",
            "zzcdifictar":"",
            "zztyparcour":"",
            "zztytransac":"",
            "zzefetablit":"",
            "zzestemission":"",
            "zztypidentf":"",
            "zzefemettri":"",
            "zzntprestat":"",
            "zzdtearrive":"",
            "zztyprelakm":"",
            "zztransporteur":"0000022907",
            "zznbvk":64.00,
            "zzcdterrit":"DO",
            "zzcdbranchd":"10073",
            "zzdistributeur":"64699",
            "zzdivtransp":"18967",
            "zzcdbrancht":"01500",
            "zzcdrelakm":"",
            "zzattributr":"",
            "zztierprest":""
        }
    }]
}]

问题分析与解决方案

你的问题根源在于当前的flatten_json函数把整个JSON数组都展开成了单个字典，而且循环赋值时不断覆盖result里的键值对，最后只剩下最后一条数据。另外你的JSON结构是外层数组，每个元素包含vbrk（单个对象）和vbrp（对象数组），意味着每个主条目可能对应多个子行，我们需要正确处理这种一对多的结构。

下面是修正后的脚本，我会做这些调整：

重写flatten_json，让它返回单个扁平化的字典，而不是键值字符串
遍历JSON外层数组，处理每个主条目
对于每个主条目里的vbrp数组，把vbrk的字段和每个vbrp对象的字段合并成一行数据
收集所有行数据后统一写入CSV

修正后的代码：

import json
import csv
import os

# 路径设置
downloads = os.path.expanduser("~/Downloads")
json_path = os.path.join(downloads, "REB_ECC_THEMIS_DECISIONNEL_20250620020005.JSON")
csv_path = os.path.join(downloads, "filtered_output.csv")

# 加载JSON数据
with open(json_path, "r", encoding="utf-8") as f:
    data = json.load(f)

def flatten_json(obj, prefix=''):
    """返回扁平化的字典，处理嵌套结构"""
    result = {}
    if isinstance(obj, dict):
        for k, v in obj.items():
            new_prefix = f"{prefix}.{k}" if prefix else k
            result.update(flatten_json(v, new_prefix))
    elif isinstance(obj, list):
        # 这里针对你的结构，列表里是单个对象（比如vbrp里的单个vbrp对象），直接取第一个元素处理
        # 如果列表有多个元素，你可能需要调整逻辑，比如加索引前缀
        if obj:
            result.update(flatten_json(obj[0], prefix))
    else:
        result[prefix] = obj
    return result

all_rows = []
all_fields = set()

# 遍历外层JSON数组里的每个主条目
for main_item in data:
    # 扁平化vbrk部分
    vbrk_flat = flatten_json(main_item["vbrk"])
    # 遍历vbrp数组里的每个子条目
    for vbrp_item in main_item["vbrp"]:
        # 扁平化vbrp部分
        vbrp_flat = flatten_json(vbrp_item["vbrp"])
        # 合并vbrk和vbrp的字段
        row = {**vbrk_flat, **vbrp_flat}
        all_rows.append(row)
        # 收集所有字段名
        all_fields.update(row.keys())

# 写入CSV
with open(csv_path, "w", newline="", encoding="utf-8") as csvfile:
    # 把字段名转为列表，保证顺序稳定（可选：可以手动排序）
    fieldnames = sorted(all_fields)
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(all_rows)

说明

新的flatten_json直接返回扁平化字典，更方便后续处理
针对你的JSON结构，每个主条目（包含vbrk和vbrp数组）会生成对应数量的行（vbrp有几个元素就生成几行），每行都包含vbrk的公共字段和对应vbrp的明细字段
收集所有字段名确保CSV表头包含所有可能的键，不会遗漏字段

这样运行后，CSV里就会有所有的行数据，而不是只显示最后一行啦！

内容来源于stack exchange

JSON转CSV仅输出最后一行数据的问题求助

问题分析与解决方案 #

说明 #

问题分析与解决方案

说明