none
Power Query -We found extra characters at the end of JSON input RRS feed

  • Question

  • Hi ,

    I have the below kind of output from API. When i try using the web output is returned as a document. So i saved the output in .Json file and it works perfectly.

    Next i changed the source to the API in the code while using the file. i get the error- "We found extra characters at the end of JSON input"

    Below is the code when using source file.

    let

        Source = Json.Document(File.Contents("H:\winnt\Desktop\new 2.json")),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ABC", "runId"}, {"Column1.ABC", "Column1.runId"})
    in
        #"Expanded Column1"

    I just changed the source in the above to API link and it failed with the error.

    let
        Source = Json.Document(Web.Contents("https://xyz.com")),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ABC", "runId"}, {"Column1.ABC", "Column1.runId"})
    in
        #"Expanded Column1"

    data

    [ {
      "ABC" : "text_1",
      "runId" : 625
    }, {
      "ABC" : "text_2",
      "runId" : 626
    } ]

    Thanks,

    Akul Minocha


    Monday, July 10, 2017 12:19 PM

Answers

  • It looks like you're being redirected to a login site when you use Web.Contents, so your call needs to also provide the login information somehow. Without referring to the API documentation, it's difficult to know exactly what the requirements are, but you might want to try something like this:


    let
        username = <<YOUR USERNAME>>,
        password = <<YOUR PASSWORD>>,
        authstr = "Basic " & Binary.ToText(Text.ToBinary(username & ":" & password),0),
        URL = "https://www.example.com",
        options = [ Headers = [ #"Authorization" = authstr ]],
    
        Source = Json.Document(Web.Contents(URL, options))
    in
        Source



    Monday, July 17, 2017 4:12 PM

All replies

  • I've seen this error, and when I have, it's been because Web.Contents isn't actually getting data.

    What's the result when you check the metadata, like this:
       metadata = Value.Metadata(Web.Contents("https://xyz.com"))

    My guess is your response code is either a 4xx or 5xx.

    Thursday, July 13, 2017 6:29 PM
  • * The output of Value.Metadata(Web.Contents("https://xyz.com")) a small table comes with response status as 200.

    Also

    * The output of Text.FromBinary(Web.Contents("example.com")) is entirely different , since the website is restricted one the output has "<div class="metaLogin">Sign in to continue </div> <div class="login"> <form action="passwordLogin" method="post" autocomplete="off"> " –
      *The Output in the Web view (image uploaded) though exactly matches the new 2.json 

    also when i use just Data-> from web and enter the URL. The data is extracted correctly.

    Can you please help??


    .
    Saturday, July 15, 2017 3:31 PM
  • It looks like you're being redirected to a login site when you use Web.Contents, so your call needs to also provide the login information somehow. Without referring to the API documentation, it's difficult to know exactly what the requirements are, but you might want to try something like this:


    let
        username = <<YOUR USERNAME>>,
        password = <<YOUR PASSWORD>>,
        authstr = "Basic " & Binary.ToText(Text.ToBinary(username & ":" & password),0),
        URL = "https://www.example.com",
        options = [ Headers = [ #"Authorization" = authstr ]],
    
        Source = Json.Document(Web.Contents(URL, options))
    in
        Source



    Monday, July 17, 2017 4:12 PM
  • The Tab "Web view" shows the output correctly as expected. I am not sure it would be redirecting to page for authentication?

    Thanks,

    Akul Minocha

    Wednesday, August 16, 2017 11:49 AM