Problem when reading data from an XLS file

Shihab 0 Reputation points
2023-07-26T07:31:55.86+00:00

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.
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,272 questions
PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
1,845 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Shihab 0 Reputation points
    2023-07-26T11:21:16.2833333+00:00

    Problem solved!