Author Posts

May 12, 2016 at 2:52 am

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?

May 12, 2016 at 3:18 am

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