How to convert an excel to a nested JSON using xlrd?

Question

I am a newbie in Python, and I need to convert an excel file to a nested JSON. I'm using xlrd library to parse the excel file.

an example of my Excel file:

code number  object code   device Model    brand
09823            0x3          modelA         C
08334            0x3          modelA         C
98223            0x7          modelB         C

I want the output in the following format:

[
  "brand": "C",
   "devices":
[
    {
      "device_model": "modelA",
      "object_code" : "0x3",
      "part_numbers": [
         "09823",
         "08334" 
      ]
    },
  {
      "device_model": "modelB",
      "object_code" : "0x7",
      "part_numbers": [
         "98223"
      ]
    }
]

This is my code:

def xlsParse(xlsfile): store = [] #List of objects to be parsed into json file framenames = [] #this list helps us keep track of what has been stored in our list of objects

book = xlrd.open_workbook("myFilee.xls")
sh1 = book.sheet_by_index(0)

for rx in range(1, sh1.nrows):
    if sh1.row(rx)[14].value not in framenames:
        framenames.append(sh1.row(rx)[14].value)
        frame = {"brand": sh1.row(rx)[14].value,
        
          "devices":[]
           }
        store.append(frame)

segment = {"object_code":""}

for frame in store:
    for rx in range(1, sh1.nrows):
        if frame["brand"] == sh1.row(rx)[14].value:
            if segment["object_code"] != sh1.row(rx)[12].value:
                
                segment = {
                "object_code": sh1.row(rx)[12].value,
                "device_model":sh1.row(rx)[13].value,
              
                "part_numbers":[{
               
                "": sh1.row(rx)[3].value
                 
                    }]
                }
                frame["devices"].append(segment)
            else:
                packet_partNumber = {
               "" :sh1.row(rx)[3].value,
               }
                segment["part_numbers"].append(packet_partNumber)

The output of this code is :

[
    {
        "brand": "C",
        "devices": [
            {
                "object_code": "0x3",
                "device_model": "modelA",
                "part_numbers": [
                    {
                        "": "09823"
                    },
                    {
                        "": "08334"
                    }
                ]
            },
            {
           "object_code": "0x7",
                "device_model": "modelB",
                "part_numbers": [
                    {
                        "": "09223"
                    }
                ]
            }
        }
      ]
    }
]

I think that I need to separate the "part numbers" and append them to my Jason. I tried it, but I couldn't get the output that I wanted.

score 0 · Answer 1 · answered May 27 '21 at 14:16

0

I would use pandas to take of this. You can easily convert it like this.

dataframe = pd.read_excel("myFilee.xls")
dataframe.to_json('json_output.json', index=False)

answered May 27 '21 at 14:16

killersquid

51
4

1

Thanks, but I need to convert it to a nested jason. – roya zamiri May 27 '21 at 14:20
1

Check this out, maybe this will help ya out. https://stackoverflow.com/questions/40470954/convert-pandas-dataframe-to-nested-json – killersquid May 27 '21 at 14:25

How to convert an excel to a nested JSON using xlrd?

1 Answers1