Problem solved!
Problem when reading data from an XLS file
Shihab
0
Reputation points
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.