时间戳

首页 > 解决方案 > 将 JSON 输出转换为 CSV - 删除值中的换行符

python - 将 JSON 输出转换为 CSV - 删除值中的换行符

问题描述

我正在调用一个 API，其输出如下：

    {
        "fId": "GL",
        "totalItems": 1200,
        "totalHits": 1200,
        "fields": [
            "id",
            "refNumber",
            "name",
            "type",
            "parentId",
            "parentName",
            "parentType",
            "description"
        ],
        "items": [
            {
                "ref": "#27:270",
                "id": "20",
                "values": [
                    "20",
                    "FID-20",
                    "TS SYSTEM",
                    "TABLE",
                    "1426",
                    "ALIAS",
                    "SCHEMA",
                    "tEST DESCRIPTION"
                ]
            },
            {
                "ref": "#27:269",
                "id": "507",
                "values": [
                    "507",
                    "FID-507",
                    "FCR Ratio",
                    "TABLE",
                    "1426",
                    "CONGR",
                    "SCHEMA",
                    "Test description",
                ]
            }
     ]
]

“字段”包含标题值，“项目”包含相应的值。

我能够使用来自@ErdoğanOnal 的输入来展平 JSON 中的“字段”和“项目”键，但我面临另一个问题，因为“项目”中的“描述”有多个换行符，例如。“tEST\n D\nSCRIPTION “因此，输出被分成多行。我修改了@ErdoğanOnal 的逻辑，如下所示，但对于少数条目，它仍然被分成多行。

header = r["fields"]
result = ",".join(header) + "\n"
for item in r["items"]:
    for value in item["values"]:
        if "\n" in value:
          value=value.replace("\n", "")
        else:
          value
        result += value + ","
    result = result.rstrip(",") + "\n"
result = result.strip()
print(result)

有人可以帮忙吗？

标签： pythonjsonpython-3.x

解决方案

这里是带有换行符的 JSON：

json_out = {
  "fId": "GL",
  "totalItems": 1200,
  "totalHits": 1200,
  "fields": [
    "id",
    "refNumber",
    "name",
    "type",
    "parentId",
    "parentName",
    "parentType",
    "description"
  ],
  "items": [
    {
      "ref": "#27:270",
      "id": "20",
      "values": [
        "20",
        "FID-20",
        "TS SYSTEM",
        "TABLE",
        "1426",
        "ALIAS",
        "SCHEMA",
        "tEST DESCRIPTION"
      ]
    },
    {
      "ref": "#27:269",
      "id": "507",
      "values": [
        "507",
        "FID-507",
        "FCR Ratio",
        "TABLE",
        "1426",
        "CONGR",
        "SCHEMA",
        "tEST\n D\nESCRIPTION "
      ]
    }
  ]
}

# Find the header
header = json_out["fields"]
result = ",".join(header) + "\n"
for item in json_out["items"]:
    for value in item["values"]:
        # Get rid of newlines
        result += value.replace("\n", "") + ","
    result = result.rstrip(",") + "\n"
result = result.strip()
print(result)

输出是：

id,refNumber,name,type,parentId,parentName,parentType,description
20,FID-20,TS SYSTEM,TABLE,1426,ALIAS,SCHEMA,tEST DESCRIPTION
507,FID-507,FCR Ratio,TABLE,1426,CONGR,SCHEMA,tEST DESCRIPTION

推荐阅读