TypeError: '<' not supported between instances of 'str' and 'int'

EagerToLearn · 8 February 2024 16:01

Hi Mentors, I have one question.
I am trying to read an excel file using the openpyxl
import openpyxl

class ExcelUtils:
@staticmethod
def get_row_count(file, sheetName):
workbook = openpyxl.load_workbook(file)
sheet = workbook[sheetName]
return sheet.max_row

@staticmethod
def get_column_count(file, sheetName):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook[sheetName]
    return sheet.max_column

@staticmethod
def read_data(file, sheetName, rowNum, columnNum):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook[sheetName]
    values = str(sheet.cell(row=rowNum, column=columnNum).value)
    return values

@staticmethod
def write_data(file, sheetName, rowNum, columnNum, data):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook[sheetName]
    sheet.cell(row=rowNum, column=columnNum).value = data
    workbook.save(file)

pathToTestDataFile = “D://LEARNING//MY_LEARNING//ROBOTFRAMEWORK_PYCHARM//ROBOTFRAMEWORK//nopCommerceApp//TestData//ALL_DEFAULT_CATEGORIES.xlsx”
for i in range(1, 16):
excelData = ExcelUtils.read_data(pathToTestDataFile, “Sheet1”, i, 1)
print(excelData)
then I am trying to use it in a robot framework file

Loop Through Excel File And Find Given Value
[Arguments] ${PathToExcelFile} ${NameOfExcelSheet} ${HTML_Table_Web_Element}
${RowCountInExcel}= Run Keyword ExcelUtils.Get Row Count ${PathToExcelFile} ${NameOfExcelSheet}

@{ExcelCategoriesValues}    Create List
FOR    ${counter}    IN RANGE    1    ${RowCountInExcel}
    ${ValueFromExcel}=    Run Keyword    ExcelUtils.Read Data    ${PathToExcelFile}      ${NameOfExcelSheet}     ${counter}      1
    Log    ValueFromExcel: ${ValueFromExcel}
    Append To List    ${ExcelCategoriesValues}      ${ValueFromExcel}
END

When I run it I am constantly getting this exception.

TypeError: ‘<’ not supported between instances of ‘str’ and ‘int’

Can you please guide me?
Sincere Regards
EagerToLearn

_daryl · 8 February 2024 17:14

Hi

Its hard to know if it throws in your Python file or the robot file, but as a first check, just on whats above alone.

Some debug ideas for you:
→ You could throw a try except around the code within def read_data(...) and, on exception, print the rowNum and columnNum. At least it will give you some indication of where the problem is. Its possible one of the cells holds some dud data, a line break maybe, or something (Ive seen similar with data from Excel before but cannot recall the exception it had thrown), but visually inspecting the document should also be a good way of checking, but you would have to click into the cells to check.
→ Same as above with the FOR block snippet in your .robot, wrap a TRY/EXCEPT around this line, and see what row count is thrown at:
${ValueFromExcel}= Run Keyword ExcelUtils.Read Data ${PathToExcelFile} ${NameOfExcelSheet} ${counter} 1

Providing the full console log could be helpful’er or the robot log, but feels like a read data issue, but I could be wrong and it could be something else…

You may also find (given we’ve no idea on your sheet data) that the cell values could be a formula, which visually you’d never tell without clicking into them, or unless you manage the sheet yourself, which could possibly be why, provided this is thrown at the data read.

I hope this helps

Thanks.

_daryl · 8 February 2024 19:13

Just in case, if its formula-related, you can set: data_only ignores loading formulas and instead loads only the resulting values.

EagerToLearn · 9 February 2024 06:20

Hi @_daryl So nice of you It worked. The excel practically just consists of one column and idea is to get all the values from the column and compare it with one column (read as a list in robot framework) and then comparison is done using python.

In Robot Framework I do it like this:

This file is the test case runner:

*** Settings ***
Resource    ../../LoginAsAdmin.robot
Resource    ../../Logout.robot
Resource    NavigateToCategoriesPage.robot
Resource    ../../CommonTests/CommonKeywords.robot
Library    ../lib/ExcelUtils.py

Test Setup      Create Setup to Run Tests    https://admin-demo.nopcommerce.com/login    Firefox
#Test Teardown   Close Web Browser

*** Variables ***
${PathToExcelFile}=                 D://LEARNING//MY_LEARNING//ROBOTFRAMEWORK_PYCHARM//ROBOTFRAMEWORK//nopCommerceApp//TestData//ALL_DEFAULT_CATEGORIES.xlsx
${NameOfExcelSheet}=                Sheet1
${HTML_Table_Web_Element}=          xpath=//*[@id='categories-grid']
                                    #/tbody/tr[${row_count}]/td[1]

# xpath=//*[@id=class="table table-bordered table-hover table-striped dataTable no-footer"]/thead/tr[${row_count}]/td[${row_count}]

*** Test Cases ***

Find Given Category In Given Excel File
    Login as Admin
    Navigate to Categories Page
    Set Id Categories Page Search Box    C
    Sleep    3s
    Click Search Categories Button
    Sleep    2s
    Click Search Categories Button
    Sleep    10s
    Page Should Contain     1-7 of 7 items
    Sleep    3s
    Loop Through Excel File And Find Given Value    ${PathToExcelFile}    ${NameOfExcelSheet}    ${HTML_Table_Web_Element}
    Sleep    10s

********************* CommonKeywords.robot ******************************

*** Settings ***
Library     SeleniumLibrary
Library    ../lib/ExcelUtils.py
Library    Collections
Library     ExcelLibrary

*** Keywords ***
    Loop Through Excel File And Find Given Value
        [Arguments]     ${PathToExcelFile}      ${NameOfExcelSheet}     ${HTML_Table_Web_Element}
        ${RowCountInExcel}=     Run Keyword    ExcelUtils.Get Row Count    ${PathToExcelFile}      ${NameOfExcelSheet}
        Log    Total number of Rows in Excel: ${RowCountInExcel}    console=true
        #     def read_data(file, sheetName, rowNum, columnNum):
        ${ValueFromExcel} =     Set Variable    ""
        @{ExcelCategoriesValues}    Create List
        FOR    ${counter}    IN RANGE    1    ${RowCountInExcel}
            TRY
               ${ValueFromExcel}=       Run Keyword     ExcelUtils.Read Data    ${PathToExcelFile}      ${NameOfExcelSheet}     ${counter}      1
            EXCEPT    "TypeError":
                Log     ValueFromExcel: ${ValueFromExcel}
            END
            Log    ValueFromExcel: ${ValueFromExcel}
            Append To List    ${ExcelCategoriesValues}      ${ValueFromExcel}
        END

        @{RESULT}=                                Create List
        @{HTML_DATA}=                       Create List
        Log    Here the html data is printed:   console=true
            # FILL THE HTML DATA LIST
            FOR    ${row_count}    IN RANGE     1      16
                IF    ${row_count} == 8
                     BREAK
                END
                ${html_table_data}=          Get Text       xpath=//*[@id='categories-grid']/tbody/tr[${row_count}]/td[2]

                Append To List    ${HTML_DATA}      ${html_table_data}
                #Log To Console    "--------------------------START: HTML_DATA_LIST------------------------------------"
                Log    html_table_data:${html_table_data}    console=true
            END
            #Log To Console    "--------------------------------END: HTML_DATA_LIST------------------------------"
            ${HTML_TABLE_ROW_COUNT}=    Get Length    ${HTML_DATA}
            ${HTML_TABLE_ROW_COUNT}     Evaluate    ${HTML_TABLE_ROW_COUNT}-1
            ${Excel_Row_Count}=      Get Length    ${ExcelCategoriesValues}
            ${iterationCount}=       Set variable    0
            Log To Console    Excel_Row_Count: ${Excel_Row_Count}
            Log To Console    iterationCount : ${iterationCount}
            #    def find_string_in_list(self, excel_table_as_list, html_table_as_list):
            @{RESULT}       Create List
            @{RESULT}=      Run Keyword     ExcelUtils.Find String In List      ${ExcelCategoriesValues}     ${HTML_DATA}

            Get Length    item
        FOR    ${element}    IN    ${RESULT}
            
            Log    Final Result: ${element}    console=true
            IF    ${element} == "true"
                Log    PASS     console=true
            ELSE
                Log    Fail     console=true
            END
        END

In python side:

@staticmethod
def find_string_in_list(excel_table_as_list, html_table_as_list):
#def find_string_in_list():
resultList =
counter = 0

    #html_manual_list = ['Electronics', 'Electronics >> Camera & photo', 'Electronics >> Cell phones', 'Computers','Gift Cards', 'Apparel >> Clothing', 'Apparel >> Accessories']
    #excel_manual_list = ['Computers', 'Computers >> Desktops', 'Computers >> Notebooks', 'Computers >> Software','Electronics', 'Electronics >> Camera & photo', 'Electronics >> Cell phones','Electronics >> Others', 'Apparel', 'Apparel >> Shoes', 'Apparel >> Clothing', 'Apparel >> Accessories', 'Digital downloads', 'Books', 'Jewelry', 'Gift Cards']

    excel_table_as_list_here = list(excel_table_as_list)
    html_table_as_list_here = list(html_table_as_list)

    #excel_table_as_list_here = list(html_manual_list)
    #html_table_as_list_here = list(excel_manual_list)

    for html_data in html_table_as_list_here:
        print("html_data: ", html_data)
        if html_data in excel_table_as_list_here:
            resultList.append("true")
        elif html_data not in html_table_as_list_here and counter >= len(html_table_as_list_here):
            resultList.append("false")
            break
    counter = counter + 1
    return resultList

ResultList = ExcelUtils.find_string_in_list()

print(ResultList)

for i in range(len(ResultList)):

print(ResultList[i])

I would greatly appreciate you suggesting better approach to this code

One more question, do you know in pycharm how I can get the output from the python side getting printed (using print statement for example) when I am launching the robot framework code
using robot .\Categories_Page_Test_Runner.robot which then further utilizes the code from the python ExcelUtils.py file but no print statement output is seen such as
print(f"values: {values}")

Excel file is just one column:

HTML table looks like:

Sincere Regards
EagerToLearn

orenault · 9 February 2024 12:21

Hi!
This error is because you are comparing a string with another kind of instance.
So forcing data_only seems a good way to resolve this issue as you force to get strings.

But I dot nor understand why creating specific python code, as there are many Robot Framework libraries to handle Excel files. like robotframework-excellib:

https://rawgit.com/peterservice-rnd/robotframework-excellib/master/docs/ExcelLibrary.html

Maybe you can give it a try.
Regards
Olivier

Topic		Replies	Views
Unable to read data from Excel file variable not found Robot Framework	4	967	28 December 2023
Error Reading Variables from Excel file Libraries	2	802	10 February 2023
Subject: Issue with Retrieving Data from Specific Excel Cell in Robot Framework Robot Framework	3	199	9 February 2024
Opening a Spefic Excel's Sheet using RobotFramework Robot Framework	2	3577	19 November 2020
Unable to read .xlsx file using excelrobot library Libraries	0	465	26 June 2023

TypeError: '<' not supported between instances of 'str' and 'int'

ResultList = ExcelUtils.find_string_in_list()

print(ResultList)

for i in range(len(ResultList)):

print(ResultList[i])

Related topics