Need help in python scripting

[rajrishi990]

I am new to python
I have below json file and i need to convert it to csv and pull only selected columns in csv.Selected columns will be id,SID,Type,ttt,S.
I am unable to figure out solution. PLease help

Output:
{
"test": [
{
"id": 1,
"name": "dummy",
"salary": [
{
"SID": "1",
"Type ": "INR",
"isMandatory": "Yes"
},
{
"SID": "2",
"Type": "DOLLAR",
"isMandatory": "No"
}
],
"ttt111": [
{
"ttt": 4,
"S": null
},
{
"ttt": 5,
"S": null
},
{
"ttt": 6,
"S": null
}
]
}}

[ichabod801]

What have you tried? We're not big on writing code for people here, but we would be happy to help you fix your code when you run into problems. When you do run into problems, please post your code in Python tags, and clearly explain the problem you are having, including the full text of any errors.

[rajrishi990]

i am using below script . i get values only for id and name and rest is blank.

import sys
import json
import csv
reload(sys)
sys.setdefaultencoding('utf8')
with open('/local/a.json','r') as f:
        json_data=json.load(f,strict=False)
with open('/local/test.csv','w') as outfile:
        csv_write=csv.writer(outfile,quoting=csv.QUOTE_ALL)
        for json_d in json_data["a"]:
                row_array = []
                try:
                        row_array.append(json_d["id"])
                except KeyError:
                        row_array.append('')
                try:
                        row_array.append(json_d["name"])
                except KeyError:
                        row_array.append('')
                try:
                        row_array.append(json_d["SID"])
                except KeyError:
                        row_array.append('')
				try:
                        row_array.append(json_d["Type"])
                except KeyError:
                        row_array.append('')
                except TypeError:
                        row_array.append('')
                csv_write.writerow(row_array)
outfile.close()
[python]
[hr]
i am using below script . i get values only for id and name and rest is blank.
[python]
import sys
import json
import csv
reload(sys)
sys.setdefaultencoding('utf8')
with open('/local/a.json','r') as f:
json_data=json.load(f,strict=False)
with open('/local/test.csv','w') as outfile:
csv_write=csv.writer(outfile,quoting=csv.QUOTE_ALL)
for json_d in json_data["a"]:
row_array = []
try:
row_array.append(json_d["id"])
except KeyError:
row_array.append('')
try:
row_array.append(json_d["name"])
except KeyError:
row_array.append('')
try:
row_array.append(json_d["SID"])
except KeyError:
row_array.append('')
try:
row_array.append(json_d["Type"])
except KeyError:
row_array.append('')
except TypeError:
row_array.append('')
csv_write.writerow(row_array)
outfile.close()

[rajrishi990]

i found solution but i am getting error in below one as key error:'ttt' if ttt is not available for one record

import sys
import json
import csv
reload(sys)
sys.setdefaultencoding('utf8')
with open('/local/dummy.json','r') as f:
        json_data=json.load(f,strict=False)
with open('/local/application.csv','w') as outfile:
      csv_write=csv.writer(open("/local/application.csv", "wb+"))
# Write CSV Header, If you dont need that, remove this line
csv_write.writerow(["id", "SID","Type", "ttt"])
for x in json_data["dummy"]:
    csv_write.writerow([x["id"],
                x["salary"][0]["SID"],
                x["ttt111"][0]["ttt"],
                x["ttt111"][0]["S"]])

[DeaD_EyE]

import json
import csv


with open('/local/dummy.json') as f:
    json_data = json.load(f, strict=False)

with open('/local/application.csv', 'w') as outfile:
    csv_write = csv.writer(outfile)
    csv_write.writerow(["id", "SID", "Type", "ttt"])
    for x in json_data["dummy"]:
        try:
            row = x["id"], x["salary"][0]["SID"], x["ttt111"][0]["ttt"], x["ttt111"][0]["S"]
        except KeyError:
            # skip this row?
            # using a default fill value?
            continue # continue with next iteration, won't write the row
        csv_write.writerow(row)

Don't ask for permission, ask for forgiveness.
The try-except block handles the error. You can decide what you want to do, if the key does not exist.

Instead of re-opening the csv-file the whole time for only one row is inefficient.
Use the file-object outfile in the with-block instead. As long the for-loop is inside the with-block,
the file is still open and it would write.

By the way, why are you setting the default encoding?
A reload of sys direct after importing it, does not change anything.

[rajrishi990]

in my code if i use below

   try:
                        row_array.append(json_d["test"]["aa"]["bb"])
                except KeyError:
                        row_array.append('')

i am getting below error. Can some one help

TypeError: 'NoneType' object is unsubscriptable

[DeaD_EyE]

Then catch two exceptions:

try:
    # code
except (KeyError, TypeError):
    # code to handle the exception

The TypeError came, because test or aa returns None.
You expect that the value is also a dict. But it seems, that sometimes there is a None.

example_dict = {
    'dict': {
        'subdict': {
            'subsubdict': {
                'value': 42,
            }
        }
    }
}


print(example_dict['dict']['subdict']['subsubdict']['value'])

# now replacing the value of subdict with None.
example_dict['dict']['subdict']['subsubdict'] = None


# TypeError
print(example_dict['dict']['subdict']['subsubdict']['value'])

Error:
---------------------------------------------------------------------------      
TypeError                                 Traceback (most recent call last)      
<ipython-input-4-0a16fb6ffb69> in <module>                                       
----> 1 print(example_dict['dict']['subdict']['subsubdict']['value'])            
                                                                                 
TypeError: 'NoneType' object is not subscriptable                                

[rajrishi990]

i have nested json inside json like below: -
[json]
<a>
<Id>3</Id>
<name>IT</name>
<axe>
<prog>
<progr>C#</progr>
</prog>
</axe>
[/json]

When i am trying pull selected column using below , i am getting blank values for column=progr

import json
import csv
with open('/local/a.json','r') as f:
        json_data=json.load(f,strict=False)
with open('/local/a.csv','w') as outfile:
        csv_write=csv.writer(outfile,quoting=csv.QUOTE_ALL)
        csv_write.writerow(["Id", "progr"])
        for json_d in json_data["a"]:
                row_array = []
                try:
                        row_array.append(json_d["Id"])
                except KeyError:
                        row_array.append('')
                try:
                        row_array.append(json_d["a"]["axe"]["prog"]["progr"])
                except (KeyError,TypeError):
                        row_array.append('')
                except TypeError:
                        row_array.append('')
                csv_write.writerow(row_array)
outfile.close()

[DeaD_EyE]

<a>
<Id>3</Id>
<name>IT</name>
<axe>
<prog>
<progr>C#</progr>
</prog>
</axe>

This is not JSON. It's invalid XML.

[rajrishi990]

{
"a": [
{
"Id": 3,
"name": "I)",
"axe": [
{
"progr": 378
}
}
]
]
}

---

<a>
<Id>3</Id>
<name>IT</name>
<axe>
<prog>
<progr>C#</progr>
</prog>
</axe>

This is not JSON. It's invalid XML.

Json
{
"a": [
{
"Id": 3,
"name": "I)",
"axe": [
{
"progr": 378
}
}
]
]
}