如何使用vba从Datastore.prime中抓取数据 [英] How to scrape data from Datastore.prime using vba
问题描述
我正试图从这个网站上搜集数据
源代码包含此格式的数据。
DataStore.prime('stagefixtures',$。exptend({stageId:12496,isAggregate:false},calendar.parameter()),[[959688,1,'Monday,Dec 21 2015','20:00',13,'阿森纳',0,167,'曼城,0,'2:1','2:0',1,1,'FT','1',0,1,112,0]
,[959683,4,'星期六,2015年12月26日' ,'12:45',96,'斯托克',0,32,'曼联',0,'vs',, 0,1 ,,' - 1',0,1,13,0]
,[959615,4,'星期六,2015年12月26日','15:00',24,'阿斯顿维拉',0,29,'西汉姆',0,'vs',, 0,1 ,,' -1',0,1,6,0]
,[959625,4,'星期六,2015年12月26日','15:00',183,'伯恩茅斯',0,162,'水晶宫',0 ,'vs',, 0,1 ,,' - 1',0,1,10,0]
,[959635,4,'星期六,2015年12月26日','15:00',15, '切尔西',0,27,'沃特福德',0,'vs',, 0,1 ,,' - 1',0,1,15,0]
,[959645,4,'星期六, 2015年12月26日','15:00',26,'利物浦',0,14,'莱斯特',0,'vs',, 0,1 ,,' - 1',0,1,15,0]
,[959655,4,'星期六,2015年12月26日','15:00',167,'曼城',0,16,'桑德兰',0,'vs',, 0,1, ,' - 1',0,1,4,0]
,[959691,4,'星期六,2015年12月26日','15:00',259,'斯旺西',0,175,'West Bromwich Albion ,0, 'VS', ,0,1 ,,' - 1',0,1,5,0]
,[959698,4,'星期六,2015年12月26日','15:00',30,'托特纳姆',0,168 ,'Norwich',0,'vs',, 0,1 ,,' - 1',0,1,8,0]
,[959665,4,'星期六,2015年12月26日','17 :30',23,'Newcastle United',0,31,'Everton',0,'vs',, 0,1 ,,' - 1',0,1,7,0]
,[ 959674,4,'星期六,2015年12月26日','19:45',18,'南安普顿',0,13,'阿森纳',0,'vs',, 0,1 ,,' - 1',0 ,1,11,0]
]);
此代码应该从表格格式中抓取数据,但我在这种情况下不要怎么做。
期权明确
Sub WeeklyFixtures()
Dim IE As Object,obj As Object
Dim r As Integer,c As Integer,t As Integer
Dim elemCollection As Object
设置IE = CreateObject( InternetExplorer.Application)
使用IE
.Visible = True
.navigate(http://www.whoscored.com/regions/252/tournaments/2/england -premier-league)
虽然IE.ReadyState<> 4
DoEvents
Wend
Do IE.busy:DoEvents:Loop
ThisWorkbook.Sheet1.Clear
Set elemCollection = IE.Document.getElementsByTagName(TABLE)
对于t = 0 To(elemCollection.Length - 1)
对于r = 0 To(elemCollection(t) .Rows.Length - 1)
对于c = 0 To(elemCollection(t).Rows(r).Cells.Length - 1)
ThisWorkbook.Worksheets(1).Cells(r + 1, c + 1)= elemCollection(t).Rows(r).Cells(c).innerText
Next c
Next r
Next t
End with
设置IE =无
结束子
试用此代码:
Option Explicit
Sub GetWhoscoredData ()
Dim strCont,arrRows,strComma,arrQuots,i,arrCols
使用CreateObject(Microsoft.XMLHTTP)
。打开GET, http://www.whoscored.com/regions/252/tournaments/2/england-premi er-league,False
。发送
strCont = .ResponseText
结束
strCont = Split(strCont,'stagefixtures')(1)
strCont = Split(strCont,[[)(1)
strCont = Split(strCont,]);)(0)
strCont = Replace(strCont,vbCrLf, )
strComma = Mid(CreateObject(Scriptlet.TypeLib)。GUID,2,36)
arrQuots = Split(strCont,')
For i = 1 To UBound(arrQuots) )步骤2
arrQuots(i)=替换(arrQuots(i),,,strComma)
下一个
strCont =加入(arrQuots,)
arrRows =拆分(strCont,],[)
For i = 0 To UBound(arrRows)
arrCols = Split(arrRows(i),,)
Cells(i + 1,1 ).Value = Replace(arrCols(2),strComma,,)
Cells(i + 1,2).Value = Replace(arrCols(3),strComma,,)
Cells (i + 1,3).Value = Replace(arrCols(14),strComma,,)
Cells(i + 1,4).Value = Replace(arrCols(5),strComma,, )
Cells( i + 1,5)。NumberFormat =@
Cells(i + 1,5).Value = Replace(arrCols(10),strComma,,)
Cells(i + 1, 6).Value = Replace(arrCols(8),strComma,,)
Next
Cells.Columns.AutoFit
End Sub
它为我提供的输出如下:
I am trying to scrape data from this website http://www.whoscored.com/regions/252/tournaments/2/england-premier-league
When I use inspect element I see that data is in table format as shown in picture below.
The source code has the data in this format.
DataStore.prime('stagefixtures', $.extend({ stageId: 12496, isAggregate: false }, calendar.parameter()), [[959688,1,'Monday, Dec 21 2015','20:00',13,'Arsenal',0,167,'Manchester City',0,'2 : 1','2 : 0',1,1,'FT','1',0,1,112,0] ,[959683,4,'Saturday, Dec 26 2015','12:45',96,'Stoke',0,32,'Manchester United',0,'vs',,0,1,,'-1',0,1,13,0] ,[959615,4,'Saturday, Dec 26 2015','15:00',24,'Aston Villa',0,29,'West Ham',0,'vs',,0,1,,'-1',0,1,6,0] ,[959625,4,'Saturday, Dec 26 2015','15:00',183,'Bournemouth',0,162,'Crystal Palace',0,'vs',,0,1,,'-1',0,1,10,0] ,[959635,4,'Saturday, Dec 26 2015','15:00',15,'Chelsea',0,27,'Watford',0,'vs',,0,1,,'-1',0,1,15,0] ,[959645,4,'Saturday, Dec 26 2015','15:00',26,'Liverpool',0,14,'Leicester',0,'vs',,0,1,,'-1',0,1,15,0] ,[959655,4,'Saturday, Dec 26 2015','15:00',167,'Manchester City',0,16,'Sunderland',0,'vs',,0,1,,'-1',0,1,4,0] ,[959691,4,'Saturday, Dec 26 2015','15:00',259,'Swansea',0,175,'West Bromwich Albion',0,'vs',,0,1,,'-1',0,1,5,0] ,[959698,4,'Saturday, Dec 26 2015','15:00',30,'Tottenham',0,168,'Norwich',0,'vs',,0,1,,'-1',0,1,8,0] ,[959665,4,'Saturday, Dec 26 2015','17:30',23,'Newcastle United',0,31,'Everton',0,'vs',,0,1,,'-1',0,1,7,0] ,[959674,4,'Saturday, Dec 26 2015','19:45',18,'Southampton',0,13,'Arsenal',0,'vs',,0,1,,'-1',0,1,11,0] ]);
This code is supposed to scrape data from the table format but I don't how to do it in this case.
Option Explicit
Sub WeeklyFixtures()
Dim IE As Object, obj As Object
Dim r As Integer, c As Integer, t As Integer
Dim elemCollection As Object
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
.navigate ("http://www.whoscored.com/regions/252/tournaments/2/england-premier-league")
While IE.ReadyState <> 4
DoEvents
Wend
Do While IE.busy: DoEvents: Loop
ThisWorkbook.Sheet1.Clear
Set elemCollection = IE.Document.getElementsByTagName("TABLE")
For t = 0 To (elemCollection.Length - 1)
For r = 0 To (elemCollection(t).Rows.Length - 1)
For c = 0 To (elemCollection(t).Rows(r).Cells.Length - 1)
ThisWorkbook.Worksheets(1).Cells(r + 1, c + 1) = elemCollection(t).Rows(r).Cells(c).innerText
Next c
Next r
Next t
End With
Set IE = Nothing
End Sub
Try this code:
Option Explicit
Sub GetWhoscoredData()
Dim strCont, arrRows, strComma, arrQuots, i, arrCols
With CreateObject("Microsoft.XMLHTTP")
.Open "GET", "http://www.whoscored.com/regions/252/tournaments/2/england-premier-league", False
.Send
strCont = .ResponseText
End With
strCont = Split(strCont, "'stagefixtures'")(1)
strCont = Split(strCont, "[[")(1)
strCont = Split(strCont, "]);")(0)
strCont = Replace(strCont, vbCrLf, "")
strComma = Mid(CreateObject("Scriptlet.TypeLib").GUID, 2, 36)
arrQuots = Split(strCont, "'")
For i = 1 To UBound(arrQuots) Step 2
arrQuots(i) = Replace(arrQuots(i), ",", strComma)
Next
strCont = Join(arrQuots, "")
arrRows = Split(strCont, "],[")
For i = 0 To UBound(arrRows)
arrCols = Split(arrRows(i), ",")
Cells(i + 1, 1).Value = Replace(arrCols(2), strComma, ",")
Cells(i + 1, 2).Value = Replace(arrCols(3), strComma, ",")
Cells(i + 1, 3).Value = Replace(arrCols(14), strComma, ",")
Cells(i + 1, 4).Value = Replace(arrCols(5), strComma, ",")
Cells(i + 1, 5).NumberFormat = "@"
Cells(i + 1, 5).Value = Replace(arrCols(10), strComma, ",")
Cells(i + 1, 6).Value = Replace(arrCols(8), strComma, ",")
Next
Cells.Columns.AutoFit
End Sub
The output it gives for me is as follows:
这篇关于如何使用vba从Datastore.prime中抓取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!