Excel table variable type casting


#1

Hello,

So I’m getting data from an Excel file in form of a table then accessing the individual rows to type onto a webform. However, I have a number value in my Excel table and it should be strictly number or integer, but the WorkFusion table format is double (like 10.0).

cap5

This is the output from the WorkFusion table but I would like the numbers without decimal points. Is it possible to do casting or keep my data like it is orginally?


#2

Hi @scarlett_njeri,

Table variable contains only String values.

To read “10” instead of “10.0” you can do one of the following actions:


#3

Hi,

Iam pushing the exceldata into table datatype by using Get Range action,the integer fields are added with decimals,one decimal is adding to my data(Eg: 10 to 10.0) and big int value is converted to exponential(Eg: 56475544 to 5.6475544E7).I want the same value to be present without any decimals. My excel data format is text eventhough the table is saving in decimal format.I want same data to be copied into the table without any decimal points.while iam passing the data from table the decimal value is passing .How to solve this problem.

Actual data is:
10 test 56475544
rpa table data is:
my_table Table “{”“rows”":[["“10.0"”,"“test”","“5.6475544E7"”]]}"


#4

Hi @hemadri_kamatham,
Please read the suggested solution above in this conversation.
Also, I’m attaching the link to another topic that might be useful for you Longer variables unable to be written as a number

Best regards
Rehina Ivanova
Workfusion Service Desk


#5

Hi Rehina Ivanova,

Thank you for ur support.

I am not able to convert the scientific num to integer. Kindly find the below attached sample project and give a solution to solve this problem.
Datatype_casting.zip (125.6 KB)

Thanks & Regards,
Hemadri


#6

Here is a working sample - Datatype_casting_1.7z (101.6 KB)

You can also use the Text actions to cut the decimal part of a string - https://kb.workfusion.com/display/RPAe/Text

BTW you have 1.1.4 version which is not supported now - please update to 1.1.7 which has new cool features


#7

Hi
I am having the similar probelm but the solution doesnt work for me.
My Excel contains items like this:

30606996310
mickeymouse
30606996319
user.name

All is formatted as text in Excel but the “get cell” action returns the value “3.060699631E10” for the first item.
Do you have an updated “Datatype_casting_1.7” script? The one you sent doesnt work for me in 1.4.0.

Thanks!


Unable to copy this 1 column (values) exactly on the website ..rest is working fine
#8

Hi Tim,

Try this example: Datatype_casting_1.4.0.zip (6.5 KB)


How to keep the text formart
Excel Get Range -> Table -> Set Range changes numeric format to 0.0
#9

Thanks Alesia

it works as long as in column 3 of the Excel example are only numeric values. But in my example that coloumn contains a mix of numeric values (30606996310) and text values (mickeymouse) and I get an error on the latter:

Error executing NumberFormatAction
(…) Caused by: java.lang.IllegalArgumentException: Representation ‘mikeymouse’ is unknown to RNumber mask ‘0.00E0;en-US’
(…)

The use case I am working on is checking a list of usernames. These could be numbers or text.


#10

Does the number always have 11 digits?


#11

In this case yes.


#12

Try using If-else to check if the string matches regex [0-9]\..*E.*, and only use the Number format if it does. The regex should work if there is a different number of digits, too.


#13

Yes! This works great!
Thanks Alesia!