How to break out of nested loops in Python when searching for a specific value in an Excel file?

Question

I have an Excel file with a list of shops and their addresses. I want to search it by shop number and save the address to a file. My problem is that I am a newb and I don't know how to make the loop stop and re-run input('Enter shop number: ') when the shop number doesn't exist in my Excel file. Currently, my script prints 'Invalid shop number!' for every row. Can someone help me? I know that it's probably something basic, but as I said, I am a newbie... Thanks in advance!

import openpyxl
from openpyxl import Workbook

file = '...\\Desktop\\shops.xlsx'
wb = openpyxl.load_workbook(file, read_only=True)
ws = wb.active

shop = int(input('Enter shop number: '))

save = open('shop.txt', 'w')

for row in ws.iter_rows(1):
    for cell in row:
        if cell.value == shop:
            print(ws.cell(row=cell.row, column=2).value, file = save)
            save.close()
        else:
            print('Invalid shop number!')

Please can you show us a screenshot of the data headers for context? — Plato77, Jan 10 '20 at 16:08

score -1 · Answer 1 · answered Jan 10 '20 at 16:47

You could have a boolean flag to indicate if the shop number was found or not:

found = False
for row in ws.iter_rows(1):
    for cell in row:
        if cell.value == shop:
            print(ws.cell(row=cell.row, column=2).value, file = save)
            save.close()
            found = True
if not found:
    print('Invalid shop number!')

If you want to only handle the first cell found with the shop number or there are no duplicate shop numbers, you can put this in a function and return after the shop number is found.

Alternatively, you can break in the if statement and break again after the inner loop if the flag is True or continue in an else clause after the inner loop and break after that clause.

See the answers to this related question about how to break out of multiple loops.

score -1 · Answer 2 · answered Jan 10 '20 at 17:16

-1

This works! Thank you all for help!

save = open('shop.txt', 'w')    
while not found:
    shop = int(input('Enter shop number: '))
    for row in ws.iter_rows(1):
        for cell in row:
            if cell.value == shop:
               print(ws.cell(row=cell.row, column=2).value, file = save)
               save.close()
               found = true
               break
    if not found:
        print('Invalid shop number!')

answered Jan 10 '20 at 17:16

zhadox

1
3

Again, `found` isn't initialized here, and lowercase `true` is a `NameError`. This also only breaks out of the innermost loop and will continue to check each row after the first instance of the shop number has been found. – Harmon758 Jan 10 '20 at 17:23

PraveenB · Accepted Answer · 2020-01-13T15:36:29.457

-2

You have to put the logic inside while loop where you are taking the input and break from while loop when you find the shop address in file or based on your requirements.

save = open('shop.txt', 'w')    
while not found:
    shop = int(input('Enter shop number: '))
    for row in ws.iter_rows(1):
        for cell in row:
            if cell.value == shop:
               print(ws.cell(row=cell.row, column=2).value, file = save)
               save.close()
               found = True
               # or you can write
               # break
        if found:
            break
    if not found:
        print('Invalid shop number!')

edited Jan 13 '20 at 15:36

answered Jan 10 '20 at 16:55

PraveenB

1,270
10
11

`found` isn't initialized, and lowercase `true` is a `NameError`. Also, this prints `'Invalid shop number!'` for every row before the shop number is found, and a single `break` will only break out of the innermost loop. – Harmon758 Jan 10 '20 at 17:00
i didnt run it and just provided the solution to give direction to OP. anyways have made edits based on your point – PraveenB Jan 10 '20 at 17:50
The semicolon is unnecessary. Also not sure why you downvoted my answer, which outlined the method you're using along with debatably better alternatives and a reference, but ok. – Harmon758 Jan 10 '20 at 20:14
Not sure why do you think like that. but now if i see your solution first of all you are not addressing the OP problem to repeatedly ask for input. As i already mentioned i didn't run it and typed the solution in haste just to give an idea. Anyways looks like OP has resolved it based on idea we provided. – PraveenB Jan 13 '20 at 15:49

How to break out of nested loops in Python when searching for a specific value in an Excel file?

3 Answers3