Issue with Excel column values read

Ruby
#1

Hi,

I stuck in a point in my code. I have an excel which contains values as
below :

Tax code

wst-45
tre-2%
12.45%
10
5
ascht5

When my code is trying to read the values only the bad data is coming
from the values like 5,10… They are being converted to 10.0,5.0. Which
causes my code to fail,as in the select list no such values like 10.0 or
5.0. I tried to fix it by formatting the Excel column as “Text”,but
doesn’t work.

CODE:

if wbs.cells(rows,8).value != nil

elem = driver.find_element(:name, “defaultAnswerId”)
option = Selenium::WebDriver::Support::Select.new(elem)
puts wbs.cells(rows,8).value
default_val = wbs.cells(rows,8).value.strip
option.select_by(:text, default_val)

end

any idea how to fix that.

#2

If you already know it has to be an integer, use “.to_i”, or “Integer()”
in the appropriate places.

#3

Joel P. wrote in post #1096311:

If you already know it has to be an integer, use “.to_i”, or “Integer()”
in the appropriate places.

No! @Joel - there can be anything - that’s the problem. So I am looking
for a way to fetch the value from the Excel cells as it is,no conversion
during fetching.

#4

If you’re using the spreadsheet gem (which I am for one script), then
you’ll
have to use .to_i since values are always brought in as real numbers. If
the
issue is that you may have a nil value, just do this:

excel_value.to_i if excel_value.!nil?

Wayne

Joel P. wrote in post #1096311:

If you already know it has to be an integer, use “.to_i”, or “Integer()”
in the appropriate places.

No! @Joel - there can be anything - that’s the problem. So I am looking
for a way to fetch the value from the Excel cells as it is,no conversion
during fetching.

#5

Wayne B. wrote in post #1096314:

If you’re using the spreadsheet gem (which I am for one script), then
you’ll
have to use .to_i since values are always brought in as real numbers. If
the
issue is that you may have a nil value, just do this:

excel_value.to_i if excel_value.!nil?

Wayne

This is the column as an example :

Tax code

wst-45
tre-2%
12.45%
10
5
ascht5

where different kind of values can be present. And I am using
“win32ole”. So how could I use the “to_i”?

#6

unknown wrote in post #1096322:

Am 11.02.2013 16:40, schrieb Love U Ruby:

tre-2%

end

any idea how to fix that.

Not without the information where the `wbs’ object came from.

I have provided the code, which is the most victim, so the full code is
doing lot’s of operations. So that would confuse the whole pain area.
And I have told that I am using “win32ole”. wbs is the object referring
to the excel work sheet.

Thanks.

#7

Am 11.02.2013 16:40, schrieb Love U Ruby:

tre-2%

end

any idea how to fix that.

Not without the information where the `wbs’ object came from.

Also, if your problem is not related to Selenium then most of
your provided code seems not to be relevant here.

Please provide a code snippet as self-contained and to the point
as possible.

#8

Love U Ruby wrote in post #1096307:

in the select list no such values like 10.0 or 5.0

You know where to change the value to an integer. Just before you select
in on a list which takes integers!

#9

Joel P. wrote in post #1096348:

Love U Ruby wrote in post #1096307:

in the select list no such values like 10.0 or 5.0

You know where to change the value to an integer. Just before you select
in on a list which takes integers!

now say a column has value 10 and 15.2. Now ruby fetches 15.2 as
expected,but for 10, it is 10.0. So if I check for integer,it would say
NO - 10 is float, as program read it as 10.0.

thanks

#10

Joel P. wrote in post #1096351:

And how many select lists do you use which take floats?

In the web-page only one “select list” which contains all mixture of
data? And the integer is the pain of mine. :frowning:

Thanks

#11

And how many select lists do you use which take floats?

#12

Looking at your previous example list, you’re right you have a mixture.
But the
question I have is how do you know what is suppose to be there? Is
every 6th
item in your list suppose to be an integer? You have to do a bit of
critical
thinking here. How is the program suppose to know what to use? Here is
the one
know I can provide. You know that numbers will always be represented as
real
numbers. The question is knowing that, what can you trigger off of to
determine
when to make it an integer? Once you determine that bit of logic,
figuring out
the code will be a piece of cake.

Wayne

----- Original Message ----
From: Love U Ruby [email protected]
To: ruby-talk ML [email protected]
Sent: Mon, February 11, 2013 1:51:48 PM
Subject: Re: Issue with Excel column values read.

Joel P. wrote in post #1096351:

And how many select lists do you use which take floats?

In the web-page only one “select list” which contains all mixture of
data? And the integer is the pain of mine. :frowning:

Thanks

#13

You could write a routine to check the value if its a real number, make
it an
integer. That’s about the best I think you can hope for doing it the way
you are
now.

Wayne

#14

Wayne B. wrote in post #1096356:

Looking at your previous example list, you’re right you have a mixture.
But the
question I have is how do you know what is suppose to be there? Is
every 6th
item in your list suppose to be an integer? You have to do a bit of
critical
thinking here. How is the program suppose to know what to use? Here is
the one
know I can provide. You know that numbers will always be represented as
real
numbers. The question is knowing that, what can you trigger off of to
determine
when to make it an integer? Once you determine that bit of logic,

There presence is dynamic in the column, but some times couldn’t be
there.

Thanks

#15

Wayne B. wrote in post #1096362:

You could write a routine to check the value if its a real number, make
it an
integer. That’s about the best I think you can hope for doing it the way
you are
now.

Wayne

Okay, when the original real number comes into that column say 15.2
then, converting it to integer will again would cause a fatal error as
no such element or values found… :frowning:

#16

I don’t know whether Selenium-Webdriver is easily capable of this kind
of interaction, but this is the sort of thing I’d do when faced with an
unknown select list in Watir-Webdriver:

val = #Your current value

elem = driver.select_list(:name, “defaultAnswerId”)

options = elem.options.map(&:text)

if options.include?(val)
elem.select(val)
elsif val.class == Float && options.include?(val.to_i)
elem.select(val.to_i)
else
puts “#{val} not found in list”
end

#17

Joel P. wrote in post #1096372:

val = #Your current value

elem = driver.select_list(:name, “defaultAnswerId”)

options = elem.options.map(&:text)

if options.include?(val)
elem.select(val)
elsif val.class == Float && options.include?(val.to_i)
elem.select(val.to_i)
else
puts “#{val} not found in list”
end

Wooowwww!!!

#18

I’d rather avoid using rescue unless it’s unavoidable. Testing the
values before committing leaves room to capture genuine exceptions
rather than using them for flow control.

#19

Right, but again, this is where critical thinking comes into play (and
rescue).

Go look here: There are some good examples of things to look at.

http://stackoverflow.com/questions/1235863/test-if-a-string-is-basically-an-integer-in-quotes-using-ruby

#20

Am 11.02.2013 19:01, schrieb Love U Ruby:

I have provided the code, which is the most victim, so the full code is
doing lot’s of operations. So that would confuse the whole pain area.
And I have told that I am using “win32ole”. wbs is the object referring
to the excel work sheet.

First, in your original post you did not state that you use win32ole.

Second, you asked how to read the values from the work sheet without
conversions, and to answer that question the crucial part of the code
would be how you got the wbs object, with which gem, etc.