Problem when reading data from an XLS file

Question

Hi!

I am new to powershell scripting. I have a task where I need to read the *.XLS file. After that I need to extract specific data. I have written a powershell script to read the XLS file easily. But unfortunately I can't extract specific data. Can someone please explain me where is the problem?

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("C:\Users\mm\Downloads\01_01_2023.XLS")
$worksheet = $workbook.Sheets.Item(1)
$usedRange = $worksheet.UsedRange
$data = $usedRange.Value2

$workbook.Close()
$excel.Quit()

# Release COM objects from memory
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($usedRange)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

$data
$data.Count

# Look for specific value
$search_value = "SUM"

for ($row = 0; $row -lt $data.Count; $row++) {
    $Cell_Value = $data[$row][0]
    $Cell_Value
    if ($Cell_Value -match "^$search_value") {
        $Row_Index = $row
        $Row_Index
        break
    }
}


Error:
The index [0] is invalid for an access to a 2-dimensional array. In C:\Users\mm\Downloads\Read XLS File.ps1:64 characters:5 + $Cell_Value = $data[$row][0] + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~     + CategoryInfo : InvalidOperation: (:) [], RuntimeException     + FullyQualifiedErrorId : NeedMultidimensionalIndex   The index [0] is invalid for accessing a 2-dimensional array.

Answer 1

Problem solved!