Read Cell String showing up as a number. Can't convert

Help
1

Hello everyone,

I read a lot of posts on this topic but most are having a problem reading from Excel. I’m successfully reading a list of columns that contain numeric record IDs into a Generic Variable. I need to look for the rows that have “#N/A” in the cell. The "#NA’ is coming in as a number. I tried format changes to the Excel sheet from General to Text as well as trying several ToString conversions and all I get is -2146826246 as a string value. How do ready the “#NA” into a string variable.

Excel column G:

Match Issue
12486
12486
12489
12491
#N/A
12452
12452
12452

image

MatchIssue = -2146826246 for Row 6

Thanks ! Scott

2

@Scott_M

Can you try below steps once and let us know if it’s works or not.

  1. Use Read range activity to read excel and store it in datatable dtOutput.
  2. Use For Each Row activity to iterate through all rows of data table dtOutput.
  3. Put If condition inside For Each Row, If( row(“Match Issue”).ToString.equals(“#N/A”) then perform your desired activity else skip.
1 Like
3

If you are not able to implement the provided solution or still issue exist …pls upload your sample excel sheet with the mention data . i will provide the solution xaml .

4

Hi,

Please try to read/convert the data as (MatchIssue) string.

Thanks,
Leeladhar

5

Thank you for your reply.

I set this up as a data table but I’m getting the same result. It looks like UI Path is reading this column and determining that is a numeric field and importing the numeric equivalent of “#N/A” to the data table. I tried typing an “A” in front of the number in row 1 thinking that it would force a read as a string but that didn’t work either. Any non-numeric columns work fine. I can get around this by using a formula to set the NAs to zeros but a work around like that would not be valid in all applications. Is there some way to force UI Path to read rows as a string?

image

6

Thank you for the offer. I do have a workaround that will get this project to work. I’m worried about down the road if a work around isn’t possible. We do a lot of work with spreadsheets.

7

Ok I played around with this and it ended up being something with the “#” sign in #N/A causing the string to come over as a number. If I remove the "# " the N/A comes over just fine. To resolve, I needed to use a default value of “Not Found” for formulas that result in #N/A.

Use “=IFERROR(VLOOKUP(H2,‘EV Data’!$A:$B,2,FALSE),”“Not Found”“)”

instead of “=VLOOKUP(H2,‘EV Data’!$A:$B,2,FALSE)”

Thanks everyone for all your help.

1 Like
8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.