Parsing JSON in Python and writing to Excel file

Question

I have the following JSON saved in a text file called test.xlsx.txt. The JSON is as follows:

{"RECONCILIATION": {0: "Successful"}, "ACCOUNT": {0: u"21599000"}, "DESCRIPTION": {0: u"USD to be accrued. "}, "PRODUCT": {0: "7500.0"}, "VALUE": {0: "7500.0"}, "AMOUNT": {0: "7500.0"}, "FORMULA": {0: "3 * 2500 "}}

The following is my python code:

f = open(path_to_analysis_results,'r')
message = f.read()
datastore = json.loads(str(message))
print datastore
f.close()

With the json.loads, I get the error "ValueError: Expecting property name: line 1 column 21 (char 20)". I have tried with json.load, json.dump and json.dumps, with all of them giving various errors. All I want to do is to be able to extract the key and the corresponding value and write to an Excel file. I have figured out how to write data to an Excel file, but am stuck with parsing this json.

RECONCILIATION : Successful
ACCOUNT : 21599000
DESCRIPTION : USD to be accrued.
PRODUCT : 7500.0
VALUE : 7500.0
AMOUNT : 7500.0
FORMULA : 3 * 2500

I would like the data to be in the above format to be able to write them to an Excel sheet.

Mike Scotty · Accepted Answer · 2017-11-27 10:34:55Z

2

Your txt file does not contain valid JSON.

For starters, keys must be strings, not numbers.

The u"..." notation is not valid either.

You should fix your JSON first (maybe run it through a linter such as https://jsonlint.com/ to make sure it's valid).

answered Nov 27, 2017 at 10:34

Mike Scotty

10.8k5 gold badges41 silver badges52 bronze badges

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

Comments

Sanchit · Accepted Answer · 2017-11-27 10:54:48Z

0

As Mike mentioned, your text file is not a valid JSON. It should be like:

{"RECONCILIATION": {"0": "Successful"}, "ACCOUNT": {"0": "21599000"}, "DESCRIPTION": {"0": "USD to be accrued. "}, "PRODUCT": {"0": "7500.0"}, "VALUE": {"0": "7500.0"}, "AMOUNT": {"0": "7500.0"}, "FORMULA": {"0": "3 * 2500 "}}

Note: keys are within doube quotes as JSON requires double quotes. And, your code should be (without str()):

import json

f = open(path_to_analysis_results,'r')
message = f.read()
print(message) # print message before, just to check it.
datastore = json.loads(message) # note: str() is not required. Message is already a string
print (datastore)
f.close()

answered Nov 27, 2017 at 10:54

Sanchit

3,3098 gold badges39 silver badges55 bronze badges

Collectives™ on Stack Overflow

Parsing JSON in Python and writing to Excel file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related