Forum Discussion

Deleted's avatar
Deleted
Dec 18, 2017

We Found Extra Characters at End of JSON Line

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!

BI & Data Analysis
excel
power bi

9 Replies

Sort By
  • Jedi-X's avatar
    Jedi-X
    Copper Contributor
    Aug 04, 2020

    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.

    • keka2340's avatar
      keka2340
      Copper Contributor
      May 28, 2021

      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 

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Dec 18, 2017

    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/20607/page/2 . 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.

     

     

    • Deleted's avatar
      Deleted
      Dec 18, 2017

      Hi Sergei,

       

      Thank you for your insight and reply!  I will apply your suggestion and reply back on the results.

       

      I was looking more closely as the Business.json file's content.  There are nested levels of data (e.g. attributes) that are inconsistent from one line to another.  For instance the second line has information about garage parking which the first line does not.  If I were to "guess" what the error message, "We found extra characters at end of JSON line", means is that it doesn't understand what to do with the extra data objects found in the second line and not in the first.
       
      To test this theory opened Business.json into the Notepad app.  I then deleted all lines after the fifth or so line (see attached TESTbusiness.json).  Attempted to load the file into Excel and Power BI Desktop.  Same error message appeared.  I then deleted all lines except for the first one (see attached TEST2business.json)  This file loaded just fine, as you can seen in this screen shot:
       
      Inline image 1
       
      I then added back the second line from the json file (see attached TEST3business.json).  When I attempted to load this file the error message returned:
      Inline image 2
       
      Based upon these simple tests, what have we learned?  Essentially two things:
         1. The Excel/Power BI JSON parser cannot handle lines with inconsistent data objects
         2. A JSON to CSV conversion wouldn't solve the issue.
       
      Excel/Power BI are built upon tabular models where data is arranged in nice columns and rows.  As they are downloaded the Yelp data files simply don't fit into that schema.
       
      With all of that said your suggestion may resolve it all.  THANKS!
       
      Ron
      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Dec 18, 2017

        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.

Resources