How to convert Excel to JSON and append it to existing JSON file?-CodePudding

I have Excel file like this:

-------------------------
| myValue | myValue2 | myValue3 |
-------- ------- --------
|   1   |   A   |   AA|
|   2   |   B   |   BB|
|   4   |   C   |   CC  |
|   5   |   D   |   DD  |
|   6   |   E   |   EE|
|   7   |   F   |   FF  |
|   8   |   G   |   GG  |
--------------------------

I want to convert my Excel file to JSON like

{
"myValue":
{
"1":"1",
"2":"2"
},
"myValue2":
{
"A":"A",
"B":"B"
},
"myValue3":
{
"AA":"AA",
"BB":"BB"
}
}

I already have a JSON file like this, so I should append values from Excel to that file. It should add values under "myValue" add to another JSON file under the "myValue" in that file. I already tried some solutions from site, but they did not work for me. Also, the problem is that myValue, myValue2, myValue3 are not always in the same order as displayed here. The ideal solution would be to find myValue in JSON file which already contains it and add values in it directly from Excel row which contains the same value.

CodePudding user response：

This works

# Importing dependencies 
import pandas
import json

# Reading xlsx into pandas dataframe
df = pandas.read_excel('../Data/18-12-21.xlsx')

# Encoding/decoding a Dataframe using 'columns' formatted JSON
jsonfile = df.to_json(orient='columns')

# Print out the result
print('Excel Sheet to JSON:\n', jsonfile)

# Make the string into a list to be able to input in to a JSON-file
json_dict = json.loads(jsonfile)

# write from and file to write to
with open('data.json', 'w') as json_file:
    json.dump(json_dict, json_file)

Output

{
    "myValue": {
        "0": 1,
        "1": 2,
        "2": 4,
        "3": 5,
        "4": 6,
        "5": 7,
        "6": 8
    },
    "myValue2": {
        "0": "A",
        "1": "B",
        "2": "C",
        "3": "D",
        "4": "E",
        "5": "F",
        "6": "G"
    },
    "myValue3": {
        "0": "AA",
        "1": "BB",
        "2": "CC",
        "3": "DD",
        "4": "EE",
        "5": "FF",
        "6": "GG"
    }
}