Recognizing patterns from TXT Powershell

0

I'm trying to append TXT to XLSX based on patterns. If it matches it should append. It works when I define the sheet columns and items explicitly.

This is what I tried so far. I get the following error: HRESULT: 0x800A03EC. What's going wrong here?

$fileContent = @'
Node: ABC
Name: PC
Cluster: HS1
Node: XZZ
Name: CC
Cluster: HS2
Node: XYZ
Name: DD
Cluster: HS3
'@
$Testbestand = $fileContent.Split( [System.Environment]::NewLine,
          [System.StringSplitOptions]::RemoveEmptyEntries )

$linieIdLast = ''
$linieTemplate = [ordered]@{}
foreach ( $linie in $Testbestand ) {
    $linieId, $linieVal = $linie -split ":\s*",2
    if ( $linieId -in $linieTemplate.Keys ) {
        break
    } else {
        $linieIdLast = $linieId
        $linieTemplate.Add( $linieId, $Null )
    }
}

$linieComb = New-Object -TypeName PSCustomObject -Property $linieTemplate
$liniesAll = foreach ( $linie in $Testbestand ) {
    $linieId, $linieVal = $linie -split ":\s*",2
    $linieComb.$linieId = $linieVal.Trim()
    if ( $linieId -eq $linieIdLast ) {
        $linieComb
        $linieComb = New-Object -TypeName PSCustomObject -Property $linieTemplate
    }
}

$TargetFile = Targetfile
$sheetName    = Sheet
$objExcel     = New-Object -ComObject Excel.Application
$workBook     = $objExcel.Workbooks.Open($TargetFile)
$sheet    = $workBook.sheets.item($sheetName)
$row = 33     

    foreach ( $linieKey in $linieTemplate.Keys ) 
    {
       $column = ($sheet.Columns.Find($linieKey))
       $sheet.cells.item($row, $column).value = $linieTemplate.$linieKey
       $row++
    }

$workBook.Save()
$objExcel.Quit() 

The error I get:

Type mismatch. (HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
At line:47 char:8
+        $sheet.cells.item(1, $column).value = $linieTemplate.$linieKey
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
excel
powershell
design-patterns
append
xlsx
asked on Stack Overflow May 13, 2020 by Sbirq

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0