Power Query网抓案例

施阳 / 2017年6月24日2018年5月24日 / 困难

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，效果如下图：

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

附件

评论提问解答 (63 kB)

打赏赞(21)

62 Replies to “Power Query网抓案例”

散落于云海说道：

2017年6月26日上午10:04

牛逼.

回复
我说道：

2017年7月11日下午10:56

真牛逼

回复
姓名说道：

2017年7月12日上午11:06

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

回复
1. 施阳说道：
  
  2017年7月12日上午11:21
  
  是get没错，但是真实地址和地址栏的url应该是不一样的，说明你找的不对。
  
  回复
王泽亮说道：

2017年7月25日上午11:25

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

回复
1. 施阳说道：
  
  2017年7月26日下午4:14
  
  PQ处理这种动态参数暂时没有什么太好的办法，毕竟不是专业的爬虫工具。可以把cookie复制放到excel单元格里然后导入PQ，这样每天多一个复制cookies的操作，稍微麻烦了一点但也能达到目的。
  
  回复
郑先生说道：

2017年9月7日上午10:14

http://www.neeq.com.cn/disclosure/announcement.html 求问这里的数据怎么抓出

回复
1. 施阳说道：
  
  2017年9月7日上午10:30
  
  参考案例3+案例5，把post参数改改就行了，返回的是json。你要抓具体哪个栏目下的数据？条件说清楚
  
  回复
  1. 郑先生说道：
    
    2017年9月25日下午7:15
    
    信息披露下面的第一个公司公告信息。请给源代码，谢谢
    
    回复
    1. 郑先生说道：
      
      2017年9月25日下午7:27
      
      还有个挂牌审查里面的数据，谢谢。
      
      回复
      1. 施阳说道：
        
        2017年9月26日下午12:21
        
        见正文底部附件。我只做了一个公司公告信息，另一个挂牌审查基本一样，自己改改参数吧。
    2. 凌晨说道：
      
      2018年2月26日下午6:19
      
      发现案例1可以直接找到真实的访问地址（去除无关参数、按股票代码升序排列后为http://quote.stockstar.com/webhandler/rank.ashx?type=a&sortfield=1&direction=0&pageid=1），然后用案例2中的方法改变pageid来取数。这样有个好处：
      案例1中的股票排列顺序默认为按涨跌幅大小排序，在工作日开市期间爬取的话，每页的股票会根据涨跌幅大小发生位置的变化，一页页去读取的话由于时间差，可能存在重复读取或者漏读的情况。改进后的方法可以避免该问题。
      
      施总，对么？
      
      回复
Mr.F说道：

2017年10月24日下午3:20

练习题试了一天，都展不开想要的table，怎么操作好？

回复
曽明说道：

2017年11月25日下午12:39

http://triathlon.basts.com.cn/Results.aspx 这个网站的查询也是以post方式提交查询数据，但参考第3个样例却没法取得相关数据（只抓取具体某一场比赛的成绩，
类型：业余
比赛项目：男子全程分龄组
分龄组别：男子全程18-29岁组
，）请指导

回复
1. 施阳说道：
  
  2017年11月27日下午3:27
  通过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
```
  回复
rpz说道：

2017年12月13日上午10:46

请教：
http://218.94.1.175/Sky.WebHall/sftweb/xingquan_topublic_list_xk_page.aspx
这个页面的页码都加密的，不可能找到规律怎么办。
而且通知还需要抓取二级页面中的“行政许可决定文书号”。
谢谢。

回复
1. 施阳说道：
  
  2017年12月13日上午11:39
  
  我把前20页的URL列出来对比了一下，规律还是能找到一点的，至于怎么破解就不知道了。。。
  
  回复
  1. rpz说道：
    
    2017年12月14日上午10:11
    
    唉……
    谢谢。
    
    回复
青年1987说道：

2018年4月15日上午8:10

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

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

回复
per说道：

2018年5月27日上午12:59

想用excel实现比特币自动交易，已有交易所提供的api key和Secret，需要怎么设置？

回复
1. 施阳说道：
  
  2018年5月27日上午1:19
  
  我没有用过比特币交易系统。。。一般api都会有详细说明的，按照说明填写api地址和参数就行了
  
  回复
山有木说道：

2018年9月4日下午1:47

网抓案例中带有sign签名算法的网抓，该怎么进行操作，求版主大大指点
Request URL:https://h5api.m.taobao.com/h5/mtop.taobao.detail.getdetail/6.0/?jsv=2.4.8&appKey=12574478&t=1536039405162&sign=e79ba3ce2bfb0ac370ecbe7938330176&api=mtop.taobao.detail.getdetail&v=6.0&dataType=jsonp&ttid=2017%40taobao_h5_6.6.0&AntiCreep=true&type=jsonp&callback=mtopjsonp3&data=%7B%22itemNumId%22%3A%22564467768559%22%7D

回复
戎说道：

2018年12月19日下午4:50

请教：
Cookies里面__RequestVerificationToken值一直变动，这个如何爬取额

回复
多米尼说道：

2019年2月14日下午10:35

Query：是GET方式
Content：是POST方式
两者为啥能够同时存在呢~~~~

回复
1. 施阳说道：
  
  2019年2月18日下午12:50
  
  Query不是GET，POST也可能有Query，但有Content一定是POST
  
  回复
多米尼说道：

2019年2月15日上午1:12

大佬，你上面的请求类型是From Data，但是遇到Request Payload的POST如何解析 ~~~

回复
1. 施阳说道：
  
  2019年2月18日下午12:50
  
  一样的方法
  
  回复
  1. 多米尼说道：
    
    2019年2月21日上午9:41
    
    嗯嗯，已经搞定了。要把Request Payload里面的数据全部写到Content里面~~
    
    回复
    1. langyu说道：
      
      2022年1月27日下午2:49
      
      请问具体怎么操作的啊
      
      回复
朱文芬说道：

2019年3月12日下午3:24

求助：还是不懂，毕业论文急需空气质量的数据，数据页高达几千页，个人能力有限，可以帮忙写代码抓取吗
http://sthjt.ah.gov.cn/pages/SJZX_List.aspx?CityCode=340100&LX=4&KSSJ=2017-01-01%2000&JSSJ=2017-12-31%2023&page=1

回复
1. 施阳说道：
  
  2019年3月14日下午1:10
  
  这直接不就能抓到吗？只有最后page=后面的数字在变
  
  回复
游客说道：

2019年3月29日下午2:23

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

回复
jimmie说道：

2019年5月7日上午11:11

你好，我想请教一下，第1和第2案例里面的{0}[Data]和{2}[Data]是什么意思？非常感谢

回复
1. 施阳说道：
  
  2019年5月10日上午4:20
  
  你可以理解为在excel表里，{0}就是第一行，{2}就是第三行，取其中的[Data]这一列。
  
  回复
Henry说道：

2019年6月20日下午1:48

你好，请教一下，如何抓取下这个网站https://www.ccmn.cn/historyprice/cjxh_1/

回复
1. 施阳说道：
  
  2019年6月20日下午2:11
  
  https://www.ccmn.cn/history_data/cjxh.html
  
  回复
suntiny说道：

2019年6月22日下午3:51

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

回复
1. 施阳说道：
  
  2019年6月24日上午8:36
  
  Text.FromBinary(Web.Contents())
  
  回复
  1. suntiny说道：
    
    2019年6月24日下午12:02
    
    施老师，从fiddler中看到在浏览器点击文中链接返回了内容（不是json,和power query一样），浏览器启动了一个java公式链接，然后在文中的链接加上一串规律的字符后返加json（用这个修改的连接在浏览器中无返回json），而power query 在用web.contents（链接)图形化操作web视图中看到json，点转换数据后提示json结尾有多余字符。用text.frombinary也是返加一串字符html（无规律）。请老师指导。
    
    回复
    1. 施阳说道：
      
      2019年6月24日下午3:34
      
      你把PQ里返回的字符贴出来看看？
      
      回复
      1. suntiny说道：
        
        2019年6月24日下午5:01
        
        这个是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(有时浏览器有跳转网页，如果加载过，不跳转），老师，难度好大啊！
david说道：

2019年8月8日下午12:52

报错信息：
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

回复
崔说道：

2020年3月30日下午1:40

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

回复
1. 施阳说道：
  
  2020年3月30日下午2:19
  
  这个很简单，F12的XHR里总共就两个，POST方式提交，参考案例3
  
  回复
TT说道：

2020年4月5日下午8:58

公司内部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
合同列表

回复
陈小赖说道：

2020年4月14日下午7:03

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

回复
1. 施阳说道：
  
  2020年4月15日上午12:56
  
  登录就加个cookie，点击对应报表在F12里应该都能跟踪到。详细教程都在这里了https://pqfans.com/2230.html。再详细我看不到网页具体情况也回答不了。
  
  回复
vinci lau说道：

2020年4月25日上午10:49

请教老师, 这个网站需要登录, 然后选择某个公司某年份(或某季度)的报表, 这个报表怎么抓取? 谢谢!
http://webapi.cninfo.com.cn/#/dataBrowse

回复
小白说道：

2020年4月25日下午5:42

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

回复
Leon说道：

2020年5月5日上午9:56

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

回复
1. 施阳说道：
  
  2020年5月10日下午3:04
  
  值要么是数字要么是文本，2020-04-18是文本所以要双引号，1998哪来的？2020减4减18等于1998
  
  回复
王辉说道：

2020年6月19日下午4:30

请问施老师：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. 施阳说道：
  
  2020年6月21日下午3:09
  
  这个网页把数据写死了，直接复制链接从Web导入就行了，不用写代码，至少登陆前是这样。
  想尝试翻页但要登录，就没试。
  
  回复
红色山羊说道：

2020年6月26日下午3:59

powerquery 抓取金山文档中表格的数据可以实现吗（需要登录自己的账号密码的）

回复
1. 施阳说道：
  
  2020年6月28日上午12:53
  
  可以，但一般这种工具都会有API接口，参考https://open.wps.cn/docs/cloud/cloudfile-ability/obtain/api-list
  
  回复
罗简单说道：

2020年8月22日下午12:00

施阳老师您好，请教一个问题，需要获取烟草证信息，网址：
http://app.gjzwfw.gov.cn/jmopen/webapp/html5/licquery/index.html
输入许可证号：500302107375就可以查询
请教如何通过PowerQuery查询？
谢谢！

回复
崔说道：

2021年9月6日下午4:22

我个人感觉你那个有验证码，验证码每次都会变化，不太还用PQ进行抓取。

回复
初一说道：

2022年3月12日下午9:51

请问博主，目前腾讯文档在线表格的数据如何去获取，目前卡在这一步

回复
琅清说道：

2022年6月14日下午5:34

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

回复
1. CaptainWei说道：
  
  2022年7月4日下午6:24
  
  大佬请教一下，怎样通过PowerQuery读取Cookie呢？
  
  回复
一个初中教务员说道：

2022年12月11日上午10:36

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

回复
小韩说道：

2023年8月17日下午6:03

很好的帖子,感谢分享,美中不足的是案例中有些网页url链接打不开了。。。

回复