Excel - We found extra characters at the end of JSON input

Question

I am trying to import my json file to excel via the get data function. When doing this i get an error saying that "We found extra characters at the end of JSON input"

i ran the json file in jsonformatter and got this additional piece of information:

Parse error on line 1: ...s":"1555615338756"} {"created_at":"Thu A -----------------------^ Expecting 'EOF', '}', ',', ']', got '{'

Edit: line 1

{"created_at":"Thu Apr 18 19:22:18 +0000 2019","id":1118957948263206913,"id_str":"1118957948263206913","text":"Arsenal jersey looks weird. #NapoliArsenal","source":"\u003ca href=\"https://mobile.twitter.com\" rel=\"nofollow\"\u003eTwitter Web App\u003c/a\u003e","truncated":false,"in_reply_to_status_id":null,"in_reply_to_status_id_str":null,"in_reply_to_user_id":null,"in_reply_to_user_id_str":null,"in_reply_to_screen_name":null,"user":{"id":955479430377373696,"id_str":"955479430377373696","name":"Yash Iyer","screen_name":"MesutOziI28","location":"Bengaluru South, India","url":null,"description":"RM,Nerazzurri,BFC,RCB,bcci,rafa nadal and so on! Lately into B99,superstore! Sympathetic liker of tweets!","translator_type":"none","protected":false,"verified":false,"followers_count":258,"friends_count":454,"listed_count":0,"favourites_count":47788,"statuses_count":5318,"created_at":"Mon Jan 22 16:37:02 +0000 2018","utc_offset":null,"time_zone":null,"geo_enabled":false,"lang":"en","contributors_enabled":false,"is_translator":false,"profile_background_color":"F5F8FA","profile_background_image_url":"","profile_background_image_url_https":"","profile_background_tile":false,"profile_link_color":"1DA1F2","profile_sidebar_border_color":"C0DEED","profile_sidebar_fill_color":"DDEEF6","profile_text_color":"333333","profile_use_background_image":true,"profile_image_url":"http://pbs.twimg.com/profile_images/1109886916609007616/9rAavtGh_normal.jpg","profile_image_url_https":"https://pbs.twimg.com/profile_images/1109886916609007616/9rAavtGh_normal.jpg","profile_banner_url":"https://pbs.twimg.com/profile_banners/955479430377373696/1544903252","default_profile":true,"default_profile_image":false,"following":null,"follow_request_sent":null,"notifications":null},"geo":null,"coordinates":null,"place":null,"contributors":null,"is_quote_status":false,"quote_count":0,"reply_count":0,"retweet_count":0,"favorite_count":0,"entities":{"hashtags":[{"text":"NapoliArsenal","indices":[28,42]}],"urls":[],"user_mentions":[],"symbols":[]},"favorited":false,"retweeted":false,"filter_level":"low","lang":"en","timestamp_ms":"1555615338756"}

Answer 1

The answer is in the error message:

Expecting 'EOF', '}', ',', ']', got '{'

Looking at where { appears notice that directly before that is }. The JSON has no separator after the closing curly } and thus cannot process it because it's looking for one of the following:

• EOF
• }
• ,
• ]

In this case, it most likely needs a ,. It could also need ],, if it's an array of items. If neither of those fix it, you will need to post the entire line 1 of your JSON.

Answer 2

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.

Answer 3

I had to do two changes to make this work: changed json body to Pascal case notation even thought it was in Camel case in chrome payload , and secondly I was returning a simple string to verify the service is working - once I changed that to an object (as a json formatted response) this resolved the error I was getting.