如何将CSV转换为正确格式的Json

Question

您好，我是使用python将csv转换为Json的新手。 我试图将csv文件转换为json格式，并在函数调用中返回响应，但得到的是斜杠字符

当我使用记事本打开CSV文件时，数据就是这样显示的

"Sno ","Name","Age ","City"
"1","Alex","27","Newyork"
"2","Smith","25","Los angeles"
"3","austin","26","Calfornia

预期输出：

[
    {
        "Sno ": "1",
        "Name": "Alex",
        "Age ": "27",
        "City": "Newyork"
    },
    {
        "Sno ": "2",
        "Name": "Smith",
        "Age ": "25",
        "City": "Los angeles"
    },
    {
        "Sno ": "3",
        "Name": "austin",
        "Age ": "26",
        "City": "Calfornia"
    }
]

Python代码：

def refresh():
    # reading the header from the csv file in an array
    with open(r"C:\Users\prasanna.kommuri\example_flask_application\target_py_files\data_file.csv", "r") as f:
        reader = csv.reader(f)
        csv_header_row = next(reader)

    #Reading the content from the csv file
    json_data = [json.dumps(d) for d in csv.DictReader(open(r"C:\Users\prasanna.kommuri\example_flask_application\target_py_files\data_file.csv", 'r'), fieldnames=csv_header_row, quotechar='"', delimiter=',',
                 quoting=csv.QUOTE_ALL, skipinitialspace=True)]
    return jsonify(json_data)

实际输出：

[
   "{\"Sno \": \"Sno \", \"Name\": \"Name\", \"Age \": \"Age \", \"City\": \"City\"}",
   "{\"Sno \": \"1\", \"Name\": \"Alex\", \"Age \": \"27\", \"City\": \"Newyork\"}",
   "{\"Sno \": \"2\", \"Name\": \"Smith\", \"Age \": \"25\", \"City\": \"Los angeles\"}",
   "{\"Sno \": \"3\", \"Name\": \"austin\", \"Age \": \"26\", \"City\": \"Calfornia\"}"
]

有人能帮我解决哪里出了问题吗？或者任何建议/回答都会有帮助 先谢谢你

Answer 1

请检查这个

import csv
import json

def refresh():
    # reading the header from the csv file in an array
    data_dict_list = []
    with open(r"result.csv", "r") as f:
        reader = csv.reader(f)
        csv_header_row = next(reader)

        for data in reader:
            data_dict_list.append(
                {
                    csv_header_row[0]: data[0],
                    csv_header_row[1]: data[1],
                    csv_header_row[2]: data[2],
                    csv_header_row[3]: data[3],
                 }
            )

        return  json.dumps(data_dict_list, indent=4)

result = refresh()
print(result)

输出：

[
    {
        "Sno ": "1",
        "Name": "Alex",
        "Age ": "27",
        "City": "Newyork"
    },
    {
        "Sno ": "2",
        "Name": "Smith",
        "Age ": "25",
        "City": "Los angeles"
    },
    {
        "Sno ": "3",
        "Name": "austin",
        "Age ": "26",
        "City": "Calfornia"
    }
]

Answer 2

import json, csv

with open(r'U:\foobar.csv', 'r', newline='') as f:
    print(json.dumps(list(csv.DictReader(f)), indent=4))

印刷品

[
    {
        "Sno ": "1",
        "Name": "Alex",
        "Age ": "27",
        "City": "Newyork"
    },
    {
        "Sno ": "2",
        "Name": "Smith",
        "Age ": "25",
        "City": "Los angeles"
    },
    {
        "Sno ": "3",
        "Name": "austin",
        "Age ": "26",
        "City": "Calfornia"
    }
]

或输出到文件

with open(r'U:\foobar.csv', 'r', newline='') as fin:
    with open(r'U:\foobar.json', 'w') as fout:
        json.dump(list(csv.DictReader(f)), fout, indent=4))

Answer 3

免责声明这并不是解释代码失败的原因，而是提供了一个使用pandas的替代解决方案，正如您提到的，您也乐于接受新的解决方案

>>> import pandas as pd
>>> from io import StringIO  # that's just to pretend I have a .csv file like yours

>>> df = pd.read_csv(StringIO('''"Sno ","Name","Age ","City" 
"1","Alex","27","Newyork" 
"2","Smith","25","Los angeles" 
"3","austin","26","Calfornia"'''))                                                                                                                 

>>> print(df.to_json(orient="records", indent=4))                                                                                                                      
[
    {
        "Sno ":1,
        "Name":"Alex",
        "Age ":27,
        "City ":"Newyork "
    },
    {
        "Sno ":2,
        "Name":"Smith",
        "Age ":25,
        "City ":"Los angeles "
    },
    {
        "Sno ":3,
        "Name":"austin",
        "Age ":26,
        "City ":"Calfornia"
    }
]

简而言之，假设您有一个指向名为path_to_csv的.csv文件的路径，它是一个1行程序：

json_data = pd.read_csv(path_to_csv).to_json(orient="records", indent=4)