[Get-Content], OutOfMemoryException

[Alukard-X]

Hi! I'm trying to load a big (~1.5GB) html file in old "excel 2003 xmlns" format to xlsx w/o Excel.
For "smaller" files that works ok. For large files I'm getting [Get-Content], OutOfMemoryException.
Is there anyway to solve that?

function Get-HtmlTable {
param(
[Parameter(Mandatory=$true)]
$Url,
$TableIndex=0,
$Header,
[int]$FirstDataRow=0,
[Switch]$UseDefaultCredentials
)

$r = New-Object -ComObject "HTMLFile"
$r.IHTMLDocument2_write($(Get-Content $Url -raw))
#$r.body.OuterHtml
#$r.getElementsByTagName("table")

$propertyNames = $Header

  #  $table    = $r.body.OuterHtml
    $table = $r.getElementsByTagName("table")[$TableIndex]
    
    $totalRows=@($table.rows).count

    for ($idx = $FirstDataRow; $idx -lt $totalRows; $idx++) {

        $row = $table.rows[$idx]
        $cells = @($row.cells)
       # $cells[0]
        if(!$propertyNames) {
            if($cells[0].tagName -eq 'td') {
                $propertyNames = @($cells | ForEach-Object {$_.innertext})
            } else  {
                $propertyNames =  @(1..($cells.Count + 2) | Foreach-Object { "P$_" })
            }
            continue
        }

        $result = [ordered]@{}

        for($counter = 0; $counter -lt $cells.Count; $counter++) {
            $propertyName = $propertyNames[$counter]

            if(!$propertyName) { $propertyName= '[missing]'}
            $result.$propertyName= $cells[$counter].InnerText
        }

        [PSCustomObject]$result
    }

}

function Import-Html {
[CmdletBinding()]
param(
$Url,
[int]$Index = 0,
$Header,
[int]$FirstDataRow = 0,
[Switch]$UseDefaultCredentials
)

$xlFile = [System.IO.Path]::GetTempFileName() -replace "tmp", "xlsx"
Remove-Item $xlFile -ErrorAction Ignore

Write-Verbose "Exporting to Excel file $($xlFile)"

$data = Get-HtmlTable -Url $Url -TableIndex $Index -Header $Header -FirstDataRow $FirstDataRow -UseDefaultCredentials: $UseDefaultCredentials

$data | Export-Excel $xlFile -Show -AutoSize -BoldTopRow -AutoFilter -FreezeTopRow

}
Import-Html $args[0] 0

[Alukard-X]

trimmed by sed

[dfinke]

Not sure. Can Get-Content read from a URL? Never tried to read a 1.5GB file with that function, I would think it would be to slow at minimum.

I would've start with [System.IO.File]::ReadAllText

[Alukard-X]