IT Solutions IT Solutions - 29 days ago 9
JSON Question

Iterating all elements within a for loop with Python to extract JSON data

I have the below example data in a file and I'm using Python with the below code to get data out of it to a CVS file and the logic seems to work fine with other data with less elements using the

writerow()
function.

I'm having trouble figuring out how to tell this logic to dump all rows and not just the first one i.e.
[0]
and I've read and tested with putting the
for
loop in a
range(len(int)):
array or whatever this way but I keep getting errors.

If I do not specify the
[#]
element or whatever in the
writerow[item['tags']['<field>']
then I get an error about
TypeError: list indices must be integers or slices, not str
so I just cannot figure out how to tell this thing to dump all the rows for these fields in this JSON file to a CSV file, but this is what I have and that is what I tried with the
range(len)
function but I cannot get it to work.

I'm new to Python so I don't know it that well yet but from another question on another SE community for this task I was told it'd be a good tool to use for dumping JSON data to CSV and it seems to work well until I need to iterate all elements within other levels, and so on.

I also read there may be a way to use a while loop to do this but iterating with a non-while loop using the array with the
range(len)
method makes better sense to me but I'm not doing something correct in the loop for it to work. I think I'm on the right path but hoping a Python expert can help me.

Sample Data



{
"success": true,
"devices": [
{
"id": 123456,
"name": "RemoteSite123",
"tags": [
{
"id": 445533,
"name": "testtag",
"dataType": "Float",
"description": "",
"alarm": "",
"value": 0,
"quality": "good",
"devId": 1
},
{
"id": 112233,
"name": "Test2PSI",
"dataType": "Float",
"description": "",
"alarm": "",
"value": 0,
"quality": "good",
"devId": 2
},
{
"id": 90011,
"name": "Tank2",
"dataType": "Bool",
"description": "",
"alarm": "",
"value": 0,
"quality": "good",
"devId": 3
},
{
"id": 55110,
"name": "Tank3",
"dataType": "Bool",
"description": "",
"alarm": "",
"value": 0,
"quality": "good",
"devId": 4
},
{
"id": 71231,
"name": "Line4Sensor",
"dataType": "Bool",
"description": "",
"alarm": "",
"value": 0,
"quality": "good",
"devId": 5
},
{
"id": 74321,
"name": "WaterFill",
"dataType": "Bool",
"description": "",
"alarm": "",
"value": 0,
"quality": "good",
"devId": 6
},
{
"id": 70589,
"name": "tank9PSI",
"dataType": "Float",
"description": "",
"alarm": "",
"value": 0,
"quality": "good",
"devId": 7
}
],
"lastSynchroDate": "2016-11-08T03:32:21Z"
}
]
}


My Code



import json
import csv

with open('C:\\folder\\test.txt',"r") as file:
data = json.load(file)

with open('C:\\folder\\test.csv',"w",newline='') as file:
csv_file = csv.writer(file)
for item in data["devices"]:
csv_file.writerow([item['tags'][0]['id'], item['tags'][0]['name'], item['tags'][0]['dataType'], item['tags'][0]['description'], item['tags'][0]['alarm'], item['tags'][0]['value'], item['tags'][0]['quality'], item['tags'][0]['devId']])

Answer

You can try putting one more for loop to iterate over tags list as well

with open('D:\\test.csv',"w") as file:
    csv_file = csv.writer(file)
    for item in data["devices"]:
        for tag in item['tags']:
            csv_file.writerow([tag['id'], tag['name'], tag['dataType'], tag['description'], tag['alarm'], tag['value'], tag['quality'], tag['devId']])

Kindly lemme know whether this is what needed