通过Power Query抓取火车票余票信息

Excel实战分享

春运将至，很多小伙伴都在刷火车票吧，希望大伙都能抢到票，早日平安回家。

利用Power Query查询火车票余票信息，来凑个热闹，先上图。
--------------------------------------------------------------------------------------------------------------


--------------------------------------------------------------------------------------------------------------


--------------------------------------------------------------------------------------------------------------
其中步骤大概分为3步：

    1、获取站名代号与站名对应表

    2、再根据传递进来的参数，获取相关余票原始数据

    3、对余票原始数据进行数据处理，最终以表格的形式呈现出来

1. let
   
2.     出发地="北京",
   
3.     目的地="上海",
   
4.     出发日="2018-02-11",
   
5.     url="https://kyfw.12306.cn/otn/resources/js/framework/station_name.js?station_version=1.9027",        //车站信息链接
   
6.     web_stations = Text.BetweenDelimiters(Text.FromBinary(Binary.Buffer(Web.Contents(url))),"var station_names ='","';"),    //车站信息
   
7.     sta=(n as number) as list=>List.Buffer(List.Alternate(List.Skip(Text.Split(web_stations,"|"),n),4,1,1)),    //用于获取车站名称列表、车站代号列表
   
8.     stations=(s as text) as text=>sta(2){List.PositionOf(sta(1),s)},       //获取指定车站名称的车站代号
   
9.     url2="https://kyfw.12306.cn/otn/leftTicket/queryZ",                    //余票查询链接
   
10.     headers=[#"User-Agent"="Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/45.0.2454.101 Safari/537.36",#"X-Requested-With"="XMLHttpRequest"],
    
11.     query=[leftTicketDTO.train_date=出发日,leftTicketDTO.from_station=stations(出发地),leftTicketDTO.to_station=stations(目的地),purpose_codes="ADULT"],
    
12.     web_data=Json.Document(Binary.Buffer(Web.Contents(url2,[Query=query,Headers=headers])))[data],        //车次信息
    
13.     fields={"车次","出发站","到达站","出发时间","到达时间","历时","商务座","一等座","二等座","高级软卧","软卧","动卧","硬卧","软座","硬座","无座","其他"},
    
14.     business=(a as text,b as text) as text=>if a&b="" then "" else if a&b="无" or a&b="无无" then "无" else if a<>"无" and a<>"" then a else b,    //特等座与商务座的余票信息
    
15.     seat=(lst as list,i as number) as text=>if i=6 or i=7 then "【"&{"过","始","过","终"}{Number.From(lst{i}=lst{i-2})+i*2-12}&"】 "&Record.Field(web_data[map],lst{i})
    
16.                                              else if i=25 then business(lst{25},lst{32})
    
17.                                              else lst{i},    //指定车次、指定座位的余票信息
    
18.     records=(lst as list) as list=>List.Transform({3,6,7,8,9,10,25,31,30,21,23,33,28,24,26,29,27},each seat(lst,_)), //指定车次的余票信息
    
19.     standby = #table(fields,List.Transform(web_data[result],each records(Text.Split(_,"|")))),        //余票信息
    
20.     final=Table.Buffer(Table.ReplaceValue(standby,"","--",(x,y,z)=>if x=y then z else x,fields))      //表格整理
    
21. in
    
22.     final

复制代码

lukewu77

打开后什么都没有？

Excel实战分享

lukewu77 发表于 2018-1-16 15:36
打开后什么都没有？

Excel2010/Excel2013需要安装Power Query插件，Excel2016已经内置了Power Query（数据获取与转换），具体怎么操作，你可以去看看张文洲老师分享的文章 快来学习 Power Query 吧，屌爆了的Excel加载项！

呼吸爱天王

太强大了，可以学习下，不过感觉还是不太会。

阿狸小心

Excel实战分享 发表于 2018-1-16 15:43
Excel2010/Excel2013需要安装Power Query插件，Excel2016已经内置了Power Query（数据获取与转换），具 ...

是附件有问题，解压出错了

suiyuanban

报这个错是什么原因
DataFormat.Error: JSON 输入的结尾有多余的字符。
详细信息:
    Value=
    Position=0

天Э昕♂淡然

Josn不标准，有报错，请楼主看看哪里出了问题。。。

工作状态

学习了，好厉害啊

z135790

DataFormat.Error: JSON 输入的结尾有多余的字符。
详细信息:
    Value=
    Position=0

是JSON的问题，还是下面余票查询链接的问题，我在网页上试了下，这个页面打不开。
url2="https://kyfw.12306.cn/otn/leftTicket/queryZ",                    //余票查询链接

deadzlq

接口地址变了，还带反爬