locked
Html table into DataGrid RRS feed

  • Question

  • I'm using the HtmlAgilityPack to extract data from a html document.  I can get the data to display in console.Writeline however I need to get it into a DataGrid/DataTable so I can import it into SQL Server.  For the life of me I can't figure out how to get it into the DataGrid.

     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
     Dim ds As New DataSet
     Dim dt As DataTable
     Dim dr As DataRow
     Dim FileName As String = "C:\City.html"
     Dim doc As New HtmlAgilityPack.HtmlDocument()
     doc.Load(FileName)
     Dim tables As HtmlNodeCollection = doc.DocumentNode.SelectNodes("/html[1]/body[1]/table[1]/tr[4]/td[1]/table[1]/tr[2]/td[3]/placeholder[1]/table[1]/tr[1]/td[1]/table[1]/tr[2]/td[2]/table[1]/tr[1]/td[1]/table[1]/tr[2]/td[1]/div[1]/table[3]/tr[1]/td[1]/table[1]")
     Dim rows As HtmlNodeCollection = tables(0).SelectNodes("./tr")
     For i As Integer = 0 To rows.Count - 1
     Dim cols As HtmlNodeCollection = rows(i).SelectNodes("./td")
     For j As Integer = 0 To cols.Count - 1
     Dim value As String = cols(j).InnerText
     Console.WriteLine(value)
     Next
    Next
     dt = ds.Tables("City")
    End Sub
    

    This is what I'm getting displayed in the Output:

    RecordTime Gen4 Gen5 Gen6 Gen7 Gross Generation House1 House2 House3 House4 House5 House6 Station Service Net Tie City Load 

    5/20/2010 1:20:37 PM000242762427644.454.609774.442925.232696.926.5193772.2205041605836562

    5/20/2010 2:20:37 PM000258442584440.1295.11262.299913.15270226.5263749.2220951485036945

    5/20/2010 3:20:37 PM000277262772639.5835.744586.475968.742650.626.3493777.5239491192435872

    Any assistance would be greatly welcomed.

    Thursday, July 8, 2010 3:34 PM

Answers

  • Then HTML is simply a string like this string,

    You can handle that with a Nvarchar field on SQL side and on VB Side as string.

    Simply follow any sample you get for AdoNet with a DataSet or with Linq to SQL creating sample 10000001 in this forum for that general purpose seems to me only confusing for others who would see this thread later.

     


    Success
    Cor
    Friday, July 9, 2010 5:47 AM

All replies

  • Both the DataGrid and the DataGridView have as far as I know now columns which can contain HTML which is showed in another way than like a string containing the tags.

    I assume that is not your goal.

     


    Success
    Cor
    Thursday, July 8, 2010 5:39 PM
  • I need to get the data from the columns in the html table into a SQL Table.  I've had experience getting data from a datagrid/datatable/dataset and importing them into a SQL Table.

    I have unfortunately no experience trying to get the output of an html table into a datagrid/datatable/dataset.  As I stated I can get the required output to print in Console.writeline, so I know I'm grabbing the data, I just don't know how to get it to where i want.

    Any suggestions would be greatly welcomed.

    Thursday, July 8, 2010 6:03 PM
  • Then HTML is simply a string like this string,

    You can handle that with a Nvarchar field on SQL side and on VB Side as string.

    Simply follow any sample you get for AdoNet with a DataSet or with Linq to SQL creating sample 10000001 in this forum for that general purpose seems to me only confusing for others who would see this thread later.

     


    Success
    Cor
    Friday, July 9, 2010 5:47 AM
  • Hi, you can try this VB.NET Excel component which has a really simple API for HTML file data export.

    Here is how you can export HTML to DataTable:

        Dim FileName As String = "C:\City.html"
    
        ' Load HTML file.
        Dim htmlFile = ExcelFile.Load(FileName, LoadOptions.HtmlDefault)
    
        ' Extract the data from the HTML file to a DataTable starting at 
        ' first row and first column until the first empty row appears.
        Dim dt = htmlFile.Worksheets.ActiveWorksheet.CreateDataTable(New CreateDataTableOptions() With
            {
                .StartRow = 0,
                .StartColumn = 0,
                .ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow
            })

    Monday, July 8, 2013 11:06 AM