I am trying to have Python read a JSON file and export it to a CSV. I am using Pandas for the conversion, but I am getting "ValueError: Expected object or value" when I run the code below.
import pandas as pd
df = pd.read_json ('contacts.json')
I am using Visual Studio Code for testing the script. When I run the above code, I get the message below in the Terminal window.
PS C:\Users\TaRan\tableau-wdc-tutorial-part-1> & "C:/Program Files/Python38/python.exe" "c:/Users/TaRan/Dropbox/Team Operational Resources/G. BI Internal/Testing/Hubspot/conversion.py"
Traceback (most recent call last):
File "c:/Users/TaRan/Dropbox/Team Operational Resources/G. BI Internal/Testing/Hubspot/conversion.py", line 3, in
df=pd.read_json('contacts.txt') File "C:\Program Files\Python38\lib\site-packages\pandas\util_decorators.py", line 199, in wrapper
return func(*args, **kwargs) File "C:\Program Files\Python38\lib\site-packages\pandas\util_decorators.py", line 296, in wrapper
return func(*args, **kwargs) File "C:\Program Files\Python38\lib\site-packages\pandas\io\json_json.py", line 618, in read_json
result = json_reader.read() File "C:\Program Files\Python38\lib\site-packages\pandas\io\json_json.py", line 755, in read
obj = self._get_object_parser(self.data) File "C:\Program Files\Python38\lib\site-packages\pandas\io\json_json.py", line 777, in _get_object_parser
obj = FrameParser(json, **kwargs).parse() File "C:\Program Files\Python38\lib\site-packages\pandas\io\json_json.py", line 886, in parse
self._parse_no_numpy() File "C:\Program Files\Python38\lib\site-packages\pandas\io\json_json.py", line 1119, in _parse_no_numpy
loads(json, precise_float=self.precise_float), dtype=None ValueError: Expected object or value
I thought it might be a problem with the JSON file, so I wrote a different one, but I still received the error. To me, it looks like something might be wrong with the Panadas package. I tried reinstalling it, but I still get the error.
EDIT
Here is a sample from the JSON file. I am displaying only one contact and changed the confidential information.
{"results":[{"id":"101","properties":{"createdate":"2020-06-05T15:18:37.746Z","email":"[email protected]","firstname":"First","hs_object_id":"101","lastmodifieddate":"2020-08-12T15:17:35.104Z","lastname":"Last"},"createdAt":"2020-06-05T15:18:37.746Z","updatedAt":"2020-08-12T15:17:35.104Z","archived":false}],"paging":{"next":{"after":"452","link":"https://api.hubapi.com/sampleurl.com"},"prev":null}}
I am getting the JSON file from the Hubspot API. I am not doing any kind of formatting before pulling it into Python for the conversion (nor do I want to - I am trying to automate this entire process). Please note that my JSON is all on one line. I am not sure if this matters or not.