Forum Discussion
Helper IVWe found extra characters at the end of JSON input.
Getting this error when doing a "from the web"
Details: "We found extra characters at the end of JSON input."
When I send the JSON to a validator it says that it is fine. How can I get more specific details on why it is complaining? There is no further detail in the trace file.
43 Replies
Found the same issue when trying to load a JSONL file (http://jsonlines.org/). To solve it, I ended up importing it as a text file (which takes one record per line) and then using the Parse function in PowerQuery to parse the JSON in each line.
how do you import as text from a site?
I have the below codes:
let
url = "https://ws.appointment-plus.com/Appointments/" & "GetAppointments",
headers = [#"Authorization" = "Basic Secretcode", #"Content-Type" = "application/json"],
postData = Json.FromValue([x = 235.7, y = 41.53]),
response = Web.Contents(
url,
[
Headers = headers,
Content = postData
]
),
jsonResponse = Json.Document(response)
in
jsonResponse
jPinhaoAdvocate II
I've had a similar issue before. In our case, the input was actually JSONL (json lines or something), and it was basically missing start and end braces around the full json content, as well as commas at the end of each line, to parse it as an array of json objects. Not sure if you're problem is the same, but it might be something along the same lines -> valid json, just not wrapped together properly.
Hope that leads you in the right direction :)
intrasightNope, it's regular JSON. And it validates with every tool that I've thrown it at. Does Power BI have some "special" JSON needs?
- intrasight
I noticed that the section of JSON containing the data table had no line breaks between rows. After adding line breaks, I no longer get the above error message. This leads me to believe that a) there is some limitation in the JSON parser used by Power BI, and b) this limitation manifests itself with an incorrect error message.
Thansk for the tip, and by the way, braces = Square braces
- DolEgon22
This is not unique to Power BI, however—it appears that this is also reproduced with Excel connecting to the blobs created from stream analytics. It appears that while other services can parse the JSON from ASA, PBI and Power Query cannot.
My troubleshooting steps:
- Using a 2 line sample from one of the JSON files, I run it through a parser and get parser errors (expecting 'EOF')
- When I wrap the JSON in square brackets and separate the lines with a comma, it parses fine
- I downloaded one of the JSON files and made 2 copies of it. I then edited one to have the brackets and comma delimiter and left the other one alone. I was unable to load the unedited JSON into PBI, but had no problem with the one I edited.
This appears to be an issue with how Power Query parses JSON from ASA.
- DolEgon22
Just a side note--my steps above don't solve our problem of connecting to Azure Blob Storage using PBI and automatically parsing the JSON stored there... my steps simply reveal the issue in PBI/Power Query.
I was able to enter an advanced query to resolve my problem.
I had to replace carriage returns with commas and surround the entire thing with a bracket to form a valid json doc.
let
Source = DataLake.Contents("https://SOMETHINGHERE.azuredatalakestore.net"),
TwitterData = Source{[Name="TwitterData"]}[Content],
#"TwitterStream-Output_0_9999 json" = Text.Replace(Text.Combine({"[", Text.FromBinary(TwitterData{[Name="TwitterStream-Output_0_9999.json"]}[Content]), "]" }), "#(cr)", ","),
#"Imported JSON" = Json.Document(#"TwitterStream-Output_0_9999 json",1252),
#"Imported JSON1" = #"Imported JSON"{0}
in
#"Imported JSON1"Still no definitive solution for this problem? I want to read multiple JSON files from a folder. Individual JSON files work fine, but reading them all in from a folder gives me the same error!
- Hennadii
I faced the same error message in Excel - Power Query editor.
A reason for that issue was wrongly selected VPN. After switching to a correct one that message is gone and a query is executed successfylly.
Hope it may help to someone )In my query, I don't select any VPN. Can you explain further? thanks
- Hennadii
I didn't use any syntax in my query related to VPN. It is just a network setting.
In my case, a json data was coming from the web. Here is a simplified version of my query:
let Url = "https://somesite.com/content.json", RawData = Web.Contents(Url), Json = Json.Document(RawData), Value = List.First(Record.ToList(Json)) ConvertedToTable = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in ConvertedToTableThis query returns a table only for one VPN setting while for another one it returns the error: DataFormat.Error: We found extra characters at the end of JSON input.
It was difficult to identify the root of the issue as simple typing in browser url returns json even with connected to "wrong" VPN.
Was there a definitive answer to this question? I'm trying to load data from a .json file, and while all editors I can think of have no issues with it Power BI throws the error. Is there a log file somewhere that would point to what is causing this error, other than referencing the "extra character"?
EvogelpohlHelper V
I'm using Azure Stream Analytics to take IoT data and send it to Azure blog storage as a JSON file.
When I attempt to open the JSON file from within PowerBi Desktop (azure blob), i get this same error.
If i download the JSON file to my desktop and open it from there, i get the same error.
Solution?
The same issue is here. the same url output (KSON) saved in text and is working bit while directly placing , then not working.
does this have a solution?
does this have a solution? i have same error
Does anyone have solution to this.
I'm stuck with the same problem. I'm trying to build a Power BI custom connector, and I'm not even sure that the request by Web.Contents is executed, since I've tried inserting the json as a string in the Power Query and that works, but if I place the same string as a json-file on App Service or as Blob it fails. I'm stranded with with an option on how to trace/debug what really happens.
In my case it's working beautifully in Power BI Desktop, but for some reason I can't get it to run in the Visual Studio for the custom connector project.
Any ideas and feedback is most welcome as I expect this to be a simpel error or misconfiguration on my side, more than an error in the SDK.
Brgds Brian
