copying data from webpage and pasting to ms access

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Alex Alex 6 months, 4 weeks ago.

  • Author
    Posts
  • #38982
    Profile photo of amit aman
    amit aman
    Participant

    Hi Team,

    I am trying to pull the specific content of a webpage and rewrite it to MS Access 2010 table. I am using following script but not getting the desired result.

    Function Check-Path($Database)
    {
    If (!(Test-Path -path (Split-Path -path $Database -parent)))
    {
    Throw "$(Split-Path -path $Database -parent) Does not Exist"
    }
    ELSE
    {
    If (!(Test-Path -Path $Database))
    {
    Throw "$Database does not exist"
    }
    }
    } #End Check-Path

    $SRU_WebPage = "https://support.sourcefire.com/supplemental/sf-rules-2016-05-05-seu.html"

    if (-not $SRU_WebPage)
    {
    $SRU_WebPage = Read-Host -Prompt "Please enter the SRU Link"
    }

    $ie = New-Object -ComObject "InternetExplorer.Application"
    $ie.Navigate("$SRU_WebPage")
    #$ie.Navigate("https://support.sourcefire.com/supplemental/sf-rules-2016-05-05-seu.html")

    #Write-Host "Loading Page"
    #while ($ie.Busy)
    #{
    # sleep -Seconds 10
    #}

    Write-Host "Processing Page"
    $SRUNumber = (($ie.Document.body.innerText.Split("`n") | Select-String "This SRU Number: (.*)").Matches.Groups[1].Value).ToString().Trim()
    $SEUNumber = (($ie.Document.body.innerText.Split("`n") | Select-String "This SEU Number: (.*)").Matches.Groups[1].Value).ToString().Trim()

    $PageSource = $ie.Document.body.innerHTML
    $PageSource = $PageSource.Split("`n")

    Function Connect-Database($Database, $Tables)
    {
    $adOpenStatic = 3
    $adLockOptimistic = 3

    $conn = New-Object -com "ADODB.Connection"
    $rs = New-Object -com "ADODB.Recordset"
    $conn.Open('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$Database;Persist Security Info=True;')

    $Row = 2
    $PageSource | % {
    if ($_ -match "(\d+)(\d+)(.*)(.*)(off|drop)(off|drop)(off|drop)")
    {
    $rs.AddNew("SELECT * FROM $Tables", $conn, $adOpenStatic, $adLockOptimistic)
    $rs.Fields.Item("SRU").value = $SRUNumber
    $rs.Fields.Item("SEU").value = $SEUNumber
    $rs.Fields.Item("GID").value = $Matches[1]
    $rs.Fields.Item("SID").value = $Matches[2]
    $rs.Fields.Item("Rule_Group").value = $Matches[3]
    $rs.Update()

    $conn.Close
    $rs.Close
    $Row++
    $Row
    }
    }
    }

    # *** Entry Point to Script ***

    $Database = & "C:\Users\am288711\Documents\SRU_Database.accdb"
    $Tables = "tbl_Rules"
    Check-Path -db $Database
    Connect-DataBase -db $Database -tables $Tables

    #Kill the excel process
    Get-Process | where {$_.Name -like "MSAccess*"} | % {
    $Id = $_.id
    if ((Get-WmiObject Win32_Process -Filter "ProcessID = '$id'" | select CommandLine) -like "*embed*")
    {
    Stop-Process -Id $id
    }
    }

    Kindly Help.

    I sorted it a little bit.

    $URL = "https://support.sourcefire.com/supplemental/sf-rules-2015-05-12-seu.html"
    $OutputFile = "$env:temp\tempfile.html"

    $data = Invoke-WebRequest -Uri $URL

    @($data.ParsedHtml.getElementsByTagName("table"))[0].OuterHTML | Set-Content -Path $OutputFile

    $Excel = @(Resolve-Path "C:\Program Files*\Microsoft Office\Office*\MSACCESS.EXE")[0].Path
    & $Excel $OutputFile

    It is working fine when I am saving it with EXCEL.EXE instead of MSACCESS.EXE.
    When it opens a fresh ACCESS page, it is terminating with the error that "Microsoft Access does not support opening HTML pages"
    Is there any way round to work out this situation?

  • #38985
    Profile photo of Alex
    Alex
    Participant

    What error are you getting? Could you be more specific?

You must be logged in to reply to this topic.