copying data from webpage and pasting to ms access

Welcome Forums General PowerShell Q&A copying data from webpage and pasting to ms access

This topic contains 1 reply, has 2 voices, and was last updated by

 
Participant
2 years, 10 months ago.

  • Author
    Posts
  • #38982

    Participant
    Points: 1
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

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

The topic ‘copying data from webpage and pasting to ms access’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort