why is the switch statement not working correctly

Question

### created arrays
$cell = [System.Collections.ArrayList]@()
$filepath = [System.Collections.ArrayList]@()
$index = $cell.IndexOf($_)
### this code pulls values from excel spread sheet as well as that files name and path 
Get-ChildItem C:\UserS\chaos\OneDrive\Documents\working\srs\dynamic*  | ForEach-Object {
    $xl = New-Object -ComObject excel.application
    $xl.Visible = $false
    $woorkbookactive = $xl.Workbooks.Open($_.FullName)
    $woorksheetactive = $woorkbookactive.Worksheets("Sheet1")
    $RANGE = $woorksheetactive.Range("A4")
    $cell.Add($RANGE.Value())
    $filepath.Add($_.FullName)
    $xl.Quit()
}
### the Above code produces these values
Selected Criteria: Enrolment Status: Left                                   
Selected Criteria: Enrolment Status: Active                                     
Selected Criteria: Enrolment Status: Active Permission Type: RESOURCE SCHEME    
###

$cell
Start-Sleep -Seconds 2
switch -exact ($cell)
{
 'Selected Criteria: Enrolment Status: Active  Permission Type: RESOURCE SCHEME'{Write-Host "Found RS";Write-Host $index};#Rename-Item -Path $filepath[$index] -NewName "DynamicRS.xlsx";continue}
 'Selected Criteria: Enrolment Status: Left'{Write-Host "Found Left";Write-Host $index};#Rename-Item -Path $filepath[$index] -NewName "DynamicLeft.xlsx";continue}
 'Selected Criteria: Enrolment Status: Active' {Write-Host "Found Active";Write-Host $index};#Rename-Item -Path $filepath[$index] -NewName "DynamicActive.xlsx";continue} 
default{write-host "no match found"}
}

I have tried with both regex and if statements however the values keep saying no match even though I can clearly see the matches

the output i received was this

no match found
no match found
no match found

I was expecting this

Found RS
found Left
Found Active

and sometimes it does detect them however the output it classes was wrong for example RS become Left and active Become Left.

Are those spaces at the end of the values actually part of them? Try `switch -exact ($cell.Trim()) ...` — jkiiski, Nov 11 '22 at 13:50
@iRon, that would convert the entire array list to a single string, which I don't think is the intent. — mklement0, Nov 11 '22 at 14:32
Aside from what @jkiiski points out, there's no obvious problem with your code. If `.Trim()` doesn't fix your problem, please try to provide a [mcve]. — mklement0, Nov 11 '22 at 14:38
You also need to declare the `$xl` object only once, above the loop. Then after the loop you do `$xl.Quit()` and release the COM objects you have used: `$woorksheetactive`, `$woorkbookactive` and `$xl`. Inside the loop is is advisable to close the workbook once you have taken the value you need with `$woorkbookactive.Close()`. The way your code now works keeps claiming memory and never frees that so eventually you will run into resource problems. — Theo, Nov 12 '22 at 12:00

score 0 · Answer 1 · answered Nov 11 '22 at 15:59

0

I think you want Break vs Continue at the end of your switch cases.

PS> $CellValues = @(
                'Selected Criteria: Enrolment Status: Active Permission Type: RESOURCE SCHEME'
                'Selected Criteria: Enrolment Status: Left',
                'Selected Criteria: Enrolment Status: Active'                                    
                )

ForEach ($cell in $CellValues) {

switch -exact ($cell)
{
 'Selected Criteria: Enrolment Status: Active Permission Type: RESOURCE SCHEME' {
                                                Write-Host "Found RS"     
                                                Break}
 'Selected Criteria: Enrolment Status: Left'   {Write-Host "Found Left"
                                                Break}
 'Selected Criteria: Enrolment Status: Active' {Write-Host "Found Active"
                                                Break} 
  default{write-host "no match found"}
}

} #End ForEach

Found RS
Found Left
Found Active

PS>

P.S. There is an extra space in the first select between Active and Permission.

answered Nov 11 '22 at 15:59

RetiredGeek

2,980
1
7
21

While using `break` is good practice and avoids unnecessary processing, it cannot be expected to make a difference here, given that all the branch conditions use distinct strings and exact matching is used. – mklement0 Nov 11 '22 at 16:06
@RetiredGeek your code works when passing the strings directly in the array however when i try to pull them form the excel spreadsheets before adding them to the array list the same problem occurs and I cant seem to see any reason for this happening – chaosblade201 Nov 12 '22 at 01:11
For debugging try adding a $Cell.Length before the sleep command and lengthen the sleep time so you can manually count the characters & spaces on the display and match to the value provided by the Length property. – RetiredGeek Nov 12 '22 at 19:07
1

Thanks for the help, everyone it was just the annoying hidden control characters where present in the string i was trying to match against – chaosblade201 Nov 14 '22 at 22:01

score 0 · Answer 2 · answered Nov 17 '22 at 03:45

To bring closure to this question:

There was nothing wrong with your switch statement per se (although its efficiency could be improved - see next section).

As you state, the real problem was (emphasis added):

it was just the annoying hidden control characters were present in the string i was trying to match against

This answer shows how you can visualize hidden control characters in strings.

As an aside:

It is good practice use continue or break in a given switch statement's branch script block (other than the default block) in order to prevent unnecessary evaluation of later branches / additional inputs .

With a single input to a switch statement, continue and break equally stop processing, but with an array (collection) as input, their behavior differs: continue stops processing of the current array element only and continues with the next one, whereas break exits the switch statement instantly.

The following examples illustrate the difference:

# SINGLE input
# -> 'found!'
# Without `break`, 'never get here' would be output too.
# `continue` in lieu of `break` works too.
switch -Regex ('one') {
  '.ne' { 'found!'; break }
  'on.' { 'never get here' }
}

# ARRAY (COLLECTION) input
# -> 1, 2
# `continue` moves to the next array element, `break` exits the whole statement.
switch -Regex ('one', 'two', 'three') {
  '.ne' { 1; continue } # move to next element
  'two' { 2; break } # stop processing of the whole statement
  'on.' { 'never get here' }
  'three' { 'never get here' }
}

why is the switch statement not working correctly

2 Answers2