XLRD Cell Value Reading Issue

General Discussion
1

I have excel table FEB2021 with two columns: Lot and Product Name.
I would like if the Lot is known (Tag " LOT"), automatically to draw the program name.
My code is:


pr = system.tag.readBlocking(["[default]GZPT_Room1/LOT"])[0].value
path = "C:\Applications\FEB2021.xlsx"

inputWorkbook = xlrd.open_workbook(path)
inputWorksheet = inputWorkbook.sheet_by_index(0)

for row in range (sheet.nrows):
	
	if sheet.cell (row,0)== pr:
		prodVal1 = sheet.cell (row,1)
		system.tag.writeBlocking(["[default]GZPT_Room1/Name1"],[prodVal1])```



Though no error message pop up, the script does not work. Any idea why?
2

You are using backslashes in string constants. Backslashes are escapes for special characters in python string constants, so you have to double them. Or you can usually get away forward slashes, even on Windows.

1 Like
3

I would also put in a print statement or two to see what you’re reading from the sheet.

4

No problem to access, read and print any cell, row, column.
Is it possible the reason to be the non specified cell format in the workbook?

5

There could also be some extra whitespace. Using repr() can help find those.

print repr(sheet.cell(row, 0))

EDIT: To further answer your question, there could als be a type difference (int vs. str, for example).

6

image
image260Ă—643 9.43 KB

this is the printout after adding "print repr(sheet.cell(row, 0))"

7

Ah. Okay, you should probably use sheet.cell_value() for individual cells. This should pare you down to just the unicode string.

EDIT: Looking through some of code used here, you may not have to change to cell_value. Read on for datatype matching, though.

Also, be sure that the value of the tag you’re reading is also a string, or converted to one at some point. The data types will need to match when you compare them.

8

If sheet.cell_value is used, I get properly the LOT numbers.

from xlrd import open_workbook, cellname
pr = system.tag.readBlocking(["[default]GZPT_Room1/LOT"])[0].value
path = "C:\Applications\FEB2021.xlsx"

inputWorkbook = xlrd.open_workbook(path)
inputWorksheet = inputWorkbook.sheet_by_index(0)
sheet = inputWorkbook.sheet_by_index(0)

for row in range (1,sheet.nrows):
	slicedVal = (sheet.cell_value(row,0))
	
	if slicedVal== pr:
		
		prodVal1 = sheet.cell (row,1)
		system.tag.writeBlocking(["[default]GZPT_Room1/Name1"],[prodVal1])```

The problem is how to compare slicedVal, type "unicode", and pr, type "long".
Conversion with ord or str(integer).decode("utf-8") did not help. Any hints?
9

A couple of options:

Casting pr to unicode would only have to happen once.

pr = unicode(system.tag.readBlocking(["[default]GZPT_Room1/LOT"])[0].value)

Casting slicedVal to long would need to happen on every iteration of the loop.

slicedVal = long(sheet.cell_value(row,0))
10

Both options end up with error:

image
if I replace the line “system.tag.writeBlocking([”[default]GZPT_Room1/Name1"],[prodVal1])" with “print pr, prodVal1”, the conversion works, it gives the lot number and product name paired properly.
However, why tag.readBlocking cannot be executed, no idea.

11

Fixed.
The correct code is:

from xlrd import open_workbook, cellname
pr = system.tag.readBlocking(["[default]GZPT_Room1/LOT"])[0].value
path = "C:\Applications\FEB2021_2.xlsx"

inputWorkbook = xlrd.open_workbook(path)
inputWorksheet = inputWorkbook.sheet_by_index(0)
sheet = inputWorkbook.sheet_by_index(0)

for row in range (1,sheet.nrows):
	
	slicedVal = long(sheet.cell_value(row,0))
	if slicedVal== pr:
		prodVal1 = sheet.cell_value(row,1)
		print pr, prodVal1
		system.tag.writeBlocking(["[default]GZPT_Room1/Name1"],[prodVal1])```

Thank you, Jordan!