I have an Excel sheet that has json stored in on of it's cells. Sheet has multiple rows, but every json is in one column.
I need a way to replace some of the elements of that json, and save it back as an Excel file.
I'm sort of a python guy, so I was trying to use pandas and json libraries, I've managed to open file, retrieve json data, navigate to value I want to change and I'm stuck. What am I supposed to do now?
import json
import pandas as pd
#Opening file, it has a lot of columns, so I'm taking only nescesary
data = pd.read_excel('file.xlsm', sheet_name='Sheet1', usecols='O,Q')
#Don't know what to do with it, so lets numpy take it to the next level
data_array = data.to_numpy()
#names and jsons are empty lists to store data
for row in data_array:
names.append(row[0])
jsons.append(row[1])
#I'm trying to get one record, just to find location of values to change
desc = json.loads(jsons[5])
So I've managed to get the values I want to change, but how am I put it all back together? I'm sure there is a one function that does that in a blink of an eye.
Here's a sample json:
{
"sections": [
{
"items": [
{
"type": "IMAGE",
"url": "URL"
}
]
},
{
"items": [
{
"type": "IMAGE",
"url": "https://a.allegroimg.com/original/112b64/28e6d8d0461eb619059bbcab4d5d"
},
{
"type": "TEXT",
"content": "String to be changed"
}
]
},
{
"items": [
{
"type": "TEXT",
"content": "Another string to be changed"
}
]
},
... n number objects ...
{
"items": [
{
"type": "IMAGE",
"url": "URL"
}
]
}
]
}
As a result I want the same json with replaced strings to have put it back into xlsm file.
