Not able to read cell value with different format in excel

Help Studio
, ,
1

Hi i am not able to read the highlighted cell value from the excel because it is in different format. i tried using both excel read range and workbook read range activity. can someone help me pls

image
image1637×500 37.6 KB

2

Hi @Sirisha_Siri ,

Are you running into an error as your read the value, or is the value being retrieved in the incorrect format(with the E+15)?

If its the former, then you could try declaring a variable of type UiPath.Core.GenericValue and store it into that.

If its the former, we can look at that as well.

Kind Regards,
Ashwin A.K

3

hey

please check the propertie called preserve format

regards

4

i am able to read the value and write it in other sheet, but while performing VLookup on that column, bot is not identifying that cell value and its not retrieving the corresponding cell value which is giving a mismatch in the output

5

Hi @Sirisha_Siri ,

Could you try reading that Cell and output it into the Immediate Panel to check whether its being retrieved as a number or in the scientific notation?

Kind Regards,
Ashwin A.K

6

it is retrieving as a scientific notation @ashwin.ashok

7

it is taking long time to read entire excel when we are enabling preserve format @fernando_zuluaga

8

here is my code @ashwin.ashok

it has to perform vlookup for the data on two different excels, it is giving expected output for all but only the cells which are having data in “E+15” format is not able to receive the matched value

MKPFOutput1_4_2022.xlsx (103.8 KB)
Main.xaml (12.8 KB)
ECC To Investigate_1_4_2022.xlsx (181.5 KB)

9

Hi @Sirisha_Siri ,

Don’t you think it is odd?
All the other items in that particular Column is alphanumerical, whereas there is just this one instance of it being completely numerical.

I suspect that that entry is incorrect.
Also, using code we won’t be able to retrieve the original value with 100% accuracy->

image

Decimal.Parse("3.22274E+15",System.Globalization.NumberStyles.Any)

As an experiment, you could try using the TRIM() function in Excel to see what its original value is like so →

image
image

Kind Regards,
Ashwin A.K

10

So is it not possible to map those values that has complete numerical values @ashwin.ashok ?

11

I’ll look into it once more, please give me some time.

Kind Regards,
Ashwin A.K

1 Like
12

Hi @Sirisha_Siri ,

I’m not entirely sure if this will provide the expected output, so please verify if the output is as expected → 

(From i In Enumerable.Range(0, dt1.Rows.Count)
Group Join d2 In dt2.AsEnumerable
On dt1.Rows(i)("Transaction Confirm No").toString.Trim Equals If(Decimal.TryParse(d2("Document Header Text").toString.Trim,0),Decimal.Parse(d2("Document Header Text").toString.Trim,System.Globalization.NumberStyles.Any).ToString,d2("Document Header Text").toString.Trim) Into grp=Group
Let lkv = If(grp.Count().Equals(0),"N/A",grp.First().item("Material Document").toString)
Select t=Tuple.Create(i, lkv)).ToDictionary(Function (t) t.Item1, Function (t) t.Item2)

Kind Regards,
Ashwin A.K

13

No @ashwin.ashok …it is not giving expected output, also the other cell values are getting mapped in E+N format with this code

MKPFOutput1_4_2022.xlsx (103.8 KB)