Transform JSON to XLS file with Python

Question

I'm trying to transform a JSON file to a XLS file, but it returns me an error called:

-> name = record['name'] TypeError: byte indices must be integers or slices, not str

I've already tried do that thing with JS but I'm completely lost, I think Python Will be more easy to understand all of this

import os
import json
import win32com.client as win32  # pip install pywin32
import requests
"""
Step 1.1 Read the JSON file
"""


json_data = requests.get("https://restcountries.com/v3.1/all")
print(json_data.content)

"""
Step 1.2 Examing the data and flatten the records into a 2D layout
"""
rows = []

for record in json_data:
    name = record['name']


"""
Step 2. Inserting Records to an Excel Spreadsheet
"""
ExcelApp = win32.Dispatch('Excel.Application')
ExcelApp.Visible = True

wb = ExcelApp.Workbooks.Add()
ws = wb.Worksheets(1)

header_labels = ('name')

# insert header labels
for indx, val in enumerate(header_labels):
    ws.Cells(1, indx + 1).Value = val

# insert Records
row_tracker = 2
column_size = len(header_labels)

for row in rows:
    ws.Range(
        ws.Cells(row_tracker, 1),
        ws.Cells(row_tracker, column_size)
    ).value = row
    row_tracker += 1

wb.SaveAs(os.path.join(os.getcwd(), 'Json output.xlsx'), 51)
wb.Close()
ExcelApp.Quit()
ExcelApp = None

Maybe first use print() (and print(type(...)), print(len(...)), etc.) to see which part of code is executed and what you really have in variables. It is called "print debuging" and it helps to see what code is really doing. — furas
– furas, Commented Aug 28, 2022 at 21:37
if server sends JSON data then you should get json_data.json() or json.loads(json_data.content) to get it as dictionary because json_data.content has it as bytes — furas
– furas, Commented Aug 28, 2022 at 21:38
you have empty rows because you forgot row.append(name) inside for-loop — furas
– furas, Commented Aug 28, 2022 at 21:50

furas · Accepted Answer · 2022-08-28 21:46:31Z

1

.content gives data as bytes. You should get .json() to have it as Python dictionary.

response = requests.get("https://restcountries.com/v3.1/all")

json_data = response.json()

#json_data = json.loads(response.content)

Minimal working example

I use [:10] only to display first 10 values but you should skip [:10]

import requests

response = requests.get("https://restcountries.com/v3.1/all")

json_data = response.json()
#print(json_data)

rows = []

for record in json_data[:10]:
    name = record['name']['official']
    print(name)
    rows.append(name)

Result:

Republic of Finland
Republic of Guatemala
Republic of Chile
Oriental Republic of Uruguay
Kyrgyz Republic
Republic of Zambia
Niue
Republic of Austria
Georgia
Republic of Trinidad and Tobago

edited Aug 28, 2022 at 21:46

answered Aug 28, 2022 at 21:41

furas

149k12 gold badges121 silver badges171 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Transform JSON to XLS file with Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related