Dec 17 2017 05:39 PM
Dec 17 2017 05:39 PM
Using the JSON files provided on the Yelp website, https://www.yelp.com/dataset. When attempting to connect to the files via Excel's "Get Data" or Power BI, and error message appears stating "We found extra characters at end of JSON line."
Attached is a sample of the data.
Any ideas why the error message is being returned?
Thank you in advance!
Dec 18 2017 03:04 AM
Hi Ron,
I'm not familiar with JSON, but seen what the issue is discussed not first time, e.g. https://community.powerbi.com/t5/Desktop/We-found-extra-characters-at-the-end-of-JSON-input/td-p/206... . Perhaps Power Query parser doesn't support all modifications of JSON file.
If validate your file here https://www.freeformatter.com/json-validator.html it returns "The JSON input is NOT valid according to RFC 4627 (JSON specification)."
Parser starts working if add the object on the top of your file and separate array by commas, e.g.
{"businesses":[ {"business_id": "..... ,{"business_id": "..... ,{"business_id": "..... ]}
Attached file is parsed correctly.
No practical solution but perhaps will give you the direction.
Dec 18 2017 09:16 AM
Hi Sergei,
Thank you for your insight and reply! I will apply your suggestion and reply back on the results.
Dec 18 2017 09:28 AM
Dec 18 2017 09:28 AM
I bracketed the JSON text with a
{"businesses":[
...
]}
The same MIcrosoft Excel/Power BI error notice was displayed: "We Found Extra Characters at End of JSON Line".
Within the scope of Microsoft's JSON parser I am left with impression that it cannot handle nested levels of data objects.
Ron
Dec 18 2017 10:05 AM
Hi Ron,
Not sure about conversion of JSON to CSV, perhaps that also could help. However, your error is not since records in the array have different sets of elements. My vision
- PQ parser always works if you have only one record in the array;
- if you have more than one record to parse JSON correctly you shall separate records in the array by comma and add on the top the name of the object.
In the attached archive Excel file parses you initial JSON modified as above. You only have to expand nested records one by one. If some elements are missed null will be returned in related column.
Here is also your sample with 2 records, initial one and updated as above. First one returns extra character error, second one works.
In addition, if generate M script manually with not-updated JSON and click on error you'll see it points on the first after character of the second record, doesn't matter how many records you have and do they have exactly the same structure or not.
Dec 18 2017 10:07 AM
Ron, I guess you forgot to separate records by comma. Please see files in my previous post.
Dec 18 2017 11:10 AM
Sergei,
You are absolutely correct.
Used Sublime Text to edit my JSON file to add the header and footer lines, plus the commas at the beginning of each new line as you suggested. I then attempted a connection to the edited JSON file via Excel. This time, success!
Thank you!!
May 25 2020 09:40 PM
Can you please shore this solution in pictures with me. i am facing same error.
Aug 04 2020 02:57 PM
Forever old, but I was trying to ingest an AWS CloudCheckr JSON into Excel via Power Query and getting the "We found extra characters at the end of JSON input."
Finally figured out, with the help of https://jsonformatter.org/ that some data was provided as True -- without quotes, which Excel PQ needed. Simple find/replacing :True, with :"True", did the trick.
Seriously, Microsoft, you did not recognize Boolean when you found it? Excel would have had no problem.
May 28 2021 12:19 AM
@Jedi-X Microsoft's handling of Boolean values in JSON is not very friendly, I also found this problem in the process of JSON verification JSON Formatter