Can someone help me to convert this excel to json format using python please, i have tasks and subtasks like in this picture link
1
-
@Andrei I would like to convert this excel to a nested json using python, with A01 as the main task, and A1 that is a task in A01, and A9 is a subtask in A1, and A22 as a subsubtask of A9...elk_python– elk_python2022-05-25 07:23:15 +00:00Commented May 25, 2022 at 7:23
Add a comment
|
1 Answer
You can either use the xlrd library or the pandas library. Read the documentation of both and choose which would be best for you.
Pandas would look like this (stolen from someone else):
import pandas
import json
# Read excel document
excel_data_df = pandas.read_excel('data.xlsx', sheet_name='sheet1')
# Convert excel to string
# (define orientation of document in this case from up to down)
thisisjson = excel_data_df.to_json(orient='records')
# Print out the result
print('Excel Sheet to JSON:\n', thisisjson)
# Make the string into a list to be able to input in to a JSON-file
thisisjson_dict = json.loads(thisisjson)
# Define file to write to and 'w' for write option -> json.dump()
# defining the list to write from and file to write to
with open('data.json', 'w') as json_file:
json.dump(thisisjson_dict, json_file)
4 Comments
elk_python
Thank you for ansewring, but in this case i want to have an hierarchy of tasks, subtasks and "subsubtaks" in my json format, or something like parent-child, because after that i want to push this json to ElasticSearch after.
Denel
Try to understand the json library yourself. I don't know exactly what result you are hoping for, but if you want something like this:
{"A" : {"B": "C"}} it is possible, but you just need to figure out how to use the json and pandas libraryDenel
I recommend reading the thread that I linked on the bottom of my answer. You can see that the person asking the question is trying to achieve the exact same thing. It is possible but you just need to figure out how to implement it using the json and pandas library yourself by reading the documentation.
elk_python
Yes, it's exactly what i need to do, it's like a nested json. And I don't know how to modelise it for the example that i give.