Power Query网抓案例

本篇为案例篇,详细步骤见《Power Query网抓详解》

1、翻页URL会变化,直接get方式提交

URL:http://quote.stockstar.com/stock/ranklist_a_3_1_1.html
此为沪深A股数据,需要抓取1-20页的所有数据。点击下一页后观察URL发现,html前面最后一个数字即为页数,那么只需要自定义函数,将其做成变量然后调用即可。另外发现每一页的最后一行都是多余的,可以用Table.RemoveLastN删掉。

let
    get_data =(x)=>Table.RemoveLastN(Web.Page(Web.Contents("http://quote.stockstar.com/stock/ranklist_a_3_1_"&Text.From(x)&".html")){0}[Data],1),
    result = Table.Combine(List.Transform({1..20},get_data))
in
    result

 

2、翻页URL不会变化,F12找出真实地址

URL:http://221.215.38.136/grcx/kscx/list.action?kscxVo.jsp=ylypmlcx.jsp
要抓取1-20页数据,但发现翻页URL不会变,无法根据URL控制页数。浏览器按F12发现,网页以get方式提交,图中参数4即为页数,表格的真实URL为http://221.215.38.136/grcx/pages/kscx/ylypmlcx.jsp?page_ylypmlcxQuery=4,于是方法同上。

let
    source = Table.Combine(List.Transform({1..20},each Web.Page(Web.Contents("http://221.215.38.136/grcx/pages/kscx/ylypmlcx.jsp?page_ylypmlcxQuery="&Text.From(_))){2}[Data])),
    result = Table.SelectRows(source, each not Text.StartsWith([药品编码], "当前"))
in
    result

 

3、post方式提交

URL:http://www.drugfuture.com/cndrug/national.aspx?ApprovalDateStart=2016-01-01&ApprovalDateEnd=2016-12-31
和之前不同的是,该网页是以post方式提交,如果只是在URL后加参数无法得到正确的页数,于是要用到Web.Contents中的Content提交post参数,另外headers中的Content-Type是必须的,所以也要加进来。因为有个"-"为特殊符号,所以要加个#""。

需要注意的是,提交的参数需要是encode后的,__VIEWSTATE参数太长了,我没放进来,自己改一下。

let
    get_data = (page)=>
    let
        url="http://www.drugfuture.com/cndrug/national.aspx",
        headers=[#"Content-Type"="application/x-www-form-urlencoded"],
        content="__EVENTTARGET=GridViewNational&__EVENTARGUMENT=Page%24"&Text.From(page)&"&__VIEWSTATE=太长了自己改吧",
        query=[ApprovalDateStart="2016-01-01",ApprovalDateEnd="2016-12-31"],
        web_data=Web.Contents(url,[Query=query,Headers=headers,Content=Text.ToBinary(content)]),
        data=Table.RemoveLastN(Web.Page(Text.FromBinary(web_data))[Data]{0},1)
    in
        data,
    result = Table.Combine(List.Transform({1..78},get_data))
in
    result

 

4、需要登录,加cookies

URL:http://www.51mis.com/biaozhun/index.php?module=Accounts&action=index&parenttab=Parent%20Accounts
这是一个CRM的试用账户,现要抓出客户分类中的vip客户下的1-4页的所有信息。这里面有三个问题要解决:①需要登陆 ②点击客户分类下的任何子分类URL都不会变,如何定位到我们想要的vip客户 ③翻页问题
我们首先打开这个URL验证下手机号登陆,然后打开一个新窗口,再次打开这个URL,发现看到的已经和刚才不一样了,这是因为刚才登陆后服务器已经把所有的身份识别信息记录在cookies中,当再次访问的时候浏览器检查到本地有cookies,于是向服务器提交了一个headers中带有cookies的请求,验证通过。所以我们只需要复制cookies添加到headers中即可,问题①解决。
然后尝试点击客户分类下的不同子分类,对比发现有一个叫viewname的参数在变化,而vip客户对应的参数为179,问题②解决。
页码对应的则是start参数,上面介绍过,问题③解决。

let 
    get_data=(page)=>
    let
        url="http://www.51mis.com/biaozhun/index.php",
        headers=[#"Content-type"="application/x-www-form-urlencoded",Cookie="ck_login_id_lingdang=201; ck_login_theme_lingdang=softed; ck_login_language_lingdang=zh_cn; IESESSION=alive; _qddamta_4008885110=3-0; pgv_pvi=1109719040; pgv_si=s9133628416; tencentSig=2914921472; Hm_lvt_15823373277d5586cce1d8fa22740e98=1498477295,1498478011; Hm_lpvt_15823373277d5586cce1d8fa22740e98=1498478011; LXB_REFER=www.baidu.com; _qddaz=QD.evgckn.ew8amb.j4e2ox3t; PHPSESSID=j0m2ou15d8hcumonfcul46kj64; _qdda=3-1.3bt43b; _qddab=3-i0t2ae.j4e2ox3v"],
        content="viewname=179&start="&Text.From(page),
        query=[action="index",module="Accounts"],
        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)),{""})
in
    result

 

5、返回json:

URL:http://sports.sina.com.cn/g/ucl/table.html
要抓取2011-2016所有欧冠积分榜数据。通过F12不难找到真实地址,去掉无关参数后为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,那么就需要使用Json.Document来解析,其中season参数控制赛季,自定义函数后构建{2011..2016}的list调用即可:

let
    get_data=(x)=>Table.FromRecords(Json.Document(Web.Contents("http://platform.sina.com.cn/sports_all/client_api?_sport_t_=football&_sport_s_=opta&_sport_a_=teamOrder&app_key=3571367214&type=10&season="&Text.From(x)))[result][data]),
    result = Table.Combine(List.Transform({2011..2016},get_data))
in
    result

 

6、非结构化数据:

以上案例都有一个共同点:都为结构化数据,可以直接通过解析表或json等方式从而获取数据,但有些网页中的数据是非结构化的。
例如:爬取本站页面https://pqfans.com/catalog中所有的锚链接。

对于这种非结构化的网页,只能通过Text.FromBinary解析出源码,然后当文本处理。如果网页的编码为GBK或GB2312,Text.FromBinary的第二参数要加个0,否则会出现乱码,如果是UTF-8则不用第二参数。

let
    source = Text.FromBinary(Web.Contents("https://pqfans.com/catalog")),
    result = List.Transform({0..List.Count(Text.PositionOf(源,"<a href=""https://pqfans.com/",2))-1},each "https://pqfans.com/"&Text.BetweenDelimiters(源,"<a href=""https://pqfans.com/","""",_))
in
    result

 

练习

URL:http://datacenter.mep.gov.cn/index!MenuAction.action?name=402880fb24e695b60124e6973db30011
请用以上介绍的方法,抓出2017/6/20-2017/6/23全部49页的全国城市空气质量日报
 

补充

对于动态参数,可以把变量写在单元格中,然后将单元格导入PQ,效果如下图:

修改单元格中的值,刷新即可得到不同日期、不同类型等数据,体验类似于网页中的查询。
可参考附件,数据来源于下方评论。

附件

62 Replies to “Power Query网抓案例”

  1. 练习题中的URL通过F12查看是GET的方式,但是request URL和地址栏的URL一模一样,这要怎么操作呢?

  2. 博主您好,想请教一下,我想登陆公司oa获取报表,但是公司OA登陆是使用multipart/form-data 这种方式提交表单的,这种有办法登陆吗?用cookie的话会过期的,一天后就登录不了了

    1. PQ处理这种动态参数暂时没有什么太好的办法,毕竟不是专业的爬虫工具。可以把cookie复制放到excel单元格里然后导入PQ,这样每天多一个复制cookies的操作,稍微麻烦了一点但也能达到目的。

    1. 参考案例3+案例5,把post参数改改就行了,返回的是json。你要抓具体哪个栏目下的数据?条件说清楚

          1. 见正文底部附件。我只做了一个公司公告信息,另一个挂牌审查基本一样,自己改改参数吧。

        1. 发现案例1可以直接找到真实的访问地址(去除无关参数、按股票代码升序排列后为http://quote.stockstar.com/webhandler/rank.ashx?type=a&sortfield=1&direction=0&pageid=1),然后用案例2中的方法改变pageid来取数。这样有个好处:
          案例1中的股票排列顺序默认为按涨跌幅大小排序,在工作日开市期间爬取的话,每页的股票会根据涨跌幅大小发生位置的变化,一页页去读取的话由于时间差,可能存在重复读取或者漏读的情况。改进后的方法可以避免该问题。

          施总,对么?

    1. 通过F12找到Results.aspx这条记录,右侧查看response,发现数据确实在这条请求里,但是PQ里就是出不来?
      那一定是少了某个必要参数,就可以把Request Headers里的参数多填几个试一试,发现这个网址里需要加User-Agent。
      参数太长了我就不写了,你把第二三行引号里的参数填一下就好了。

      let
          url="http://triathlon.basts.com.cn/Results.aspx",
          header=[#"Content-Type"="",#"User-Agent"=""],
          content="",
          data=Web.Page(Text.FromBinary(Web.Contents(url,[Headers=header,Content=Text.ToBinary(content)]))){0}[Data]
      in
          data
      
    1. 我把前20页的URL列出来对比了一下,规律还是能找到一点的,至于怎么破解就不知道了。。。
      q1

  3. 博主,请教,http://mobile.umeng.com/apps
    需要登录,执行的时候报错,请问这是什么情况呢?

    报错如下:
    Expression.Error: 仅当匿名连接时才支持“Cookie”标头。

    1. 我没有用过比特币交易系统。。。一般api都会有详细说明的,按照说明填写api地址和参数就行了

  4. 那么问题来了:怎么快速的获取cookies?cookies也是网页返回的吧?能不能也用pq获取?

  5. 施总,浏览器使用这个https://glxy.mot.gov.cn/BMWebSite/company/getCompanyAchieveList.do?companyId=932d747e001a4cdea36384ea8bfc5867&type=11链接可以返回json数据,用power query 返回是处理过的json,如何才能在power query 得到浏览器的json?一直跟随施总的博客成长,奈何遇到难题还是解不开?

      1. 施老师,从fiddler中看到在浏览器点击文中链接返回了内容(不是json,和power query一样),浏览器启动了一个java公式链接,然后在文中的链接加上一串规律的字符后返加json(用这个修改的连接在浏览器中无返回json),而power query 在用web.contents(链接)图形化操作web视图中看到json,点转换数据后提示json结尾有多余字符。用text.frombinary也是返加一串字符html(无规律)。请老师指导。

          1. 这个是Json.Document(Web.Contents("https://glxy.mot.gov.cn/company/getCompanyAchieveList.do?companyId=932d747e001a4cdea36384ea8bfc5867&type=11"))时的返回错误
            DataFormat.Error: We found extra characters at the end of JSON input.
            Details:
            Value=
            Position=0
            这个是Json.Document(Text.FromBinary(Web.Contents("https://glxy.mot.gov.cn/company/getCompanyAchieveList.do?companyId=932d747e001a4cdea36384ea8bfc5867&type=11"),65001))返回错误
            DataFormat.Error: We found extra characters at the end of JSON input.
            Details:
            Value=
            Position=0
            查看Text.FromBinary返回内容,根本不是差一点是json文件,整个是json的meta content是经过加密处理,然后后台经过解密后才能在浏览器返回json(有时浏览器有跳转网页,如果加载过,不跳转),老师,难度好大啊!

  6. 报错信息:
    DataSource.Error: 仅当匿名连接时才支持带 Content 选项的 Web.Contents。
    详细信息:
    DataSourceKind=Web
    DataSourcePath=http://10.30.201.15/zh-hans/admin/reports/rptrepair

    老师,你好!提示这个如何解决?

    编辑器代码:
    let
    get_data=(page)=>
    let
    url="http://10.30.201.15/zh-hans/admin/reports/rptrepair/",
    headers=[#"Content-Type"="application/x-www-form-urlencoded",Cookie="sessionid=qmhp3fl1h930gftfiow02xkhkebylga8; csrftoken=MD5bV5gTuSCUs0mxZO32kdhJNX558OpwHv4Y8tEl581cA401VTNrkDn3xqbbSufH; _t=1565162791.926616"],
    content="worker_depart=20"&"repaired: False",
    query=[ApprovalDateStart="2017-01-01",ApprovalDateEnd="2019-12-31"],
    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..5},get_data)),{""})
    in
    result

  7. 跪求大神帮忙看下使用power query 如何获取下面这个网页上的表格的所有信息:
    网址是:https://www.jisilu.cn/data/cbnew/#cb,要获取截图中表格中所有的信息,非常感谢

  8. 公司内部ERP系统有几千页的数据,想全部导出来,可是一次导5页 10页感觉速度还好,到100 500页就很慢了,是我的代码有问题吗,想请教一下应该如何优化呢
    let
    get_data=(page)=>
    let
    url="https://tongji.com/customReport/getCustomPageByEnv/1523", //Requset URL中?前面的部分
    headers=[#"Cookie"="vistorId=077c65f1-ceb2-45d3-aaae-61ed64f528a6; SESSION=d8e2b18d-366b-4eef-9695-92217f4632c2; sessionVistorId=8b9823a6-e4dd-4615-a049-598d5223523d; hytkh004419=b8c44d8f95f2456ba90d15a761a47b8f"], //如果不需要登录请删除整行,同时删除下一行中的Headers=headers
    query=[id="1523",numPerPage="100",currentPage=Text.From(page)], //Query String Parameters,即Requset URL中?后面的部分
    web=Json.Document(Web.Contents(url,[Headers=headers,Query=query]))
    //totalPages = Record.ToTable(web[detail]){3}[Value], //totalPages 总页数。
    //webtable= Table.FromList(web[detail][resultList], Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
    web,
    合同列表=List.Transform({1..500},get_data)
    in
    合同列表

  9. 你好博主,是这样的,我现在要抓取公司的crm系统数据,要先登录账号,再点击对应报表,然后会跳到另一个标签。应该怎么实现啊?实在抱歉,因为我M语言基础可能没有。只会套用。。。希望解答详细一点。

    1. 登录就加个cookie,点击对应报表在F12里应该都能跟踪到。详细教程都在这里了https://pqfans.com/2230.html。再详细我看不到网页具体情况也回答不了。

  10. 博主,请教。
    1,抓取政府网站公开信息是否违法?
    2,能否通过power query实现抓取目录下的详细信息,使其与目录一一对应。例如,国家药品监督管理局网页下的otc中药说明书范本-柴黄软胶囊说明书-功能主治。

  11. 大佬,在输入这样query=[startDate=2020-04-18,endDate=2020-04-20,offset=0,pageSize=15,effect=15]的参数后,Web.Contents显示报错,无法将值1998转换为Text,这是什么原因啊

    1. 值要么是数字要么是文本,2020-04-18是文本所以要双引号,1998哪来的?2020减4减18等于1998

  12. 请问施老师:http://www.iwencai.com/stockpick/search?typed=1&preParams=&ts=1&f=1&qs=result_rewrite&selfsectsn=&querytype=stock&searchfilter=&tid=stockpick&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这个网页,我用F12打开调试工具,却怎么样也找不到它页码在哪里?请问应该如何解决?

    1. 这个网页把数据写死了,直接复制链接从Web导入就行了,不用写代码,至少登陆前是这样。
      想尝试翻页但要登录,就没试。

    1. 可以,但一般这种工具都会有API接口,参考https://open.wps.cn/docs/cloud/cloudfile-ability/obtain/api-list

  13. 施阳老师好,目前我通过PowerQuery来读取Cookie,当Cookie失效后更换新的就可以刷新,但是需要人员频繁更新,又没有什么办法可以将Cookie写在excel表格外端,再通过内部进行引用?

  14. 结果搜问题最终还是搜到这儿来,5年前就已经写得清晰明了。那么网络搜索的其它结果,是大家都把好东西藏起来了呢?还是那些答案的答者自己也不太明白?

回复 施阳 取消回复

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