Forum Discussion
DataFormat.Error: We found extra characters at the end of JSON input.
Hello all,
Been working around for a few days with PowerBI now and got a lot of stuff working allready. Have used variouse Visualzations and connecting to large volume SQL Server, Azure SQL databases works like a charm.
But when I import a large JSON file with data I got a error. The file is a 49Mb JSON file with 63774 records and when I loaded it I got this error :
DataFormat.Error: We found extra characters at the end of JSON input.
Details:
Value=
Position=1681799
So I took a small snip of the file to setup the datasource in the query editor. And after a few tests and making the dasboard it was time to work with the real file. I found out that the when I'm below this value it does work (output.json size 1.681.786 ).
So my questions are :
1. is there a size limit to a JSON file ?
2. is it a know issue / bug (I have look on variouse places but can't find any refference to this).
5 Replies
Tested it today also for Azure blob storage. Same problem.
Four months later and nobody has responded to this. We're running into the exact same issue when using Azure Blob Storage as a datasource (even CSV).
DataMashup.Trace Warning: 24579 : {"Start":"2017-08-14T19:17:20.5789351Z","Action":"SimpleDocumentEvaluator/GetResult","HostProcessId":"17348","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Runtime.ValueException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: [DataFormat.Error] We found extra characters at the end of JSON input.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Json.JsonParser.Parse(JsonTokenizer tokenizer)\r\n at Microsoft.Mashup.Engine1.Library.Json.JsonModule.Json.DocumentFunctionValue.TypedInvoke(Value jsonText, Value encoding)\r\n at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue2`3.Invoke(Value arg0, Value arg1)\r\n at Microsoft.Mashup.Engine1.Language.DebugExpression.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.FunctionInvocationExpression2.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.DebugExpression.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.ExpressionInvocationExpression2.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.DebugExpression.Execute(MembersFrame1& frame)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Runtime.Library.Linker.BindFunctionValue.TypedInvoke(RecordValue environment, Value section, TextValue name)\r\n at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue3`4.Invoke(Value arg0, Value arg1, Value arg2)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()\r\n at Microsoft.Mashup.Engine1.Language.ListExpression.RuntimeListValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.ListExpression.RuntimeListValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.FunctionInvocationExpression2.Execute(Value frame)\r\n at Microsoft.Mashup.Engine1.Language.Expression.ExecuteCondition(Value frame)\r\n at Microsoft.Mashup.Engine1.Language.DebugExpression.ExecuteCondition(Value frame)\r\n at Microsoft.Mashup.Engine1.Language.IfExpression.Execute(Value frame)\r\n at Microsoft.Mashup.Engine1.Language.DebugExpression.Execute(Value frame)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.Force(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.get_Item(Int32 index)\r\n at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()\r\n at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)\r\n\r\n\r\n","ProductVersion":"2.49.4831.521 (PBIDesktop)","ActivityId":"e683962d-0763-4d48-978f-e63be7e527c8","Process":"Microsoft.Mashup.Container.NetFX40","Pid":12636,"Tid":1,"Duration":"00:00:00.0200926"}
I'm also looking for and answer to this.
Hi all,
I had the same issue but I was making the called with the function and from a different query. The issue is to parse the call before it gets the JSON, The other way is to have the JSON open as text and parse it using a technique mention in a post from ExcelGuru: https://www.excelguru.ca/blog/2015/11/19/keep-only-numbers-in-power-query/They remove nothing but numbers I change to nothing but Special characters, you can add more if you want to here is my code for the list that you load before doing any changes.
CharsToRemove = List.Transform({33..45,47}, each Character.FromNumber(_))Then, you can add a custom column:
"Column name" Text.Remove([Call elementclean],CharsToRemove)
This case not sure if it works from Azure SQL like this but you should try it in SQL.
Hope this help someone.
Best,
J
