Create json from Excel data using Python

Create json from Excel data using Python

JSON

JavaScript Object Notation is an open standard file format, easy to transfer data, easy structured, more readable, shows hierarchical data. Lets generate json for given source using Python:

Source

Code example

import json
from openpyxl import load_workbook

workbook = load_workbook(filename="test.xlsx")
sheet = workbook.active

listAgents = {}

# Iterate values 
for row in sheet.iter_rows(min_row=2, min_col=4, max_col=7, values_only=True):
    agent_id = row[0]
    agentInfo = {
        "Agent": row[1],
        "Contact Number": row[2],
        "Address": row[3]
    }
    listAgents[agent_id] = agentInfo

# print agent information
print(json.dumps(listAgents))

Output

{
  "10001": {
    "Agent": "D Cohoon",
    "Contact Number": 941 000 0000,
    "Address": "abc, Florida"
  },
  "10002": {
    "Agent": "M Pike",
    "Contact Number": 941 000 0000,
    "Address": "def, USA"
  }
}

If you like the post please subscribe and keep up-to-date with new posts.

Leave a Reply

Your email address will not be published.