How to remove part of a JSON file

Asked

Viewed 893 times

3

I need to remove parts of a JSON, but I don’t know how to do it

I’m converting the same to CSV, and when I convert the structure gets all weird

Follow print explaining better, what is marked in black, need to remove and what is in red need to keep.

inserir a descrição da imagem aqui

Follow json referring what I need to convert:

{
    "@odata.context":"url requisição",
    "value":[
        {
            "cprq_cert_id":51,
            "cprq_object_id":"xxxxxxxxxxxxxxxxxxxx",
            "cprq_cert_cert_id":0
        },
        {
            "cprq_cert_id":52,
            "cprq_object_id":"xxxxxxxxxxxxxxxxxxxx",
            "cprq_cert_cert_id":0
        },
        {
            "cprq_cert_id":54,
            "cprq_object_id":"xxxxxxxxxxxxxxxxxxxx",
            "cprq_cert_cert_id":0
        },
        {
            "cprq_cert_id":72,
            "cprq_object_id":"xxxxxxxxxxxxxxxxxxxx",
            "cprq_cert_cert_id":0
        },
        {
            "cprq_cert_id":73,
            "cprq_object_id":"xxxxxxxxxxxxxxxxxxxx",
            "cprq_cert_cert_id":0
        }
    ]
}

And also the python script I use to convert:

import csv
import json
infile = open("nomejson.json","r")
outfile = open("nomecsv.csv","w")

writer = csv.writer(outfile)

for row in json.loads(infile.read()):
    writer.writerow(row)
python json type-conversion
  • 1

    By cell phone I can not see what is in the print. Have to put in the text of the question the text you highlighted in the image?

  • So I actually identified another problem he’s putting bars in the json that don’t appear in the console output, so the json format is bad. Strange

  • So the error is not reproducible? Should it be closed by "typo"? Or else it deserves you to answer it yourself

  • It is not typing, in python output it works normal, but when I send pro json it gets the bars ( '' )

  • then I think you should answer

  • @Lacobus, maybe the counterbars are literal? See previous AP comments

  • Then they appear when I run the following commands: with open(view+'. json', 'w') as outfile: json.dump(r. text, outfile)

Show 2 more comments

2 answers

4

json2csv.py:

import csv
import json

# Abre arquivo de entrada para leitura
with open("entrada.json", "r") as infile:

    # Carrega arquivo JSON de entrada
    data = json.load( infile )

# Abre arquivo de saida para gravacao
with open("saida.csv", "wb") as outfile:

    # Cria gravador de CSV...
    wr = csv.writer( outfile, delimiter=',' )

    # Para cada elemento da array "value"
    for item in data["value"]:

        # Escreve cabecalho somente na primeira iteracao
        if( data["value"].index(item) == 0 ):
            wr.writerow(item.keys())

        # Escreve linha com os dados
        wr.writerow(item.values())

input.json:

{
    "@odata.context":"https://jnj-dev-pilot.csod.com/services/api/x/odata/api/views/#vw_rpt_cert_prerequisite",
    "value":[
        {
            "cprq_cert_id":51,
            "cprq_object_id":"d0b7fae2-cc11-44d1-a45b-a39642e65c08",
            "cprq_cert_cert_id":0
        },
        {
            "cprq_cert_id":52,
            "cprq_object_id":"20ca15ff-7c6f-4938-a00a-7f17a2753a7b",
            "cprq_cert_cert_id":0
        },
        {
            "cprq_cert_id":54,
            "cprq_object_id":"d0b7fae2-cc11-44d1-a45b-a39642e65c08",
            "cprq_cert_cert_id":0
        },
        {
            "cprq_cert_id":72,
            "cprq_object_id":"d0b7fae2-cc11-44d1-a45b-a39642e65c08",
            "cprq_cert_cert_id":0
        },
        {
            "cprq_cert_id":73,
            "cprq_object_id":"d0b7fae2-cc11-44d1-a45b-a39642e65c08",
            "cprq_cert_cert_id":0
        }
    ]
}

exit.csv:

cprq_cert_id,cprq_cert_cert_id,cprq_object_id
51,0,d0b7fae2-cc11-44d1-a45b-a39642e65c08
52,0,20ca15ff-7c6f-4938-a00a-7f17a2753a7b
54,0,d0b7fae2-cc11-44d1-a45b-a39642e65c08
72,0,d0b7fae2-cc11-44d1-a45b-a39642e65c08
73,0,d0b7fae2-cc11-44d1-a45b-a39642e65c08

  • Very good, the problem now is to generate the json that does not come exactly in this format, but I will open another topic to address this issue. Thanks @lacobus!

  • Lacobus: Cool!! + 1

0

(not really an answer but a note) Using the csvkit:

$ in2csv -Ik value outro.json 
cprq_cert_id,cprq_object_id,cprq_cert_cert_id
51,d0b7fae2-cc11-44d1-a45b-a39642e65c08,0
52,20ca15ff-7c6f-4938-a00a-7f17a2753a7b,0
54,d0b7fae2-cc11-44d1-a45b-a39642e65c08,0
72,d0b7fae2-cc11-44d1-a45b-a39642e65c08,0
73,d0b7fae2-cc11-44d1-a45b-a39642e65c08,0

Browser other questions tagged python json type-conversion

You are not signed in. Login or sign up in order to post.