(Python) (Json to Excel) Generating random name and ID from Json(Python) to Excel

Question

everyone. I'm generating random name and ID from Json (Python) to Excel but there is an issue.

import json 
from faker import Faker
import random
from random import randint
from json2excel import Json2Excel

if __name__ == '__main__':
    json2excel = Json2Excel(head_name_cols=["Random_ID", "Random_UserName"])

fake = Faker('en_US')
for _ in range(10):
    my_dict = {'Random_ID': randint(0, 10000),'User': {'Random_UserName': fake.name(),'age': int(random.randrange(10, 100))} }
    
    print(json2excel.run(my_dict))

and the console output

PS C:\Users\chuan\OneDrive\Desktop\Python> & C:/Users/chuan/AppData/Local/Microsoft/WindowsApps/python3.10.exe c:/Users/chuan/OneDrive/Desktop/Python/1.py
Traceback (most recent call last):
  File "c:\Users\chuan\OneDrive\Desktop\Python\1.py", line 14, in <module>
    print(json2excel.run(my_dict))
  File "C:\Users\chuan\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\json2excel\j2e.py", line 75, in run
    file_path = self._export_handler(data_list, file_name)
  File "C:\Users\chuan\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\json2excel\handlers\export_handler.py", line 56, in __call__
    self._add_row(each_dict)
  File "C:\Users\chuan\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\json2excel\handlers\export_handler.py", line 68, in _add_row
    self._ws.append(row)
  File "C:\Users\chuan\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packa  File "C:\Users\chuan\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\openpyxl\cell\cell.py", line 120, in __init__
    self.value = value  File "C:\Users\chuan\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\openpyxl\cell\cell.py", line 252, in value
    self._bind_value(value)  File "C:\Users\chuan\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\openpyxl\cell\cell.py", line 218, in _bind_value
    raise ValueError("Cannot convert {0!r} to Excel".format(value))ValueError: Cannot convert {'Random_UserName': 'Kristen Schultz', 'age': 97} to Excel
PS C:\Users\chuan\OneDrive\Desktop\Python>

I do also see the relative problem down below to helped me get here, but I still stocking at this point,

(Link) from stack overflow "How to generate random json data everytime using python?"

(pic) The output picture

How can I correct?? thanks

score 0 · Accepted Answer · answered Sep 23 '22 at 09:51

Instead of using json2excel package you can use the definitely more common package pandas to do the same in a definitely better documented way (json2excel uses Chinese in its docs):

from faker import Faker
import random
from random import randint
import pandas as pd

if __name__ == '__main__':
    fake = Faker('en_US')
    list_of_my_dicts = []
    for _ in range(10):
        my_dict = {'Random_ID': randint(0, 10000),
                   'User': {'Random_UserName': fake.name(), 'age': int(random.randrange(10, 100))}}

        list_of_my_dicts += [my_dict]

    dataframe = pd.json_normalize(list_of_my_dicts)

    # create excel writer object
    with pd.ExcelWriter('path_to_file.xlsx') as writer:
        # write dataframe to excel
        dataframe.to_excel(writer, sheet_name='Sheet1')

Output:

A	ID	User.RandomName	User.age
0	4577	Mark Duran	39
1	360	Elizabeth Reeves	82
2	4667	Jessica Blevins	63
3	7629	Michael Allison	21
4	8392	Richard Russo	30
5	9925	Kevin Maddox	82
6	3845	Heidi Thomas	13
7	3619	Andrea Thomas	61
8	9489	Henry Smith	26
9	8055	Dennis Travis	93

(Python) (Json to Excel) Generating random name and ID from Json(Python) to Excel

1 Answers1