RPA recognized Excel number as text

I have a list of product ID generated from another system.
The format in excel is already in number and look perfect in the excel file. But when RPA copy that column to search in supplier website, it become like 7.423445E+11.

When I convert it to “Text” with the little green flag at top left of cell. It works in RPA.
I am not sure if this got to do with Excel or RPA? Any advice?
Capture

Hi @Kinalina

A recorder variable of Tables type is an array of strings, so when the recorder copies the data from Excel to a Table variable, the numbers in the cells are copied as text.

You can use Number format action (Reformat string) to set the correct format for the value

@ashapkina
I have another issue here, I have different excel list from different suppliers and sometime their product ID are prefixed with symbol like “=” it look like normal number in excel but in rpa doesn’t seem to recognized it.

So i tried using the number format “Convert string to number” but encountered error.
Is there a way for RPA to read the excel number regardless of it excel formatting?

Any advice? Thank you.
rpa Capture

@Kinalina check how the ${productid} is written in the execution-result log. It probably has a different format from the one used in the Number Format action.

@ashapkina
Sorry, dont quite understand about the error. attached is the error, hope you can advise me. Thank you.error.txt (5.5 KB)

Does it mean it can only convert from 1 pre-defined format? ie. 0.00E0?

Number conversion can be a little tricky, especially if the number has different formatting possibilities.

If you look at the examples in the number conversion type you can see some options and expand from that.

I would advise using the exception handling element, nested, with the most common type in the first “try to complete “ and other alternative options below.

Start by looking at the format options under the number convert type.

1 Like

@Kinalina According to the error log, ${productid} is empty, that’s why the Number format action fails.

Caused by: java.text.ParseException: Unparseable number: “”

You can use Exception handling as @Bonnero suggested above to avoid the error and use several number formats.

@ashapkina
When I tried the following step…i had the error. error.txt (5.4 KB)
But strangely, when I add the filter “for each” say 2-5, then it works?

Why is this so, I am using version 2.0.3

step1step2step3

java.lang.IllegalArgumentException: Representation ‘ISBN’ is unknown to RNumber mask ‘0.00E0;en-US’

The first value in the list is “ISBN”, it is not a number format, that’s why the action doesn’t work. I guess the other values are of the same format as in the action so they work.

image

@ashapkina
Thanks, I got the above fix.Can I ask for advice again. when i try to set constant value to it, the number didnt work (ie. it came out as 123E3 format).
I know the problem lies with the variables and i tried a couple of combinations but it didnt work.
Appreciate very much if you could help me.
number1number2number3number4

The value in “element” has format 123E3, so when you assign it to the “cell_number” variable usin Constant value, it keeps the same format, it is just copied from one variable to the other.

To change the format, you need to use Number Format action to reformat the string in “element” and save to “cell_number”, and then use “cell_number” in the following actions, because “element” still contains the old value ( in 123E3 format), the new value is save in the “cell_number” variable.

@ashapkina @Bonnero Using exception handling, in the number format i tried using = using the format but error occur. other than the available format presets in number format, how can i create custom format?
Capture

@Kinalina you can use a Substring between to cut out the = character.

hello @ashapkina
I tried substring between and substring but rpa doesn’t seem to recognized it.
Is it because only at the fx bar, the symbols are seen?
Any way to fix it other than re-formatting the excel?

excel

@Kinalina play the recording again and check how the values are saved in the recorder variables (in the execution-result.log)

image

@ashapkina
Is this the one?execution-result.log.csv (303 Bytes)
ID

@Kinalina In the log file, the values are empty. It happens because of the = sign in the cells. RPA Express currently cannot save the value starting with =.

Now, you can only copy such values by opening the file and copying the cells using mouse and keystrokes actions.

I see… I guess for now, I’ll do the formatting in excel. Thanks so much for checking :smiley:

@Kinalina the issue with long numbers in Excel has been fixed

Number Format Error
I tried executing in rpa express trying to convert strings to number from excel to tally
Getting this error
Kindly help

Error executing NumberFormatAction
com.workfusion.studio.rpa.recorder.playback.PlaybackException: Error executing TemplateAction[templateName=NumberFormatAction.ftl,id=9,name=Optional[NumberFormatAction],parent=-2,nextSibling=10,arguments=ActionArguments[outputLocale=[en-IN],input=[${element}],targetVariable=[number_of_products],delay=[0],inputLocale=[en-IN],xsi:type=[recorder:NumberFormatAction, recorder:NumberFormatAction],pollingInterval=[300],active=[true],direction=[STRING_TO_TYPE],awaitTimeout=[5000]]]
at com.workfusion.studio.rpa.recorder.playback.flow.StandardControlFlow.execute(StandardControlFlow.java:54)
at com.workfusion.studio.rpa.recorder.playback.action.template.TemplateAction.execute(TemplateAction.java:28)
at com.workfusion.studio.rpa.recorder.playback.action.template.TemplateAction.execute(TemplateAction.java:15)
at com.workfusion.studio.rpa.recorder.playback.player.ActionPlayer.next(ActionPlayer.java:64)
at com.workfusion.studio.rpa.recorder.player.PlaybackLogic.playNextAction(PlaybackLogic.java:152)
at com.workfusion.studio.rpa.recorder.player.PlaybackLogic.run(PlaybackLogic.java:112)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.IllegalArgumentException: Representation ‘’ is unknown to RNumber mask ‘;en-IN’
at com.workfusion.studio.rpa.recorder.api.internal.representation.RNumberRepresentation$CustomRepresentation.getValue(RNumberRepresentation.java:117)
at com.workfusion.studio.rpa.recorder.api.internal.representation.RNumberRepresentation$CustomRepresentation.getValue(RNumberRepresentation.java:84)
at com.workfusion.studio.rpa.recorder.api.internal.representation.RNumberRepresentation.getValue(RNumberRepresentation.java:46)
at com.workfusion.studio.rpa.recorder.api.types.RNumber.fromRepresentation(RNumber.java:85)
at com.workfusion.studio.rpa.recorder.api.types.RNumber$fromRepresentation$2.call(Unknown Source)
at Script12.run(Script12.groovy:8)
at com.workfusion.studio.rpa.recorder.playback.shell.GroovyShellWrapper.executeScript(GroovyShellWrapper.java:48)
at com.workfusion.studio.rpa.recorder.playback.player.PlaybackContext.executeScript(PlaybackContext.java:65)
at com.workfusion.studio.rpa.recorder.playback.action.template.TemplateAction.executeBehavior(TemplateAction.java:33)
at com.workfusion.studio.rpa.recorder.playback.flow.StandardControlFlow.execute(StandardControlFlow.java:46)
at com.workfusion.studio.rpa.recorder.playback.action.template.TemplateAction.execute(TemplateAction.java:28)
at com.workfusion.studio.rpa.recorder.playback.action.template.TemplateAction.execute(TemplateAction.java:15)
at com.workfusion.studio.rpa.recorder.playback.player.ActionPlayer.next(ActionPlayer.java:64)
at com.workfusion.studio.rpa.recorder.player.PlaybackLogic.playNextAction(PlaybackLogic.java:152)
at com.workfusion.studio.rpa.recorder.player.PlaybackLogic.run(PlaybackLogic.java:112)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.text.ParseException: Unparseable number: “”
at java.text.NumberFormat.parse(NumberFormat.java:385)
at com.workfusion.studio.rpa.recorder.api.internal.representation.RNumberRepresentation$CustomRepresentation.getValue(RNumberRepresentation.java:113)
at com.workfusion.studio.rpa.recorder.api.internal.representation.RNumberRepresentation$CustomRepresentation.getValue(RNumberRepresentation.java:84)
at com.workfusion.studio.rpa.recorder.api.internal.representation.RNumberRepresentation.getValue(RNumberRepresentation.java:46)
at com.workfusion.studio.rpa.recorder.api.types.RNumber.fromRepresentation(RNumber.java:85)
at com.workfusion.studio.rpa.recorder.api.types.RNumber$fromRepresentation$2.call(Unknown Source)
at Script12.run(Script12.groovy:8)
at com.workfusion.studio.rpa.recorder.playback.shell.GroovyShellWrapper.executeScript(GroovyShellWrapper.java:48)
at com.workfusion.studio.rpa.recorder.playback.player.PlaybackContext.executeScript(PlaybackContext.java:65)
at com.workfusion.studio.rpa.recorder.playback.action.template.TemplateAction.executeBehavior(TemplateAction.java:33)
at com.workfusion.studio.rpa.recorder.playback.flow.StandardControlFlow.execute(StandardControlFlow.java:46)
at com.workfusion.studio.rpa.recorder.playback.action.template.TemplateAction.execute(TemplateAction.java:28)
at com.workfusion.studio.rpa.recorder.playback.action.template.TemplateAction.execute(TemplateAction.java:15)
at com.workfusion.studio.rpa.recorder.playback.player.ActionPlayer.next(ActionPlayer.java:64)
at com.workfusion.studio.rpa.recorder.player.PlaybackLogic.playNextAction(PlaybackLogic.java:152)
at com.workfusion.studio.rpa.recorder.player.PlaybackLogic.run(PlaybackLogic.java:112)
at java.lang.Thread.run(Thread.java:745)