How to convert String excel string into JSON for large data file-CodePudding

I've data like this how I can convert String to JSON

CodePudding user response：

I am not sure how you are getting that, but you can create a vba macro that iterates (with do while) over each cell and build a json string in the format "property": cellValue(i). Then get the full string as json formatted: { yourFullString }

CodePudding user response：

JSON is a key/value format, so I'm assuming you want your value to be what's in the image, but what about your key?

For Python, take a look at openpyxl. Here's an example of how you might loop through all of those rows:

import json
import openpyxl

xl_wb = openpyxl.load_workbook('myxl-file.xlsx)
xl_doc = xl_wb.active

first_row = 1
last_row = 10
values = {}

for i in range(first_row, last_row):
   cell = xl_doc.cell(row=i, column=1)
   values[i] = cell.value

json_from_xl = json.dumps(values)

CodePudding user response：

In python,

df = pd.read_excel('sample.xlsx')

and then df.to_json(orient="values")

options for orient

‘split’ : dict like {‘index’ -> [index], ‘columns’ -> [columns], ‘data’ -> [values]}

‘records’ : list like [{column -> value}, … , {column -> value}]

‘index’ : dict like {index -> {column -> value}}

‘columns’ : dict like {column -> {index -> value}}

‘values’ : just the values array

‘table’ : dict like {‘schema’: {schema}, ‘data’: {data}}