CODEWITHSUNDEEP
pythonjsonxlsx
KlaoBurn
KlaoBurn

Reputation: 1

Transform JSON to XLS file with Python

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

Upvotes: 0

Views: 100

Answers (1)

furas
furas

Reputation: 142631

.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

Upvotes: 1

Related Questions