Power Query网抓详解

首先Power Query并不是一个专门的网抓或者爬虫工具,没有编程语言那么专业,实现的功能也比较有限,但其优势就是简单易学,且无缝对接excel,所见即所得。
网上关于网抓的资料有很多,但没有用PQ实现的,之前写过一篇《Power Query网抓案例》,今天来详细讲讲。
本文将以纯新手的角度,介绍一些基础的网抓知识,尽可能让每个人都能学会。

网抓主要分为三个步骤:1、抓包并分析请求,2、构建并发送请求,3、对返回的数据清洗。
首先来看一个最简单的案例,http://quote.stockstar.com/stock/ranklist_a_3_1_1.html,这是一个静态页面,以html结尾,当你点击不同栏目不同页码的时候,URL也在相应的发生变化。
对服务器来说,静态页面是实实在在保存在服务器上的文件,每个网页都是一个独立的文件。所以比如我们要抓某一页的数据,只需要点击新建查询-从其他源-自网站,把对应的URL输入进去就可以了,这和我们导入本地xlsx文件的原理是一样的,至于如何批量抓取多页我们下面再讲。
但是静态页面缺点很明显:没有数据库支持,交互性差,非常不便于维护,所以大多数网站会采用动态页面设计,这就导致了我们想要抓取数据变得没那么简单。

什么是动态页面?打个比方,打开百度首页,搜索关键词powerquery。

URL去掉无关参数精简后为https://www.baidu.com/s?wd=powerquery,搜索不同关键词只是wd=后面的部分在变化。
试想网民搜索关键词的可能性有无穷多,百度会为每一种可能做一个文件放在服务器里么?显然不会。
我们之所以能够看到不同的结果是因为当我们搜索关键词时,会使用GET方式向服务器提交带参数的请求,比如上面?后面的就是参数,wd是字段名,powerquery是值。当服务器接收到参数后,计算并返回给我们相应的结果。

那GET又是什么?这就要从HTTP协议开始说起了。
我们之所以能够使用浏览器访问网页,实际上就是浏览器向服务器提交请求(request),服务器收到请求后返回了响应(response)并把HTML代码发送给浏览器,浏览器解析并显示出来。

如上所说,对于动态页面我们请求的访问地址始终是同一个https://www.baidu.com/s,如果要让服务器知道我们想看什么,就需要一种东西来在客户端与服务器端之间进行消息传递,这就是HTTP协议请求。
HTTP协议请求主要有GET,POST,PUT,DELETE等等,分别对应查、改、增、删四个操作,其中最常见的就是GET和POST。GET仅请求资源,POST会附带一个Body包含用户数据。
GET请求的数据会附在URL之后,以?分割URL和传输数据,参数之间以&相连。上面百度的案例就是GET,再长点的话比如这样https://www.baidu.com/s?wd=power%20query%E7%BD%91%E6%8A%93&pn=10,这里有wd和pn两个参数,以&相连,中间的乱码是中文经过URL encoded转码后的结果,如果再有其他参数则继续在后面追加&即可。
POST对应改,请求提交的数据放置在HTTP包的Body中。比如你在下方发表评论,你并不能通过URL看出来你究竟向我提交了什么,而当你评论完,页面上出现了你的评论,我的网站已经被你修改了,这就是在提交POST请求。当然POST也可以用来查询数据,是一种更加安全的传递方式。

看到这里,我们已经知道了客户端和服务器端是如何进行信息传输的:客户端提交request,服务器返回response。注意我们这里说的是客户端,浏览器算一种,当然还有其他很多种,比如我们今天要讲的Power Query。
那么网抓的原理其实就是,找出我们所需要抓的数据,分析是浏览器向服务器提交了什么请求哪些参数,然后用Power Query构建同样的请求发送给服务器,服务器便会把对应的数据在Power Query中返回给我们。
了解了这个,下面我们就开始进行第一步:抓包。

抓包:

抓包可以用浏览器自带的调试工具,按F12即可调出,比较轻量,但缺点是不能支持一些复杂的抓包,但话说回来Power Query本身就不太支持复杂的网抓,所以基本也够用了。
建议使用Chrome浏览器,下面的案例全部是基于Chrome,所以不要问我其他浏览器怎么找。

以http://221.215.38.136/grcx/kscx/list.action?kscxVo.jsp=ylypmlcx.jsp为例,我们点击下方无论选多少页发现URL都不会变化,那么如何获取到比如第5页的数据呢?按下F12调出调试工具。如果不起作用的话就右击选择检查-然后点击Network。

先简单介绍一下控制台。最上面圈出来的那一排,最常见的Elements显示网页的结构,Network显示浏览器和服务器的通信。
我们选择Network后应该是空的,因为才刚刚呼出控制台监控通信,而之前的行为是没有监控的,下方文字提示"通信记录已激活,请执行request或按F5"。
下面要做的就是让浏览器发送请求,比如你要抓第5页你就点击第5页或者下一页,你要抓其他栏目你就点击对应的栏目,总之目的就是产生查询数据的行为,让浏览器监测到。
如果你的点击让页面产生了变化,那么就会出现一条条记录,每条记录都是一次请求,浏览器会对这些请求按照类型进行分类,如图中框出来的部分所示,我们要做的就是在产生的这么多条请求中找出是哪条请求让服务器返回了我们所要的数据。
其中All是所有类型,数据不可能在CSS,Font之类的里面,按照我的经验可能性DOC>XHR>JS>其他,当记录过多产生干扰时可以点击左上角圈出来的Clear清空重新查找。

下面请动动你们的小手和我一起做如下几个操作:1、打开上面的URL,2、按下F12调出Network窗口,3、拉到页面最下方点击第5页。
按照刚才说的优先级依次找一下:Doc有一条,XHR空的,JS空的,如图所示。

在Headers标签下分为了几个模块:
General中的Requset URL表示请求的访问地址,Request Method表示请求所使用的方法为GET,所以我们可以通过这里判断是GET还是POST。
Response Headers是服务器返回的响应头,因为我们目前主要是想构建请求所以这部分内容不重要。
Request Headers是请求头,其中Cookie经常会用到,Referer有可能会用到,User-Agent是让服务器识别客户的设备信息的参数,在其他语言的复杂网抓下有可能用到,但在PQ中基本用不到,这个后面再说。顺便说一下,当你访问一个网站的时候,你电脑什么系统、用的什么浏览器、显示器多大、分辨率多少、浏览轨迹这些信息已经全部暴露给对方了,想想是不是感觉很恐怖。
Query String Parameters是查询参数,因为是GET方法,所以这些参数已经全部在最上面的Requset URL的?后面了。
这里所看到的信息是已经经过解析的,在每个模块的右边还有一两个小按钮,view source表示查询源代码,view URL encoded表示查询转码后的。

讲了这么多,那么我们如何确定目前所看到的这条记录是不是就是能够返回我们想要数据的请求呢?
首先我们回想下我们是如何找到这条记录的,是因为点击了第5页。
所以我们大致能够推断出应该是提交了一个字段名和page相关且值=5的参数,而看一下最下方的Query String Parameters或者最上方的Requset URL,其中有个page_ylypmlcxQuery=5,假如我们再点击第6页第7页,这个参数也会对应的变成6,7,所以基本能够确定就是它了。
又因为是GET,参数已经全部在URL里了,所以我们可以直接把Requset URL复制粘贴到浏览器看一下。

我们在浏览器地址栏里把5改成6,7,8,看到数据也会跟着发生变化。这里除了5还有另一个参数,也不知道是什么玩意,有强迫症的可以去掉试试,变成http://221.215.38.136/grcx/pages/kscx/ylypmlcx.jsp?page_ylypmlcxQuery=5,发现对结果没影响,那么要抓第5页的数据我们只需要把这个地址复制到PQ中就可以了。

继续下一个案例,http://www.drugfuture.com/cndrug/national.aspx?ApprovalDateStart=2016-01-01&ApprovalDateEnd=2016-12-31,同样要求抓第5页。
F12,点击第5页,在Doc里发现一条记录如下图:

我们发现Request Method是POST,并且在URL中已经找不到控制页数的参数了,因为POST提交的参数在Body中。
在Request Headers中比之前多了一个Content-Type,这个参数用来描述Body的内容类型,所以一般POST类型都需要加上这个参数,否则服务器无法识别提交的Body内容。注意response里也有个Content-Type,不要填错了。
在最下方相比GET多了一个Form Data的模块,其中包含__EVENTTARGET,__EVENTARGUMENT,__VIEWSTATE,__VIEWSTATEGENERATOR四个参数,这里就是我们上面一直所说的POST提交的Body内容。我们很容易发现__EVENTARGUMENT的值为Page$5就是控制页数的,继续点击第6页发现参数变成了Page$6,也就验证了我们的猜想。
所以同上一个案例相比,我们只需要把GET换成POST,提交上面的4个参数,再加一个Content-Type表明类型,即可抓到指定的数据。

以上介绍了使用浏览器自带调试工具分别实现GET和POST抓包的方法,但是毕竟案例比较简单,基本上都只有一条记录我们一下子就能找到。但是如果出现的记录很多,我们可以使用Fiddler来快速定位。
Fiddler是一款系统代理服务器软件,下载地址请自行百度。原本是客户端发送request,服务器返回response,而有了代理之后就相当于在客户端和服务器之间夹了个小三,客户端发送request给Fiddler,Fiddler再转发给服务器,反之亦如此。由于所有的网络数据都会经过Fiddler,自然Fiddler能够截获这些数据,实现网络数据的抓包。

安装好Fiddler后首先进行初始设置。
Rules,勾选"Remove all Encodings","Hide Image Requests","Hide CONNECTs",然后Tools-Options-HTTPS全部勾上。

还以上面POST那个案例为例,在浏览器中点击第5页,在Fiddler中按ctrl+F调出查找窗口,然后在想要抓取的页面中随便找一个比较有特征的数据,比如第5页中有一个产品名称叫做"维血康颗粒"。又因为我们要找的是服务器返回的response中的数据,所以我们可以选择Responses Only以缩小查找范围。

这样我们需要的数据所在的请求就会被高亮标记出来。Fiddler界面有很多标签,我们选择"Inspectors",此时界面分为三部分,左边为session框,右上是request框,右下是response框。

所以在Fiddler中我们要做的就是,ctrl+F查找,然后查看response框确认数据是否在范围内,然后在request框里找出请求参数。
request框和response框都提供了多种视图供我们查看,最好是都选择Raw,也就是原始数据。这里只是举了个例子,在实际应用中我们搜索的时候最好搜英文或数字,而不要搜中文,因为可能因为转码的问题导致搜不到。

刚才说到所有的网络数据都会经过Fiddler,所以使用Fiddler不仅可以抓到浏览器中的数据,甚至可以抓到一些应用软件中的数据,比如说抓QQ群成员。
打开QQ群资料-成员,刚打开的时候会看到短暂的"正在加载数据,请稍候"的提示。当加载完成后,Fiddler中多了几条记录,尝试搜索"施阳",高亮定位到其中的一条记录,查看response发现,没错这正是我们要的数据。

一般点击出现"正在加载"的数据多是如此,大家都可以动手找一下QQ群文件。

不知不觉已经5000字下去了,但到现在似乎都和Power Query没多大关系。
抓包是网抓的第一步也是最重要的一步,这步没做好或者找错了那么后面都是徒劳。不管你是使用PQ,还是VBA,还是python,到这一步的方法都是一样的。
至此我们已经知道了浏览器之所以能够获取到数据是因为它到底做了什么,那么下面就开始进入下一步,把刚才浏览器做的事交给Power Query去做。

构建请求:

在M语言中,实现网抓的核心函数是Web.Contents,它能够对指定的URL向服务器发出request并接受返回的response,先看下函数介绍。

Web.Contents(url as text, optional options as nullable record) as binary
第一参数就是文本形式的URL,第二参数是可省略的record,包含上图中那一大坨参数,其中有3个比较常见,其他比较少见就不讲了。
Query:也就是F12中的Query String Parameters,之前也讲过这部分的参数也可以加在URL的?后面,以&相连,比如= Web.Contents("http://www.baidu.com/s?wd=powerquery")= Web.Contents("http://www.baidu.com/s", [Query=[wd="powerquery"]])两者是等价的,但是后者结构更清晰更便于修改和维护,所以在简单的情况下使用前者更方便,在参数比较多的复杂情况下更推荐后者。
Content:如果是GET则不填,一旦填写此参数将改为POST。填写内容就是F12里的Form Data,然后点击view source所看到的一整串文本,同样参数与参数之间是以&连接的。在Fiddler中就是request框中Raw下的最底部的部分。
Headers:也就是F12中看到的Request Headers,其中当请求方式为Post时需要Content-Type,需要登录时要Cookie,部分防盗链网站需要Referer。

服务器返回的数据可能有很多种类型,这个我们先不管,这一步我们的目的就是构建带参数的request,获取目标数据的response,我们先全部统一获取源码,到下一步再讲如何转换。
Text.FromBinary能够将Web.Contents返回的binary解析出HTML源码,而不同网站的编码方式不同,中文网站常见的有GBK和UTF-8两种编码方式,一般在网页头部的meta部分都有声明。

所以如果网页采用GBK编码,就要给Text.FromBinary加上第二参数0,否则会乱码,下面有案例会讲到。
讲完这个,剩下的就是填空题了。
GET:

let
    url="",             //Requset URL中?前面的部分
    headers=[Cookie=""],             //如果不需要登录请删除整行,同时删除下一行中的Headers=headers
    query=[],               //Query String Parameters,即Requset URL中?后面的部分
    web=Text.FromBinary(Web.Contents(url,[Headers=headers,Query=query]))
in
    web

POST:

let
    url="",
    headers=[#"Content-Type"="",Cookie=""],             //Content-Type必填,如不需要登录Cookie可省略
    query=[],
    content="",
    web=Text.FromBinary(Web.Contents(url,[Headers=headers,Query=query,Content=Text.ToBinary(content)]))
in
    web

其中的""和[]就是需要你自己去填的,建议在编辑器软件中编辑好再粘贴到Power Query的高级编辑器中,在此推荐一款好用的编辑器——Sublime Text,轻量方便颜值高。

下面结合案例来做填空题:抓QQ邮箱收件箱。
F12,点击收件箱-下一页,在Doc里出现一条mail_list,观察发现是GET方式,所以用第一个模板。
又因为邮箱肯定是需要登录才能访问的,所以要加上cookie。
把代码编辑好复制到高级编辑器,发现返回的源码有乱码,再看一眼编码方式是GBK,所以加上第二参数0,结果正确,你收件箱里某一页的数据就都出来了。
很简单吧,你可以再尝试抓QQ空间,百度网盘这些,方法都一样。

再来举个特殊情况的案例:http://bond.sse.com.cn/bridge/information/。
F12,点击下一页,这回是在JS里有个commonQuery.do,也不难找到,是GET方式,但是把Request URL复制粘贴到浏览器地址栏却发现打不开,用Power Query也无法抓到数据。

简单来说就是网站做了防盗链处理,需要加上Request Headers里的Referer。
当然特殊情况也不止这一种,如果你很确定数据就在这条请求里,但是就是出不来,思考下什么原因?因为F12中本来有很多参数,我们只填了几个必填的参数,其他都省略了,出不来数据说明我们把某个必要的参数给省略了,那么可以把F12中看到的所有参数全部填上去试下,多半都能返回正确结果。

另外目前许多网站都部署了SSL认证的HTTPS协议,相当于HTTP的加强版,更加安全,比如本文一开始讲到的百度的案例。
但是Power Query目前对HTTPS支持不是太友好,所以如果碰到URL是https开头的请改成http,否则很可能会出错。
一般来说默认提交的GET或POST参数有很多,但很多都是无效或者不相关的参数,去掉也不影响结果,所以像我这种有强迫症的就习惯挨个把参数去掉测试。

本节介绍了如何在Power Query中构建参数并向服务器发出请求,这是最简单的一步,其实就是填空题套模板。
这步完成后,可以大致浏览下返回的源码,看下我们要的数据是否在其中,如果没问题就进行下一步——数据清洗。

数据清洗:

经过上两步,我们已经抓到所需要的数据,但是格式比较乱,我们需要对其清洗成为规范的表格。
服务器返回的数据,有可能是HTML,JSON,XML等格式,举几个例子,请分别复制到浏览器打开比较下区别:
HTML:http://datacenter.mep.gov.cn:8099/ths-report/report!list.action?xmlname=1462261004631
普通的网页结构,最简单的一种情况,HTML源码中包含table标签,使用Web.Page能够直接解析成表格,再深化出table即可。

JSON:http://platform.sina.com.cn/sports_all/client_api?_sport_t_=football&_sport_s_=opta&_sport_a_=teamOrder&app_key=3571367214&type=10&season=2016
纯文本形式的结构化数据,一个字段对应一个值,使用Json.Document解析。但解析出来的不是表格而是record,除了我们要的数据还可能包含状态码、页数等等,所以需要找到数据所在的list,使用Table.FromReocrds还原成表。不会也没关系,到这一步剩下的基本靠纯界面操作就能完成。

XML:http://www.cffex.com.cn/sj/hqsj/rtj/201710/18/index.xml,与JSON类似,都是纯文本形式的结构化数据,但没有JSON常见,使用Xml.Tables解析。

以上都属于结构化数据,就是能够通过函数直接或间接转换为表格,但很多时候我们遇到的可能是一些非结构化数据,比如要抓本站所有文章的标题,它既不是表格,也不是JSON,函数解析不出来,那要怎么搞呢?
常见的有正则,XPath,CSS Selector等方法,但很遗憾PQ一个都不支持。。。所以PQ在处理这些数据的时候就比较痛苦了,只能保持第二步中Text.FromBinary解析出来的源码,然后当作文本来用Text类函数提取。

Web.Contents返回的数据类型为binary,Text.FromBinary把binary解析为text,我们可以直接使用上面三个函数来替换Text.FromBinary的位置解析binary,也可以套在Text.FromBinary的外面来解析text,理论上效果是一样的,但是有些时候却直接解析不出来,必须加一个Text.FromBinary,比如案例篇的练习题。

接下来讲很多人关心的翻页问题,如何批量抓取多个网页然后合并呢?
以第一个静态页的案例为例http://quote.stockstar.com/stock/ranklist_a_3_1_1.html,我们先写出完整的代码:

let
    源 = Web.Page(Web.Contents("http://quote.stockstar.com/stock/ranklist_a_3_1_1.html")){0}[Data]
in
    源

URL结尾的a_3_1_1中最后一个1表示页数,往后翻会依次变成23456..现在要抓1到10页,那么我们只要把最后一个数改成23456..依次抓一遍即可。
但问题是抓不同页数的代码只是改了一个数字,其他部分都是一样的,我们不可能要抓10页就把代码重复10遍,这太麻烦了,所以可以把变化的部分做成变量封装为自定义函数,写成fx = (page)=> Web.Page(Web.Contents("http://quote.stockstar.com/stock/ranklist_a_3_1_1"&Text.From(page)&".html")){0}[Data],然后用List.Transform遍历循环{1..10},分别调用自定义函数fx得到一个包含10张表的列表,最后用Table.Combine将10张表合并为一张表,写成:

let
    fx = (page)=> Web.Page(Web.Contents("http://quote.stockstar.com/stock/ranklist_a_3_1_1"&Text.From(page)&".html")){0}[Data],
    结果 = Table.Combine(List.Transform({1..10},fx))
in
    结果

注意,页数是数字,与URL的文本相连时要用Text.From进行数据类型转换。
同理,如果要批量抓取多日期、多ID之类的,只要更改自定义函数中的变量即可。
而如果我们不是要抓前10页而是所有页,而且事先是不知道一共有多少页的怎么办?如果返回的是JSON,大部分情况下数据中都会包含一个叫做totalpage的字段,所以可以分两步,第一步先随便提交一个页码拿到totalpage,可参考案例篇附件。

但是比如你现在正在使用我介绍的方法批量抓取我的网站数据,如果再多几个你这样的,那我的网站基本上就炸了。
一直如此高频率地访问网站,那得给服务器带来多大的压力。
所以很多网站会采取防爬虫措施,如果访问太频繁就会被封IP。PQ虽然不支持代理IP池之类的,但是延时还是支持的。
如果你要抓的网站会封IP,那么可以在自定义函数外面嵌套Function.InvokeAfter,设置每爬一次停顿个5秒。
比如= Function.InvokeAfter(()=>1+1,#duration(0,0,0,5)),你会发现你的电脑算1+1还没你算的快。

动态交互:

很多时候我们希望能够实现类似网页中的体验,输入指定条件比如开始和结束日期,根据指定条件抓取数据,如下图:

那么也很简单,只需要把需要查询的内容导入PQ,替换自定义函数中的变量即可,可参考案例篇附件。
另外值得一提的是,上面介绍过抓取需要登录的网站要加cookie,而cookie是有生命周期的,有时候你发现昨天抓到的数据今天就报错了,就是因为cookie过期了,你可以打开高级编辑器修改cookie的代码,但是显然太麻烦了。所以也可以把cookie写在单元格中,然后导入PQ,这样就可以实现在不打开PQ的情况下实现本需要修改代码的刷新。

调用API:

API即应用程序编程接口,调用API能够实现很多Power Query本身无法实现的功能,比如根据地址获取经纬度、翻译、分词、正则、机器人自动回复等等功能,可参考《使用PQ调用API》
调用API的原理和网抓是一样的,其实很多网站的数据本身也是使用API调出来的。
一般开发文档都有详细说明,是使用GET还是POST,然后根据说明向服务器提交参数即可,返回的数据一般都是JSON。
部分API有免费限额,就是可以免费调用多少次,超出限额的需要收费,所以常见的比如地图、翻译等API都需要去开放平台注册为开发者,然后把自己的密钥加入到提交的参数中。

编程语言接口:

上面简单介绍了一下API,你可以把API理解成封装在服务器中的自定义函数,只需要向服务器提交函数所需要的参数,就能够返回你想要的结果。
那么服务器中的函数是怎么来的?那肯定是用编程语言来写的,比如PHP,Python等等,流程就是:你通过API提交参数,服务器中的编程语言写的程序来计算,得出结果并返回给你。
所以理论上只要是能够配置服务器环境的编程语言,都可以与PQ形成交互,比如《在Power Query中使用正则表达式》就是用PHP写的。
再比如使用Python的bottle包搭建本地服务器框架,便能够通过访问localhost与Python交互,可参考《M与Python交互》

本文介绍了使用Power Query实现网抓的一些基础知识,整个流程分为抓包——构建请求——清洗数据三个步骤,重点在于第一部分,搞清楚网抓的基本原理,再结合案例《Power Query网抓案例》熟悉代码,基本上能解决大部分简单的网抓需求。

最后再啰嗦下,关键在于抓包,一旦抓到包后面都好做。而在抓包的时候,一定要灵活变通,比如在抓一些电商网站数据时,PC端往往比较难抓,所以可以考虑PC转移动,去抓移动端网站,在Chrome中的F12窗口左上角可以一键切换PC和移动模式。再比如抓QQ好友列表,你直接抓软件是抓不到的,那你就思考除了在软件还可能在哪里有接口?比如QQ空间里@好友的时候,给好友充值QB的时候,都可以轻松获取到好友列表。一旦想通这点,你会发现网抓原来这么简单。

因内容较多,篇幅有限,部分知识点只是简要带过,如有问题欢迎留言。

89 Replies to “Power Query网抓详解”

  1. 前面在群里有两个问题请教了老师,一步步学会了PQ,这个网抓的内容现在我基本能看懂,并且好几个方式也用过,没有老师这么系统的总结!赞不停!

  2. 请教一下老师,怎么解决抓取需要登录的网站内数据,加cookies在header前具体怎么操作的?还有cookies怎么获取啊?求老师解答一下

    1. 非常感谢,尽管我不是很专业也可能用不到这么深但非常感谢老师不厌其烦地给出这么详尽的讲解;
      怎么点赞也不为过!

  3. 关于 Function.InvokeAfter这个函数的用法,以文中的例子的基础上,做个解释:

    let
    fx = (page)=> Web.Page(Web.Contents("http://quote.stockstar.com/stock/ranklist_a_3_1_1"&Text.From(page)&".html")){0}[Data],
    结果 = Table.Combine(List.Transform({1..10},fx))
    in
    结果

    在上面的示例中,定义了一个函数,函数名称为fx ,所以在加延迟查询的时候,应该把Function.InvokeAfter加在fx的前面:

    结果 = Table.Combine(List.Transform({1..10},Function.InvokeAfter(()=>fx, #duration(0,0,0,1))))

    替换上面的 结果=...语句即可
    #duration(0,0,0,1)表示1秒钟,也就是,每查询一页,暂停一秒钟,当然这个数字,可以根据自己的需要替换。

    错误之处,还请施总斧正。

    1. 并不能这么写.这么控制实际上还是一次性把列表发给了 fx. 需要在fx的内部使用Function.InvokeAfter 来控制.

  4. "所以也可以把cookie写在单元格中,然后导入PQ,这样就可以实现在不打开PQ的情况下实现本需要修改代码的刷新。"
    非常感谢!目前看到的最细致的用PQ抓取网站数据的文章,非常难得。我在excel中调用PQ自定义函数时,每次更改参数都要打开PQ界面来更改输入参数。请问,您是如何实现PQ直接导入PQ,并刷新函数的。

  5. 学习了三天,学习你的大作,《Power Query网抓详解》、Power Query网抓案例,书中的案例都理解了,我就遇到json方式,但是就实现不了提取,特别想多页提取,如果你有空把json这种方式实现提取且多页提取搞个详细点的教程,便于学习、理解、使用。

    1. 太长是有多长?excel中一个单元格可容纳3W多个字符,我目前遇到的cookie通常在几千个字符以内,应该不会出现这种情况

      1. 我这里有点困或,我把cookie另作为一个表,在构建请求头时不知该如何导入cookie表的cookie值,我是以添加列编写请求的,困扰好久

          1. #"Cookie"="&表5_2[cookie]&",我刚刚试了下,没有读出cookie表格的数据,可以看看哪里出问题了么

  6. 我做了段VBA:
    Sub aa()

    wz0 = """http://money.163.com/"""

    wz = "源 = Web.Page(Web.contents(" & wz0 & "))"

    wz1 = "let" & Chr(13) & "" & Chr(10) & " " & wz & Chr(13) & "" & Chr(10) & " Data10 = 源{10}[Data]," & Chr(13) & "" & Chr(10) & " 更改的类型 = Table.TransformColumnTypes(Data10,{{""排名"", Int64.Type}, {""名称"", type text}, {""四周涨幅"", Percentage.Type}, {""今涨跌幅"", Percentage.Type}, {"""", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " 更改的类型"
    wz2 = "let" & Chr(13) & "" & Chr(10) & " 源 = Web.Page(Web.Contents(""http://money.163.com/""))," & Chr(13) & "" & Chr(10) & " Data10 = 源{10}[Data]," & Chr(13) & "" & Chr(10) & " 更改的类型 = Table.TransformColumnTypes(Data10,{{""排名"", Int64.Type}, {""名称"", type text}, {""四周涨幅"", Percentage.Type}, {""今涨跌幅"", Percentage.Type}, {"""", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " 更改的类型"
    Sheet2.Range("A1") = wz1
    Sheet2.Range("A2") = wz2

    End Sub
    可以顺利的获取数据,但用VBA删除QueryTables连接不成功,请大神指点:
    使用
    ThisWorkbook QueryTables("表1").Delete
    ThisWorkbook Queries("表1").Delete
    均提示“ 子过程或函数未定义”,不知错在哪里?
    谢谢!

      1. = Json.Document(Text.FromBinary(Web.Contents("http://sysjk.ivdc.org.cn:8081/cx/query_syscqysj/querysyscqyinfo.do?start=0&limit=2101&condList=&isGjcx=0"),0))

  7. request payload 这种请求内容是json结构 要怎么写到代码里呢 也是用Content=吧 关键后面不知道要怎样写才对
    {reportName: "ST_CurrentStockRpt", solutionId: "772"}

    1. Content="{reportName: ""ST_CurrentStockRpt"", solutionId: ""772""}"
      

      试一下,不一定对,最好是把URL发出来看看

  8. 我测试了通过调用post 方法函数来做,但是有个问题,如果其中一个连接获取不到数据,整体就都返回不了结果,提示说似乎整体是一个事务,其中任何一个出错,整个事务都会被取消,得不到任何结果,这个怎么解决?

  9. 拜读了大神的文章 自己试了下 有个问题始终困扰 就是 Content内容
    我用Chrome调试加模拟httprequest插件 可以成功获取到要的内容 但将插件中body部分内容复制到pq中 就无法获取了 始终是第一页的内容 怀疑是body和Content内容问题
    Content=Text.ToBinary(body中的内容) 忘赐教 谢谢!

  10. 现在是不是很多网页出现了反爬虫这道墙,现在网页信息获取不到,请问有什么解决方法呢?
    let
    源 = Web.Page(Web.Contents("https://sou.zhaopin.com/?p=2&pageSize=60&jl=%E5%B9%BF%E5%B7%9E&kt=3")){0}[Data]{0}[Children]
    in

  11. 网页的搜索关键词在获取的URL地址里以URL编码格式存在,获取方式为get
    录入语句query=[ ],报错提示“应为令牌 Literal”,错误字符为URL编码下的“%”字符
    请问是什么原因?

      1. let
        url="https://adm.analyze.3456wan.com/summary/androidmultiday/",

        headers=[Cookie=""],

        query=[startDate=2018-08-14&endDate=2018-11-13&appId=116&settleType=0&publisherId=0&channelAdId=&showItems%5B%5D=4&gamePackages%5B%5D=528&gamePackages%5B%5D=526],

        web=Text.FromBinary(Web.Contents(url,[Headers=headers,Query=query]))

        in
        web

        上面代码也是报错:应为令牌 Literal

          1. 问题一:&gamePackages%5B%5D=528&gamePackages%5B%5D=526,这种相同的query,但是有两个数值,应该怎么填?
            问题二:query=[gamePackages%5B%5D="526"],会报错,提示识别符无效,为何?

          2. 问题一:应该不会有这种情况吧?我是没有见过,那你直接附在URL后面就好了,不要单独写query
            问题二:有特殊字符,要加#,写作query=[#"gamePackages%5B%5D"="526"]

      2. 报错:应为令牌 Literal

        let
        url="https://adm.analyze.3456wan.com/summary/androidmultiday/",

        headers=[Cookie="我隐藏掉了"],

        query=[startDate=2018-08-14&endDate=2018-11-13&appId=116&settleType=0&publisherId=0&channelAdId=&showItems%5B%5D=4&gamePackages%5B%5D=528&gamePackages%5B%5D=526],

        web=Text.FromBinary(Web.Contents(url,[Headers=headers,Query=query]))

        in
        web

  12. 网上到处都找不到这方面的教程,最后还是这里找到了这个教程,今天在家研究了一天。目前出现个问题:1 目标网站需要登陆,但是登陆后找不到cookie。2 查询需要日期范围 是以参数形式写在url的?号后的(这个问题解决的大概思路有了)。整个网页里都找不到cookie这个怎么结局? 还有PQ能不能实现先登陆 获取到cookie,然后再用获取的cookie进行查新这样的步骤?

      1. 是公司内部的一个类crm的网站,问过服务商了.说是用一种类授权的机制.会返回一个tickets的授权码.每次和服务器交换数据都会检查登陆后服务给的这个tickets 授权码.现在的问题就算是对方配合我,也很难解决这个随时变动的tickets的问题.把这个东西变成一个函数储存起来,但是这个逻辑又变得很负责了.

      2. 最新进展.登录搞定.获取access_token秘钥搞定,query 参数也写进去了.检查了所有参数服务器就是提示报错.抓狂中.

  13. 网址信息:https://t.17track.net/zh-cn#nums=CP368885246CN

    Request URL:https://t.17track.net/restapi/track

    请求表头的内容:
    :authority:t.17track.net
    :method:POST
    :path:/restapi/track
    :scheme:https
    accept:application/json, text/javascript, */*; q=0.01
    accept-encoding:gzip, deflate, br
    accept-language:zh-CN,zh;q=0.9
    content-length:44
    content-type:application/x-www-form-urlencoded; charset=UTF-8
    cookie:_ga=GA1.2.1508408559.1537459870; __gads=ID=5affd9ec23cb0c72:T=1537459874:S=ALNI_MZ_6LZfB-cBBz7DSgRyyzSJ1a69xw; v5_TranslateLang=zh-CHS; _yq_bid=G-91D584D96F89CCBF; v5_HisExpress=18031; _gid=GA1.2.786561110.1542977361; _gat=1; Last-Event-ID=657572742f3238332f64646361656430343736312f6f676f6c2d746c75616665642d71792073782d6e6564646968206f676f6c2d646e6172622d72616276616e15211f0c452b1840ac29
    origin:https://t.17track.net
    referer:https://t.17track.net/zh-cn
    user-agent:Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.26 Safari/537.36 Core/1.63.6788.400 QQBrowser/10.3.2767.400
    x-requested-with:XMLHttpRequest

    From Data:
    {"guid":"","data":[{"num":"CP368885246CN"}]}

    根据这些数据我写得PQ代码返回不了,不知道错在哪里?能帮我看下吗?不知道是不是因为query的参数没写的原因,不知道怎么写。

    let
    //Post请求函数写法
    //填写网址
    url="t.17track.net/restapi/track",
    //填写cookie
    cookie="_ga=GA1.2.1508408559.1537459870; __gads=ID=5affd9ec23cb0c72:T=1537459874:S=ALNI_MZ_6LZfB-cBBz7DSgRyyzSJ1a69xw; v5_TranslateLang=zh-CHS; _yq_bid=G-91D584D96F89CCBF; v5_HisExpress=18031; _gid=GA1.2.786561110.1542977361; _gat=1; Last-Event-ID=657572742f3238332f64646361656430343736312f6f676f6c2d746c75616665642d71792073782d6e6564646968206f676f6c2d646e6172622d72616276616e15211f0c452b1840ac29",
    //填写表头Content-Type内容
    content_type="application/x-www-form-urlencoded; charset=UTF-8",
    origin="https://t.17track.net",
    referer="https://t.17track.net/zh-cn",

    headers=[#"Content-Type"=content_type,Cookie=cookie,Origin=origin,Referer=referer], //Content-Type必填,如不需要登录Cookie可省略

    //这部分是通过网页查询地址变动的,如果没有就为空
    query=[],

    //content内容为From Date里面的view source的字符串
    content="{""guid"":"""",""data"":[{""num"":""CP368885250CN""}]}",

    web=Text.FromBinary(Web.Contents(url,[Headers=headers,Query=query,Content=Text.ToBinary(content)]))
    in
    web

    1. url是https,你填的不加就默认是http了,把url改成https://t.17track.net/restapi/track就可以了,其他的origin和referer也不需要。
      但是试了下,同一个cookie,换一个单号就又出不来了,查其他单号还得换cookie,得研究下cookie里是不是和单号有什么关系。

  14. 请教:我根据案例抓取QQ邮箱收件箱,相关参数改了一下,提示:Expression.synataxError:应为令牌Literal。
    是我的cookie填得不对吗。cookie我填的是,request headers-cookie冒号后面一长串字符

  15. let
    url = List.Transform({null}&{1..Number.FromText(List.LastN(Text.Split(Web.Page(Web.Contents("http://www.cazy.org/GH1_all.html#pagination_PRINC"))[Data]{1}[Column1]{0},"|"),2){0})-1}, each if _=null then "http://www.cazy.org/GH1_all.html#pagination_PRINC" else "http://www.cazy.org/GH1_all.html?debut_PRINC="&Number.ToText(_*1000)&"#pagination_PRINC"),
    source = Table.Combine(List.Transform(List.Range(url,0), each Web.Page(Web.Contents(_))[Data]{1}))
    in
    source

    请教老师,为什么在source里面加了List.Range才能抓取做所有的网页,不加List.Range就报错

  16. headers=[#"Content-Type"="",Cookie=""], //Content-Type必填,如不需要登录Cookie可省略
    请问"Content-Type",前面 # 符号作用是?

    1. #表示允许命名中存在特殊字符,但同时最外面要加上引号。
      record中字段名原本不需要加引号的,但试想一下这种情况,比如说字段名中有个等号,如[Content=Type="aaa"],就分不清哪个符号是字段名里的,哪个是真正的等号,是不是就引起歧义了?
      同样,"-"也是特殊字符,所以要加#和引号。

  17. let
    get_data=(page)=>
    let
    url="https://cn8.800app.com/uploadfile/staticresource/291879/346017/ESL-Workload.aspx",
    headers=[#"Content-type"="application/x-www-form-urlencoded",Cookie="ASP.NET_SessionId=ydycfvwywbm05uz3bvcfso4q; .Crm_gooling=B7F67611D871DB11F75A202A777587E1B3F68A256E71F6EEDE6ED00E99DA3B7B61D43E4E805874C753E5F488D3E94A322E1DD0A5F153F5F5C3DEC1BA62C4D8255DA34B800D35F3977D9313E918927009FF503335AAA20CEFAD02415F571553F6538889A24C74847CF8B9EE46A708F735E081244A; Search_sele=crm_zdytable_291879_29830; spanshow1=1; %2bq%2b7L5Hl%2ft4%3d=iHWLw56vmnY%3d; 346017_appid=60285; %2fW8iOeB9lM2WL%2bSQ7gsuXg%3d%3d=VuMs%2fPGYS74%3d; wWM3Eeo%2f7AHFYBNpMmPU2w%3d%3d=; allwordis=; zxotUg4hozo%3d=%2fszQY17gCl2uq%2bIRf4E%2fvQ%3d%3d; Hm_lpvt_ce74b141bbb6d057b757fffd582cad93=1551769355; Hm_lvt_ce74b141bbb6d057b757fffd582cad93=1549933225,1550457463,1550729776"],
    content="__EVENTTARGET=ddlDate&__VIEWSTATE=/wEPDwULLTE2OTU5NzM1ODIPZBYCAgEPZBYCAgEPEGRkFgECAWRkVzi4i9kgB5b78MCDjDfma9LLRPc=&__VIEWSTATEGENERATOR=EC61ED3F"

    query=[],
    web_data=Web.Contents(url,[Query=query,Headers=headers,Content=Text.ToBinary(content)]),
    table=Web.Page(Text.FromBinary(web_data)){0}[Data]
    in
    table,
    result=Table.RemoveColumns(Table.Combine(List.Transform({1..4},get_data)),{""})

    提示:应为令牌Comma
    点击后定位到query=[], 这句,但不知道这个该怎么填

  18. let
    get_data=(page)=>
    let
    url="https://cn8.800app.com/uploadfile/staticresource/291879/346017/ESL-Workload.aspx",
    headers=[#"Content-type"="application/x-www-form-urlencoded",Cookie="已填"],
    content="__EVENTTARGET=ddlDate&__VIEWSTATE=/wEPDwULLTE2OTU5NzM1ODIPZBYCAgEPZBYCAgEPEGRkFgECAWRkVzi4i9kgB5b78MCDjDfma9LLRPc=&__VIEWSTATEGENERATOR=EC61ED3F"

    query=[],
    web_data=Web.Contents(url,[Query=query,Headers=headers,Content=Text.ToBinary(content)]),
    table=Web.Page(Text.FromBinary(web_data)){0}[Data]
    in
    table,
    result=Table.RemoveColumns(Table.Combine(List.Transform({1..4},get_data)),{""})

    提示:应为令牌Comma
    点击后定位到query=[], 这句,但不知道这个该怎么填

  19. Content-Disposition:
    attachment;filename=%e5%95%86%e4%b8%9a%e9%99%a9%e6%9c%aa%e5%86%b3%e8%b5%94%e6%a1%88%e6%b8%85%e5%8d%95%ef%bc%88%e5%89%94%e9%99%a4%e5%86%9c%e9%99%a9%ef%bc%89_5321_%e6%a0%b8%e6%9c%aa%e7%bb%93.xls
    Content-Length: 71193
    老师好,公司内部网站,没有单独content,有Content-Disposition和length,如果不输入这两项,抓取的只有某一页的10几条数据,而且抓取地址上也反应不了页数,网页上可以导出数据,但分了项目(%e5%95%86%e4%b8%9a代表一类,_5321_代表一类,%e6%a0%b8%e6%9c%aa%e7%bb%93代表一类),如何一次性抓取全部数据

  20. let
    源=(page)=>Json.Document(Web.Contents("https://cain-api.gameyw.netease.com/worldhero-web/app_api/mystic/"& Text.From(page) &"/50?serverType=0&season=201&modeType=0&mysticType=1"))[info],
    取40页=List.Combine(List.Transform(List.Transform({0..40},each _*50),源)),

    文章中提到多页合并的做法,请教一下如何实现多变量下的循环查询和合并
    如上,网站每页显示50条记录 已实现一次性获取40页数据,但是mysticType参数也需从1到10各取一次,
    即mysticType=1时取一次1-40页,mysticType=2时再取1-40页

  21. 大佬,如果url中出现了#字符的话,好像web.contents没办法抓取跳转后的正确页面内容额,这个可以怎么处理?

  22. 老师您好,报错是“应为令牌Literal”,请问如何解决?谢谢!

    let
    url="http://www.miit.gov.cn/asopCmsSearch/searchIndex.jsp", //Requset URL中?前面的部分
    query=[params=%257B%2522goPage%2522%253A4%252C%2522orderBy%2522%253A%255B%257B%2522orderBy%2522%253A%2522pl%2522%252C%2522reverse%2522%253Afalse%257D%255D%252C%2522pageSize%2522%253A10%252C%2522queryParam%2522%253A%255B%257B%2522shortName%2522%253A%2522clzl%2522%252C%2522value%2522%253A%2522clzl%253EcycM1l%2522%257D%255D%257D&callback=jsonp1591193818767&_=1591194483266], //Query String Parameters,即Requset URL中?后面的部分
    web=Text.FromBinary(Web.Contents(url,[Query=query]))
    in
    web

    1. 您好老师,补充一下说明,这个网址里面goPage%2522%253A4的A后面的数字是代码,其他都是不变的,callback后面是改变的,但是不管它也不影响打开URL。。。当我尝试您上面的方法在params前面加上#,然后双引号全部括起来,提示语法没有错误,但是提交之后显示另外一个报错:Expression.Error: 有未知标识符。是否将 [field] 速记方式用于 "each" 表达式外

  23. Post 请求里面有数组怎么 写 Contents

    比如:
    channel: [channel1,channel2,channel3]
    fromDate: "2020-06-11"
    page: 1
    pageSize: 30
    timeUnit: "day"
    toDate: "2020-06-18"
    type: "newUser"
    version: []
    view: "retention"

  24. 你好施老师!我在抓去http://www.iwencai.com/stockpick/search?w=%E6%B2%AA%E6%B7%B1A%E8%82%A1%EF%BC%8C%E5%8A%A8%E6%80%81%E5%B8%82%E7%9B%88%E7%8E%87%EF%BC%8C%E6%80%BB%E5%B8%82%E5%80%BC这个同花顺爱问财中的内容时,发现需要登录输入帐号、密码和验证码,否则无法察看下一页内容,请问有没有什么办法能够实现自动登录?
    我手动登录后,按照你介绍的方法,用浏览器自带的网抓工具分析后发现无论点击哪一页,上面的网址均不发生变化,点击第三页后继续查找发现,在XHR下点击“cache?token=cfa994……”后,右侧窗口中的Request URL:http://www.iwencai.com/stockpick/cache?token=ea1ffca7225f7a27b3e4a33444d539c1&p=3&perpage=10&showType=[%22%22,%22%22,%22onTable%22,%22onTable%22,%22onTable%22,%22onTable%22,%22onTable%22,%22onTable%22,%22onTable%22,%22onTable%22,%22onTable%22,%22onTable%22,%22onTable%22,%22onTable%22,%22onTable%22]中的参数&p=3和页码相关,但是把这个链接复制到浏览器里,浏览器显示的不是正常浏览格式,全是文本格式,但其中含有表格中的数据,却抓取不到,请问怎么样能够解决这类问题?

  25. 请教:Post抓网易邮箱的数据,
    web=Text.FromBinary(Web.Contents(url,[Headers=headers,Query=query,Content=Text.ToBinary(content)]))
    这一步导出的xml的文本数据,试了文章中说的Web.Page Json.Document Xml.Tables 等函数,都解析不出有效的数据。试了下把web这一步的数据线上载到excel单元格中,再加载一次表格,导入PQ,采用Xml分析,可以找到目标数据。想问一下,有什么函数可以做一下衔接,把文本数据直接导入到一个table中吗,省去中间的步骤,谢谢!

  26. 请教:我之前用office 2019一直可以抓取http://fund.eastmoney.com/dwjz.html#os_0;isall_1;ft_|;pt_1的数据,升级到365后,不能用了,报错“意外错误: 未能加载文件或程序集“Microsoft.mshtml, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”或它的某一个依赖项。系统找不到指定的文件。”重新做也不行,请问是程序安装有什么问题嘛?还是应该如何解决呢?谢谢!

  27. 老师,我抓取网页时出现“仅当匿名连接时才支持带Content选项的Wep.contents”提示该怎么处理

  28. 施阳老师好 看了您的PQ网抓教程 感觉受益很多,现在遇到一个问题,想向您请教下:集思录上的数据改版后现在是限制游客登录只能查看前30条数据 登录之后才能查看所有数据 之前我是可以正常用PQ抓取数据的 现在即使我把登录的cookie加进去 也发现还是只能抓取前30条记录 想请您有空了帮忙给看下 谢谢了

    let
    url="https://www.jisilu.cn/data/cbnew/cb_list/?___jsl=LST___t=1627788961010 ",
    headers=[#"Content-Type"="application/x-www-form-urlencoded",Cookie="_ga=GA1.2.690178055.1594797259; Hm_lvt_164fe01b1433a19b507595a43bf58262=1624424906,1626445525; kbzw__Session=ljfkjed9skgp5u4fokoh3d16r2
    "], //Content-Type必填,如不需要登录Cookie可省略
    query=[],
    content="fprice=&tprice=&curr_iss_amt=&volume=&svolume=&premium_rt=&ytm_rt=&rating_cd=&is_search=N&market_cd%5B%5D=shmb&market_cd%5B%5D=shkc&market_cd%5B%5D=szmb&market_cd%5B%5D=szcy&btype=&listed=Y&qflag=N&sw_cd=&bond_ids=&rp=50&page=1",
    Source=Json.Document(Web.Contents(url,[Headers=headers,Query=query,Content=Text.ToBinary(content)])),
    rows = Table.Combine(Table.FromList(Source[rows],each {Table.FromRecords({Record.FieldValues(_){1}})},{"t"})[t])
    in
    rows

  29. 各位老师好,请问如何使用Excel中的PQ进行网抓数据,网抓的内容是知乎网上搜索关键词之后的文章列表信息
    网址如下:
    https://www.zhihu.com/search?type=content&q=Excel

    真实网址是:https://www.zhihu.com/api/v4/search_v3?t=general&q=Excel&correction=1&offset=0&limit=20&filter_fields=&lc_idx=0&show_all_topics=0&search_source=Normal
    我自己尝试写了PQ代码,headers里面Cookie和Referer都加了,但是还是解析不出来,提示禁止对该资源的访问该怎么办?

  30. 如果post请求的Content-Type: "application/json; charset=utf-8"
    并且请求参数是嵌套字典,
    如何转换为pq里Content

  31. 抓取下来的是一段HTML网页结构代码,怎么从一整篇代码中提取想要的内容呢,就像是python那样的,求教求教

回复 文乐车 取消回复

您的电子邮箱地址不会被公开。 必填项已用*标注