how to add customized fields before saving the data to json

Question

I have a simple python program which converts excel to json .

import  jpype     
import  asposecells     
jpype.startJVM() 
from asposecells.api import Workbook
workbook = Workbook("sample.xlsx")
workbook.save("sample3.json")
jpype.shutdownJVM()

And i get the output as below

[
 {
  "Name": "DS",
  "Gender": "M"
 },
{
  "Name": "DS1",
  "Gender": "M"
 },
]

Instead i want the output to have some extra (hardcoded) fields and word data appended

[
 {
  "date": "06/05/2022",
  "data":{
    "Name": "DS",
    "Gender": "M"
    }
 },
 {
  "date": "06/05/2022",
  "data":{
   "Name": "DS1",
   "Gender": "M"
  }
 },
]

Any insights on aspose-cells ? i referred the aspose docs , but didnt find anyways to do it. Any help will be appreciated. Thanks in advance.

score 1 · Accepted Answer · answered Jun 05 '23 at 18:33

1

Instead of saving it to a json, convert it to a python object, modify it and save. If you cannot use that library to create a json string/python object, you can load that file with the json library and make the necessary changes:

import json
json_list = json.load("sample3.json")
new_list = []

for l in json_list:
    #do_whatever, for example...
    new_list.append({"data": l, "date": "1-1-2020"})

with open('sample3_extended.json', 'w') as outfile:
    json.dump(new_list, outfile)

The formatting of your hardcoded example is wrong, I guess you want a list of dictionaries like this:

[
 {
  "date": "06/05/2022",
  "data":{
    "Name": "DS",
    "Gender": "M"
    }
 },
 {
  "date": "06/05/2022",
  "data":{
   "Name": "DS1",
   "Gender": "M"
  }
 },
]

answered Jun 05 '23 at 18:33

100tifiko

361
1
10

is there a way to get above one in NDJSON file ? something like below ``` { "date": "06/05/2022", "data":{ "Name": "DS", "Gender": "M" } } { "date": "06/05/2022", "data":{ "Name": "DS1", "Gender": "M" } } ``` – dishanm Jun 07 '23 at 17:07
I don't think so, if you are trying to create a list of dicts, why don't you want the list brackets? – 100tifiko Jun 07 '23 at 17:58
Needed in the format for some data load (each json to be loaded as new line) – dishanm Jun 07 '23 at 18:37
In that case you can save it as a txt file instead of json. Then in each line you add a string in json format (using `json.dumps`). When you load the file, you just need to read it as json with `json.loads`. Let me post a new answer... – 100tifiko Jun 07 '23 at 19:03

score 0 · Answer 2 · answered Jun 07 '23 at 19:18

I would recommend to use the approach from my other answer (that is why I post a new answer instead of editing), load the json file and iterate in the list.

However, if you need 100% "independent" json dicts per line, you can save the info as a txt file, adding one dict per line following json standards.

Then you can read one line at a time and it will be json compatible (depending on how you use it you will need to get rid of the \n, transform the string to json (json.loads(my_dict_string) in Python, etc.

To create the txt file:

import json
json_list = json.load("sample3.json")
new_list = []

for l in json_list:
    #do_whatever, for example...
    my_dict = {"data": l, "date": "1-1-2020"}
    new_list.append(json.dumps(my_dict))


with open('sample3_extended.txt', 'w') as outfile:
    outfile.write('\n'.join(new_list))

This will create a txt file like this:

# json_list = [1,2,3,4]
{"data": 1, "date": "1-1-2020"}
{"data": 2, "date": "1-1-2020"}
{"data": 3, "date": "1-1-2020"}
{"data": 4, "date": "1-1-2020"}

how to add customized fields before saving the data to json

2 Answers2