Convert excel to json python

Question

I have an excel sheet with two columns and I want to convert it into json.

I want to convert this into a json format like this:

{"A": "This is a Sentence", "B": "1"},
{"A": "This is a Sentence", "B": "2"},
{"A": "This is a Sentence", "B": "3"},
{"A": "This is a Sentence", "B": "4"}

I tried using this command:

data= df.to_json (r'C:\Users\Admin\df_json.json',orient='split')

but it generates something like this:

[{"A": "This is a Sentence", "B": "1"},{"A": "This is a Sentence", "B": "2"},{"A": "This is a Sentence", "B": "3"},{"A": "This is a Sentence", "B": "4"},]

No, it is generated as a list,inside a square bracket. I don't want the square bracket. — user8306074
– user8306074, Commented Aug 6, 2019 at 11:00

Felk · Accepted Answer · 2021-07-05 09:30:01Z

2

The result you are getting is normal json. What you described as your desired result is json lines (also called jsonlines, jsonl or similar). There's no built-in support for it as far as I know, but if you want to output json lines it's pretty easy to do by hand. Here's an example on how you would write json lines to a file:

data = df.to_json (r'C:\Users\Admin\df_json.json', orient='split')
with open("converted.jsonl", mode="wt", encoding="utf-8") as f:
    for line in data:
        f.write(json.dumps(line) + "\n")

edited Jul 5, 2021 at 9:30

answered Aug 6, 2019 at 11:05

Felk

8,3032 gold badges41 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user8306074 Over a year ago

this gives an error: "'NoneType' object is not iterable" at line 3 which means 'data' is of type None. Am I doing something wrong ?

Collectives™ on Stack Overflow

Convert excel to json python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related