Unable to convert supplied value to a Number : Unable to cast value '' to a number data item

[SANMATINAVALE]

Hey hi Folks, I'm dealing with excel where i'm facing an error Unable to convert supplied value to a Number : Unable to cast value '' to a number data item

I've to do some calculations using excel data. That's why i'm using data casting with the help of ToNumber().   But i'm facing above error. Although the value i'm tring to cast is numeric value; But due to excel format i get thoes values as a text. So, What is the best way to convert text into number?

I tried to save this result into Number data item and aslo in Text data item but still facing same error.

------------------------------
SANMATI NAVALE
RPA DEVELOPER
Pune
------------------------------

[SANMATINAVALE]

Hey.. I overcame on this issue.

The reson behind it, There are null rows into coll/data files.

Using Append Field(Text) action (Because data field type was Text)from Utility - Collection Manipulation added "0" value into every row of desired column.

Afterwards using ToNumber() Data casting happend smoothly..

------------------------------
SANMATI NAVALE
RPA DEVELOPER
Pune
------------------------------

[ManpreetKaur1]

Hi @SANMATINAVALE ,

In order to cast the values to number, did you try casting directly into the excel using a code stage?

The reason is if you have 1000's of rows then getting the data into collection and then performing "ToNumber()" casting will take a lot of time and might not be the best way to perform the automation. 

------------------------------
Manpreet Kaur
Manager
Deloitte
*If you find this post helpful mark it as Best Answer
------------------------------

[ÖzençGezgin]

Hi @SANMATINAVALE ,

If columns are specified, it is best to select the data type as a number when retrieving collection

Have a nice day...

------------------------------
Özenç Gezgin
Supervisor
Türkiye İş Bankası A.Ş.
Europe/Istanbul
------------------------------

[SANMATINAVALE]

Hi @Manpreet Kaur,

Thank you for your reply.

This is just one of the small modules of the project and in SOP documents there are specifically mentioned that calculation data rows should be less than 24 row's.

Because we generated this data from the other automation and at the end we do some calculations based on this excel generated data.

That's a reason why we used Conversion functions for casting purposes.

But, Yes you are right, if there is a case where we've to deal with huge data it's better to go with the code stage for casting.

------------------------------
SANMATI NAVALE
RPA DEVELOPER
Pune
------------------------------

[ManpreetKaur1]

Hi @SANMATI NAVALE ,

Since you already knew that it will be less than 24 rows, then it's okay to use collection to perform the casting as there won't be any performance issues.

------------------------------
Manpreet Kaur
Manager
Deloitte
*If you find this post helpful mark it as Best Answer
------------------------------

[Neel1]

Recently - we were into similar issue and got stuck in text and number , were dealing with thousands of records and using oledb,

finally written a small code for that column and calling that if oledb failed for data type mismatch.

------------------------------
Neeraj Kumar
Technical Architect
------------------------------