Power BI(Power Query)Web请求的结果为"CR必须后跟LF".错误 [英] Power BI (Power Query) Web request results in "CR must be followed by LF" Error

问题描述

当您使用Web.Page(Web.Contents('url'))函数从网页中读取表时，某些站点将由于换行不一致而导致错误.

When you use the Web.Page(Web.Contents('url')) function to read a table from a web page, some sites will cause an error due to inconsistent linefeeds.

DataSource.Error:服务器提交了违反协议的协议. Section = ResponseHeader Detail = CR后必须加上LF

DataSource.Error: The server committed a protocol violation. Section=ResponseHeader Detail=CR must be followed by LF

似乎没有任何选项可以传递给Web函数以忽略这些错误.

There doesn't appear to be any option you can pass to the Web functions to ignore those errors.

此方法可使用一小段时间，但无法保存/刷新:

This method works for a short while, but doesn't survive a save/refresh:

let
   BufferedBinary = Binary.Buffer(Web.Contents("http://vote.sos.ca.gov/returns/president/party/democratic/county/all")),
   CleanedUp = Text.Replace(Text.Replace(Text.FromBinary(BufferedBinary), "#(cr,lf)", "#(lf)"), "#(lf)", "#(cr,lf)"),
   Table = Web.Page(CleanedUp)
in
   Table

推荐答案

它看起来像其他代码示例一样有效，但这可能意味着它尚未运行.

It might look like the other code sample works, but that probably means it just hasn't been run yet.

两个库函数Web.Contents('url')和Web.Page(Web.Contents('url'))使用不同的HTTP客户端.

The two library functions Web.Contents('url') and Web.Page(Web.Contents('url')) use different HTTP clients.

Web.Contents使用一个非常基本的HTTP客户端，但是在未转换的Web.Contents上的Web.Page将使用IE浏览器直接加载URL. IE对于任何HTTP协议错误都非常宽容，但是基本的HTTP客户端更加严格，这会导致您看到错误.

Web.Contents uses a pretty basic HTTP client, but Web.Page over a non-transformed Web.Contents will instead use the IE browser to load the URL directly. IE is very forgiving for any HTTP protocol errors, but the basic HTTP client is much more strict, which causes the error you see.

通过在之间"调用Binary.Buffer或Text.Replace，将跳过优化，其中Web.Page直接使用浏览器，而是使用Web.Contents发出Web请求，然后将这些字节加载到浏览器.

By calling Binary.Buffer or Text.Replace in "between," that will skip the optimization where Web.Page uses the browser directly, and it instead uses Web.Contents to make the web request and then loads those bytes into the browser.

因此在这种情况下，如果您调用:

So in this case if you invoke:

Web.Page(Web.Contents("http://vote.sos.ca.gov/returns/president/party/democratic/county/all"))

您将获得一个不错的结果表，但是"From Web"将被破坏，因为Web.Contents('url')本身将出错.

You'll get a good result table, but "From Web" will be broken because the Web.Contents('url') by itself will error.

由于您共享了URL，因此我可以尝试 http://vote.sos.ca.gov/returns/president/party/democratic/county/all 并遇到相同的问题.

Since you shared the URL, I was able to try http://vote.sos.ca.gov/returns/president/party/democratic/county/all and get the same problem.

潜在的问题是HTTP响应标头中的第四个cookie，中间有一个无效的十六进制字符0x01:

The underlying problem is the fourth cookie in the HTTP response header has an invalid hex character 0x01 right in the middle:

也许这是 http://vote.sos.ca.gov/可以做的事情修复他们的服务器?我尝试在他们的联系我们"页面上提交错误报告"，但我不确定这是正确的渠道...

Maybe this is something that http://vote.sos.ca.gov/ can fix on their server? I tried submitting a "bug report" on their Contact Us page but I'm not sure that is the right channel...

顺便说一句，我们的HTTP客户端库没有给出非常好的错误消息，CR或LF字符没有错.即使存在，在这种情况下，响应正文上的Text.Replace也无法解决，因为问题出在HTTP响应标头中.

BTW, our HTTP client library doesn't give very good error messages, there's nothing wrong with CR or LF characters. Even if there was, there's nothing that Text.Replace over the response body can fix in this case because the problem is in the HTTP response headers.

这篇关于Power BI(Power Query)Web请求的结果为"CR必须后跟LF".错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！