You should also post the source html of the page along with that picture.
However, I don't think there's an easy way to achieve what you want to do. First of all, the page uses a lot of nested tables, and none of them has an id, so obviously you can't use GetElementById to get the table you need. You have to use GetElementByTagName to get a collection of all tables, then you have to loop thru each one of it testing to see if it's the right one, may be by looking at it's children.count. Once you get he right table, you have to loop thru the children and extract the innertext, look at each one to see what it is and create a datarow, set the appropriate datarow.item value, and finally add the datarow to your datatable.
Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it. - Abraham Lincoln -
Stanav, this is the same page I pmed you. there is a table called "Song_Info" that I would like to capture and convert to datatable.
I have been looking at this code and also this function...
Code:
Private Function ConvertHTMLTablesToDataSet(ByVal HTML As String) As DataSet
' Declarations
Dim ds As New DataSet
Dim dt As DataTable
Dim dr As DataRow
Dim dc As DataColumn
Dim TableExpression As String = "<table[^>]*>(.*?)</table>"
Dim HeaderExpression As String = "<th[^>]*>(.*?)</th>"
Dim RowExpression As String = "<tr[^>]*>(.*?)</tr>"
Dim ColumnExpression As String = "<td[^>]*>(.*?)</td>"
Dim HeadersExist As Boolean = False
Dim iCurrentColumn As Integer = 0
Dim iCurrentRow As Integer = 0
' Get a match for all the tables in the HTML
Dim Tables As MatchCollection = Regex.Matches(HTML, TableExpression, RegexOptions.Multiline Or RegexOptions.Singleline Or RegexOptions.IgnoreCase)
' Loop through each table element
For Each Table As Match In Tables
' Reset the current row counter and the header flag
iCurrentRow = 0
HeadersExist = False
' Add a new table to the DataSet
dt = New DataTable
' Create the relevant amount of columns for this table (use the headers if they exist, otherwise use default names)
If Table.Value.Contains("<th") Then
' Set the HeadersExist flag
HeadersExist = True
' Get a match for all the rows in the table
Dim Headers As MatchCollection = Regex.Matches(Table.Value, HeaderExpression, RegexOptions.Multiline Or RegexOptions.Singleline Or RegexOptions.IgnoreCase)
' Loop through each header element
For Each Header As Match In Headers
dt.Columns.Add(Header.Groups(1).ToString)
Next
Else
For iColumns As Integer = 1 To Regex.Matches(Regex.Matches(Regex.Matches(Table.Value, TableExpression, RegexOptions.Multiline Or RegexOptions.Singleline Or RegexOptions.IgnoreCase).Item(0).ToString, RowExpression, RegexOptions.Multiline Or RegexOptions.Singleline Or RegexOptions.IgnoreCase).Item(0).ToString, ColumnExpression, RegexOptions.Multiline Or RegexOptions.Singleline Or RegexOptions.IgnoreCase).Count
dt.Columns.Add("Column " & iColumns)
Next
End If
' Get a match for all the rows in the table
Dim Rows As MatchCollection = Regex.Matches(Table.Value, RowExpression, RegexOptions.Multiline Or RegexOptions.Singleline Or RegexOptions.IgnoreCase)
' Loop through each row element
For Each Row As Match In Rows
' Only loop through the row if it isn't a header row
If Not (iCurrentRow = 0 And HeadersExist = True) Then
' Create a new row and reset the current column counter
dr = dt.NewRow
iCurrentColumn = 0
' Get a match for all the columns in the row
Dim Columns As MatchCollection = Regex.Matches(Row.Value, ColumnExpression, RegexOptions.Multiline Or RegexOptions.Singleline Or RegexOptions.IgnoreCase)
' Loop through each column element
For Each Column As Match In Columns
' Add the value to the DataRow
dr(iCurrentColumn) = Column.Groups(1).ToString
' Increase the current column
iCurrentColumn += 1
Next
' Add the DataRow to the DataTable
dt.Rows.Add(dr)
End If
' Increase the current row counter
iCurrentRow += 1
Next
' Add the DataTable to the DataSet
ds.Tables.Add(dt)
Next
Return ds
End Function
This is what I came up with... Form2 is just a form with 1 textbox, 1 button and a webbrowser.
Code:
Public Class Form2
Private songData As DataTable = Nothing
Public ReadOnly Property SongTable() As DataTable
Get
Return Me.songData
End Get
End Property
Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.songData = New DataTable("SongData")
Dim primary As DataColumn
With songData.Columns
primary = .Add("Song Number", GetType(Integer))
.Add("Title", GetType(String))
.Add("Authors", GetType(String))
.Add("Copyright", GetType(String))
.Add("Catalogs", GetType(String))
.Add("Administrators", GetType(String))
.Add("Key", GetType(String))
.Add("Key Line", GetType(String))
.Add("Also Known As", GetType(String))
.Add("Themes", GetType(String))
.Add("Lyrics", GetType(String))
End With
Me.songData.PrimaryKey = New DataColumn() {primary}
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Navigate to the song detail page
WB1.Navigate(Me.TextBox1.Text)
End Sub
Private Sub WB1_DocumentCompleted(ByVal sender As Object, ByVal e As System.Windows.Forms.WebBrowserDocumentCompletedEventArgs) Handles WB1.DocumentCompleted
Dim sbLyrics As New System.Text.StringBuilder()
Dim tables As HtmlElementCollection = WB1.Document.GetElementsByTagName("table")
Dim rows As HtmlElementCollection = Nothing
Dim cells As HtmlElementCollection = Nothing
Dim songRow As DataRow = Me.songData.NewRow()
For Each table As HtmlElement In tables
rows = table.GetElementsByTagName("tr")
If rows.Count = 1 Then
cells = table.GetElementsByTagName("td")
For Each cell As HtmlElement In cells
Dim spanCollection As HtmlElementCollection = cell.GetElementsByTagName("span")
If spanCollection.Count > 0 Then
For Each itm As HtmlElement In spanCollection
Dim txt As String = itm.InnerText
If txt.IndexOf(Environment.NewLine) > 0 Then
sbLyrics.Append(txt)
End If
Next
songRow("Lyrics") = sbLyrics.ToString
End If
Next
ElseIf rows.Count = 12 Then
Dim id As Integer = -1
Dim colName As String = String.Empty
Dim value As String = String.Empty
songRow("Title") = rows(0).Children(0).InnerText
For i As Integer = 1 To 9
colName = rows(i).Children(0).InnerText
value = rows(i).Children(1).InnerText
If colName = "Song Number" Then
id = Integer.Parse(value)
songRow(colName) = id
Else
songRow(colName) = value
End If
Next
If Me.songData.Rows.Find(id) Is Nothing Then
Me.songData.Rows.Add(songRow)
End If
End If
Next
End Sub
End Class
Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it. - Abraham Lincoln -
Looks great. Thanks very much for your kind help. I have added the code but am not sure if the values are being stored. How can I test it ? How do I read back the values stored in each column of songData Datatable ?
Instead of using WB1_DocumentCompleted to fire the code, can I use button2 ?
Last edited by Xancholy; Jul 15th, 2008 at 02:47 PM.
This is the updated code which uses GetElementById... And yes, you can use button2 instead of button1.
Code:
Public Class Form2
Private songData As DataTable = Nothing
Public ReadOnly Property SongTable() As DataTable
Get
Return Me.songData
End Get
End Property
Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.WB1.ScriptErrorsSuppressed = True
Me.songData = New DataTable("SongData")
Dim primary As DataColumn
With songData.Columns
primary = .Add("SongNumber", GetType(Integer))
.Add("Title", GetType(String))
.Add("Author", GetType(String))
.Add("Copyright", GetType(String))
.Add("Catalog", GetType(String))
.Add("Administrator", GetType(String))
.Add("Key", GetType(String))
.Add("KeyLine", GetType(String))
.Add("AlsoKnownAs", GetType(String))
.Add("Themes", GetType(String))
.Add("Lyrics", GetType(String))
End With
Me.songData.PrimaryKey = New DataColumn() {primary}
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
WB1.Navigate(Me.TextBox1.Text)
End Sub
Private Sub WB1_DocumentCompleted(ByVal sender As Object, ByVal e As System.Windows.Forms.WebBrowserDocumentCompletedEventArgs) Handles WB1.DocumentCompleted
Dim sbLyrics As New System.Text.StringBuilder()
Dim rows As HtmlElementCollection = Nothing
Dim cells As HtmlElementCollection = Nothing
Dim detailTable As HtmlElement = WB1.Document.GetElementById("details")
Dim lyricTable As HtmlElement = WB1.Document.GetElementById("lyrics")
Dim songRow As DataRow = Me.songData.NewRow()
Dim songId As Integer = -1
'Get the lyrics
If Not lyricTable Is Nothing Then
songRow("Lyrics") = lyricTable.InnerText
End If
'Get song details
If Not detailTable Is Nothing Then
rows = detailTable.GetElementsByTagName("tr")
If rows.Count >= 12 Then
Dim colName As String = String.Empty
Dim value As String = String.Empty
songRow("Title") = rows(0).InnerText
For i As Integer = 1 To 9
Try
colName = rows(i).Children(0).InnerText.Replace(" ", "")
value = rows(i).Children(1).InnerText
If colName = "SongNumber" Then
songId = Integer.Parse(value)
songRow(colName) = songId
Else
songRow(colName) = value
End If
Catch ex As Exception
Debug.WriteLine(ex.Message & ", " & ex.InnerException.Message)
End Try
Next
End If
End If
'Now add the row to our table if it's not there already
If songId >= 0 AndAlso Me.songData.Rows.Find(songId) Is Nothing Then
Me.songData.Rows.Add(songRow)
End If
End Sub
End Class
As for reading data from a datatable, you would loop thru the rows collection and read each item from the row.
Code:
For each row As DataRow in datatable.Rows
For Each col As DataColumn In datatable.Columns
MessageBox.Show(row.Item(col).ToString)
Next
Next
Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it. - Abraham Lincoln -
For Each row As DataRow In Me.songData.Rows
For Each col As DataColumn In Me.songData.Columns
MessageBox.Show(row.Item(col.ColumnName).ToString)
Next
Next
Edit: Just saw overloaded method in IDE your method should work.
yes, I navigated to a song page. Ritesh, that does not seem to work either...
I have shifted this code to a separate button2's click event
Code:
Dim sbLyrics As New System.Text.StringBuilder()
Dim rows As HtmlElementCollection = Nothing
Dim cells As HtmlElementCollection = Nothing
Dim detailTable As HtmlElement = WB1.Document.GetElementById("details")
Dim lyricTable As HtmlElement = WB1.Document.GetElementById("lyrics")
Dim songRow As DataRow = Me.songData.NewRow()
Dim songId As Integer = -1
'Get the lyrics
If Not lyricTable Is Nothing Then
songRow("Lyrics") = lyricTable.InnerText
End If
'Get song details
If Not detailTable Is Nothing Then
rows = detailTable.GetElementsByTagName("tr")
If rows.Count >= 12 Then
Dim colName As String = String.Empty
Dim value As String = String.Empty
songRow("Title") = rows(0).InnerText
For i As Integer = 1 To 9
Try
colName = rows(i).Children(0).InnerText.Replace(" ", "")
value = rows(i).Children(1).InnerText
If colName = "SongNumber" Then
songId = Integer.Parse(value)
songRow(colName) = songId
Else
songRow(colName) = value
End If
Catch ex As Exception
Debug.WriteLine(ex.Message & ", " & ex.InnerException.Message)
End Try
Next
End If
End If
'Now add the row to our table if it's not there already
If songId >= 0 AndAlso Me.songData.Rows.Find(songId) Is Nothing Then
Me.songData.Rows.Add(songRow)
End If
I then use Button3 to fire the data retrieval. But I am not getting any data to display.
Could you please try it your way and let me know if the code can be amended to display scraped data.
Last edited by Xancholy; Jul 15th, 2008 at 04:34 PM.
Public Class Form1
Private songData As DataTable = Nothing
Public ReadOnly Property SongTable() As DataTable
Get
Return Me.songData
End Get
End Property
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.WB1.ScriptErrorsSuppressed = True
WB1.Navigate(Me.ToolStripTextBox1.Text)
Me.songData = New DataTable("SongData")
Dim primary As DataColumn
With songData.Columns
primary = .Add("SongNumber", GetType(Integer))
.Add("Title", GetType(String))
.Add("Author", GetType(String))
.Add("Copyright", GetType(String))
.Add("Catalog", GetType(String))
.Add("Administrator", GetType(String))
.Add("Key", GetType(String))
.Add("KeyLine", GetType(String))
.Add("AlsoKnownAs", GetType(String))
.Add("Themes", GetType(String))
.Add("Lyrics", GetType(String))
End With
Me.songData.PrimaryKey = New DataColumn() {primary}
End Sub
Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
'Trigger code from this button
Dim sbLyrics As New System.Text.StringBuilder()
Dim rows As HtmlElementCollection = Nothing
Dim cells As HtmlElementCollection = Nothing
Dim detailTable As HtmlElement = WB1.Document.GetElementById("details")
Dim lyricTable As HtmlElement = WB1.Document.GetElementById("lyrics")
Dim songRow As DataRow = Me.songData.NewRow()
Dim songId As Integer = -1
'Get the lyrics
If Not lyricTable Is Nothing Then
songRow("Lyrics") = lyricTable.InnerText
End If
'Get song details
If Not detailTable Is Nothing Then
rows = detailTable.GetElementsByTagName("tr")
If rows.Count >= 12 Then
Dim colName As String = String.Empty
Dim value As String = String.Empty
songRow("Title") = rows(0).InnerText
For i As Integer = 1 To 9
Try
colName = rows(i).Children(0).InnerText.Replace(" ", "")
value = rows(i).Children(1).InnerText
If colName = "SongNumber" Then
songId = Integer.Parse(value)
songRow(colName) = songId
Else
songRow(colName) = value
End If
Catch ex As Exception
Debug.WriteLine(ex.Message & ", " & ex.InnerException.Message)
End Try
Next
End If
End If
'Now add the row to our table if it's not there already
If songId >= 0 AndAlso Me.songData.Rows.Find(songId) Is Nothing Then
Me.songData.Rows.Add(songRow)
End If
End Sub
Private Sub ToolStripButton3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton3.Click
'trigger data retrieval
For Each row As DataRow In Me.songData.Rows
For Each col As DataColumn In Me.songData.Columns
MessageBox.Show(row.Item(col.ColumnName).ToString)
Next
Next
End Sub
End Class
Last edited by Xancholy; Jul 15th, 2008 at 07:30 PM.
The code looks alright. However, you choose to navigate to the web page in form.load event. At that time, what's the value of Me.ToolStripTextBox1.Text? It needs to be an url that links to a song detail page.
Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it. - Abraham Lincoln -
Thanks for looking over it. The initial value of the url Me.ToolStripTextBox1.Text is the login page. I then manually login and browse over to any song page.
ToolStripButton2 becomes the 'import' button.
Does this code work at your end ? Are you able to import and retrieve ?
Last edited by Xancholy; Jul 16th, 2008 at 09:34 AM.
Thanks for looking over it. The initial value of the url Me.ToolStripTextBox1.Text is the login page. I then manually login and browse over to any song page.
ToolStripButton2 becomes the 'import' button.
Does this code work at your end ? Are you able to import and retrieve ?
Yes I did. But I didn't want to have to log on the site every time, so I used IE to log on that site, browsed to a song detail page, I then saved the source code as an html file (right-click > view source > save as).
After that, in my test application, I navigated to that file instead of the actual web page.
I'm kind of busy right now. When I have time, I'll try to it your way and see why it's not working...
Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it. - Abraham Lincoln -
OK, that page is using iframes... And that explains why my original code didn't work when you tried it live (instead of from a saved html file as when I wrote it). Try this version now. It should work.
You need a form with 2 buttons, 1 webbrowser and 1 datagridview control on it. Then paste this code in.
Button1 will nagivate to the login page of the site. You then log in as normal, then browse to a song detail page. Once the song detail page loadded, you click button 2 to extract the song data. You will see it show up in the datagridview if successful.
Code:
Public Class Form2
Private songData As DataTable = Nothing
Public ReadOnly Property SongTable() As DataTable
Get
Return Me.songData
End Get
End Property
Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.WB1.ScriptErrorsSuppressed = True
Me.songData = New DataTable("SongData")
Dim primary As DataColumn
With songData.Columns
primary = .Add("SongNumber", GetType(Integer))
.Add("Title", GetType(String))
.Add("Authors", GetType(String))
.Add("Copyright", GetType(String))
.Add("Catalogs", GetType(String))
.Add("Administrators", GetType(String))
.Add("Key", GetType(String))
.Add("KeyLine", GetType(String))
.Add("AlsoKnownAs", GetType(String))
.Add("Themes", GetType(String))
.Add("Lyrics", GetType(String))
End With
Me.songData.PrimaryKey = New DataColumn() {primary}
Me.DataGridView1.DataSource = Me.songData
End Sub
'Naviagate to the login page
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
WB1.Navigate("web site address here")
End Sub
'Once logged in, you navigate to a song detail page, then click this
'button to extract the data
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If Me.WB1.IsBusy Then
MessageBox.Show("Browser is busy. Please try again later...")
Else
Dim ssframe As HtmlWindow = WB1.Document.Window.Frames("ssframe")
If Not ssframe Is Nothing Then
Dim sbLyrics As New System.Text.StringBuilder()
Dim rows As HtmlElementCollection = Nothing
Dim cells As HtmlElementCollection = Nothing
Dim detailTable As HtmlElement = ssframe.Document.GetElementById("details")
Dim lyricTable As HtmlElement = ssframe.Document.GetElementById("lyrics")
Dim songRow As DataRow = Me.songData.NewRow()
Dim songId As Integer = -1
'Get the lyrics
If Not lyricTable Is Nothing Then
Dim lyric As String = lyricTable.InnerText
songRow("Lyrics") = lyric
End If
'Get song details
If Not detailTable Is Nothing Then
rows = detailTable.GetElementsByTagName("tr")
If rows.Count >= 12 Then
Dim colName As String = String.Empty
Dim value As String = String.Empty
songRow("Title") = rows(0).InnerText
For i As Integer = 1 To 9
Try
colName = rows(i).Children(0).InnerText.Replace(" ", "")
value = rows(i).Children(1).InnerText
Select Case colName
Case "SongNumber"
songId = Integer.Parse(value)
songRow("SongNumber") = songId
Case "Author", "Authors"
songRow("Authors") = value
Case "Catalog", "Catalogs"
songRow("Catalogs") = value
Case "Administrator", "Administrators"
songRow("Administrators") = value
Case Else
songRow(colName) = value
End Select
Catch ex As Exception
Debug.WriteLine(ex.Message & ", " & ex.InnerException.Message)
End Try
Next
End If
End If
'Now add the row to our table if it's not there already
If songId >= 0 AndAlso Me.songData.Rows.Find(songId) Is Nothing Then
Me.songData.Rows.Add(songRow)
End If
Else
MessageBox.Show("Can't find the ssframe in the current document.")
End If
End If
End Sub
End Class
Last edited by stanav; Jul 18th, 2008 at 05:09 AM.
Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it. - Abraham Lincoln -
For some reason the code below was a little erratic and I had to comment out the debug.writeline which kept firing. It could also be because ssframe appears on most pages except login.
We may have to test for existence of ssframe and a button that exclusively appears on songpage... like ctl00_cp1_btnDownload. That'll ensure we're on the right page to import.
Code:
Case Else
songRow(colName) = value
End Select
Catch ex As Exception
'Debug.WriteLine(ex.Message & ", " & ex.InnerException.Message)
That said, when it works it captures almost all fields except : Author
Catalog
Administrator
which consistently get missed.
Any idea what might be wrong ?
Last edited by Xancholy; Jul 17th, 2008 at 10:22 PM.
Reply With Quote
Originally Posted by Xancholy
