CODEWITHSUNDEEP
pythonjson
Subhaac
Subhaac

Reputation: 437

Parsing JSON in Python and writing to Excel file

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.

Upvotes: 0

Views: 2089

Answers (2)

Sanchit
Sanchit

Reputation: 3289

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()

Upvotes: 0

Mike Scotty
Mike Scotty

Reputation: 10782

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).

Upvotes: 2

Related Questions