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
}
]
}}
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.
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()
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"]])
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.
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
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
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()
Quote:<a>
<Id>3</Id>
<name>IT</name>
<axe>
<prog>
<progr>C#</progr>
</prog>
</axe>
This is not JSON. It's invalid XML.