Цель
Я ищу, чтобы очистить данные оценочной карты сверчка 20/20 с веб-сайт Cricinfo, в идеале в форму CSV для анализа данных в Excel
В качестве примера текущие австралийские оценочные карты Австралии Bash 2011/12 доступны из
Фон
Я умею использовать VBA (либо автоматизацию IE
, либо используя XMLHTTP
, а затем используя регулярные выражения) для очистки данных с веб-сайтов, т.е.
Извлечь значения из HTML TD и Tr
В этом же вопросе был опубликован комментарий, предлагающий разбор html - чего я раньше не встречал - поэтому я взглянул на такие вопросы, как теги OpenEx Open, за исключением XHTML автономные теги
Query
Пока я мог писать регулярное выражение для анализа данных крикета ниже, я хотел бы получить совет относительно того, как я мог бы эффективно получить эти результаты с помощью синтаксического анализа html.
Пожалуйста, имейте в виду, что моим предпочтением является повторяемый формат CSV, содержащий:
Nirvana для меня будет решением, которое я смогу развернуть с помощью VBA или VBscript, чтобы я мог полностью автоматизировать мой анализ, но я полагаю, что мне придется использовать отдельный инструмент для анализа html.
Примеры ссылок на сайт и извлеченные данные
Есть два метода, которые я использую для "VBA". Я опишу их один на один.
1) Использование FireFox/Firebug Addon/Fiddler
2) Использование встроенного средства Excel для получения данных из Интернета
Поскольку этот пост будет прочитан многими, я даже рассмотрю очевидное. Пожалуйста, не стесняйтесь пропустить любую часть, которую вы знаете.
1) Использование FireFox/Firebug Addon/Fiddler
FireFox: http://en.wikipedia.org/wiki/Firefox Бесплатно скачать (http://www.mozilla.org/en-US/firefox/new/)
Firebug Addon: http://en.wikipedia.org/wiki/Firebug_%28software%29 Бесплатно скачать (https://addons.mozilla.org/en-US/firefox/addon/firebug/)
Fiddler: http://en.wikipedia.org/wiki/Fiddler_%28software%29 Бесплатно скачать (http://www.fiddler2.com/fiddler2/)
Как только вы установили Firefox, установите Firebug Addon. Firebug Addon позволяет вам проверять различные элементы веб-страницы. Например, если вы хотите узнать имя кнопки, просто щелкните по нему правой кнопкой мыши и нажмите "Осмотреть элемент с помощью Firebug", и он предоставит вам все детали, которые вам понадобятся для этой кнопки.
Другим примером может быть поиск имени таблицы на веб-сайте с данными, которые вам нужно удалить.
Я использую Fiddler только тогда, когда использую XMLHTTP. Это помогает мне увидеть точную информацию, которая будет передана, когда вы нажмете на кнопку. Из-за увеличения количества BOTS, которые царапают сайты, большинство сайтов сейчас, чтобы предотвратить автоматический отказ, захватить ваши координаты мыши и передать эту информацию, а скрипач действительно поможет вам отладить эту информацию, которая передается. Я не буду вдаваться в подробности здесь, так как эта информация может быть использована злонамеренно.
Теперь давайте рассмотрим простой пример того, как очистить URL-адрес, опубликованный в вашем вопросе
http://www.espncricinfo.com/big-bash-league-2011/engine/match/524915.html
Сначала найдите имя таблицы, в которой есть эта информация. Просто щелкните правой кнопкой мыши по таблице и нажмите "Осмотреть элемент с помощью Firebug", и он даст вам снимок.
Итак, теперь мы знаем, что наши данные хранятся в таблице под названием "inningsBat1". Если мы можем извлечь содержимое этой таблицы в файл Excel, мы можем определенно работать с данными для нашего анализа. Вот пример кода, который сбрасывает эту таблицу в Sheet1
Прежде чем продолжить, я бы рекомендовал закрыть все Excel и начать новый экземпляр.
Запустите VBA и вставьте Userform. Поместите командную кнопку и элемент управления веб-браузером. Ваша Userform может выглядеть так:
Вставьте этот код в область кода пользователя
Option Explicit
'~~> Set Reference to Microsoft HTML Object Library
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Sub CommandButton1_Click()
Dim URL As String
Dim oSheet As Worksheet
Set oSheet = Sheets("Sheet1")
URL = "http://www.espncricinfo.com/big-bash-league-2011/engine/match/524915.html"
PopulateDataSheets oSheet, URL
MsgBox "Data Scrapped. Please check " & oSheet.Name
End Sub
Public Sub PopulateDataSheets(wsk As Worksheet, URL As String)
Dim tbl As HTMLTable
Dim tr As HTMLTableRow
Dim insertRow As Long, Row As Long, col As Long
On Error GoTo whoa
WebBrowser1.navigate URL
WaitForWBReady
Set tbl = WebBrowser1.Document.getElementById("inningsBat1")
With wsk
.Cells.Clear
insertRow = 0
For Row = 0 To tbl.Rows.Length - 1
Set tr = tbl.Rows(Row)
If Trim(tr.innerText) <> "" Then
If tr.Cells.Length > 2 Then
If tr.Cells(1).innerText <> "Total" Then
insertRow = insertRow + 1
For col = 0 To tr.Cells.Length - 1
.Cells(insertRow, col + 1) = tr.Cells(col).innerText
Next
End If
End If
End If
Next
End With
whoa:
Unload Me
End Sub
Private Sub Wait(ByVal nSec As Long)
nSec = nSec + Timer
While Timer < nSec
DoEvents
Sleep 100
Wend
End Sub
Private Sub WaitForWBReady()
Wait 1
While WebBrowser1.ReadyState <> 4
Wait 3
Wend
End Sub
Теперь запустите Userform и нажмите кнопку Command. Вы заметите, что данные сбрасываются в Sheet1. См. Снимок
Аналогичным образом вы можете очистить и другую информацию.
2) Использование встроенного средства Excel для получения данных из Интернета
Я считаю, что вы используете Excel 2007, поэтому я возьму это в качестве примера, чтобы очистить вышеупомянутую ссылку.
Перейдите к Sheet2. Теперь перейдите на вкладку "Данные" и нажмите кнопку "Из Интернета" в крайнем правом углу. См. Снимок.
Введите URL-адрес в окне "New Web Query Window" и нажмите "Go"
После загрузки страницы выберите соответствующую таблицу, которую вы хотите импортировать, нажав на маленькую стрелку, как показано в снимке. После этого нажмите "Импорт"
Затем Excel спросит вас, где вы хотите импортировать данные. Выберите соответствующую ячейку и нажмите "ОК". И все готово! Данные будут импортированы в указанную ячейку.
Если вы хотите записать макрос и автоматизировать его:)
Вот макрос, который я записал.
Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.espncricinfo.com/big-bash-league-2011/engine/match/524915.html" _
, Destination:=Range("$A$1"))
.Name = "524915"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """inningsBat1"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
Надеюсь, это поможет. Дайте мне знать, если у вас все еще есть вопросы.
Сид
Для кого-то другого, заинтересованного в этом, я закончил использование кода ниже, основываясь на предыдущем ответе на вопрос Сиддхарта
XMLHttp
был значительно быстрее, чем автоматизация IE
X
)
Public Sub PopulateDataSheets_XML()
Dim URL As String
Dim ws As Worksheet
Dim lngRow As Long
Dim lngRecords As Long
Dim lngWrite As Long
Dim lngSpare As Long
Dim lngInnings As Long
Dim lngRow1 As Long
Dim X(1 To 15, 1 To 4) As String
Dim objFSO As Object
Dim objTF As Object
Dim xmlHttp As Object
Dim htmldoc As HTMLDocument
Dim htmlbody As htmlbody
Dim tbl As HTMLTable
Dim tr As HTMLTableRow
Dim strInnings As String
s = Timer()
Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP")
Set objFSO = CreateObject("scripting.filesystemobject")
X(1, 1) = "http://www.espncricinfo.com/indian-premier-league-2011/engine/match/"
X(1, 2) = 501198
X(1, 3) = 501271
X(1, 4) = "indian-premier-league-2011"
X(2, 1) = "http://www.espncricinfo.com/big-bash-league-2011/engine/match/"
X(2, 2) = 524915
X(2, 3) = 524945
X(2, 4) = "big-bash-league-2011"
X(3, 1) = "http://www.espncricinfo.com/ausdomestic-2010/engine/match/"
X(3, 2) = 461028
X(3, 3) = 461047
X(3, 4) = "big-bash-league-2010"
Set htmldoc = New HTMLDocument
Set htmlbody = htmldoc.body
For lngRow = 1 To UBound(X, 1)
If Len(X(lngRow, 1)) = 0 Then Exit For
Set objTF = objFSO.createtextfile("c:\temp\" & X(lngRow, 4) & ".csv")
For lngRecords = X(lngRow, 2) To X(lngRow, 3)
URL = X(lngRow, 1) & lngRecords & ".html"
xmlHttp.Open "GET", URL
xmlHttp.send
Do While xmlHttp.Status <> 200
DoEvents
Loop
htmlbody.innerHTML = xmlHttp.responseText
objTF.writeline X(lngRow, 1) & lngRecords & ".html"
For lngInnings = 1 To 2
strInnings = "Innings " & lngInnings
objTF.writeline strInnings
Set tbl = Nothing
On Error Resume Next
Set tbl = htmlbody.Document.getElementById("inningsBat" & lngInnings)
On Error GoTo 0
If Not tbl Is Nothing Then
lngWrite = 0
For lngRow1 = 0 To tbl.Rows.Length - 1
Set tr = tbl.Rows(lngRow1)
If Trim(tr.innerText) <> vbNewLine Then
If tr.Cells.Length > 2 Then
If tr.Cells(1).innerText <> "Extras" Then
If Len(tr.Cells(1).innerText) > 0 Then
objTF.writeline strInnings & "-" & lngWrite & "," & Trim(tr.Cells(1).innerText) & "," & Trim(tr.Cells(3).innerText)
lngWrite = lngWrite + 1
End If
Else
objTF.writeline strInnings & "-" & lngWrite & "," & Trim(tr.Cells(1).innerText) & "," & Trim(tr.Cells(3).innerText)
lngWrite = lngWrite + 1
Exit For
End If
End If
End If
Next
For lngSpare = 12 To lngWrite Step -1
objTF.writeline strInnings & "-" & lngWrite + (12 - lngSpare)
Next
Else
For lngSpare = 1 To 13
objTF.writeline strInnings & "-" & lngWrite + (12 - lngSpare)
Next
End If
Next
Next
Next
'Call ConsolidateSheets
End Sub
RegEx не является полным решением для разбора HTML, потому что он не гарантированно является регулярным.
Вы должны использовать HtmlAgilityPack для запроса HTML. Это позволит вам использовать селектор CSS для запроса HTML, аналогичного тому, как вы это делаете с помощью jQuery.