CODEWITHSUNDEEP
pythonjsoncsvexport
Bitcoin Murderous Maniac
Bitcoin Murderous Maniac

Reputation: 1478

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']])

Upvotes: 0

Views: 1883

Answers (1)

Vivek Srinivasan
Vivek Srinivasan

Reputation: 2887

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

Upvotes: 1

Related Questions