We Found Extra Characters at End of JSON Line

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:

 

 

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:

 

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