Excel Read Range of strings into datatable adds quotation marks to some strings

Help Activities
, ,
1

I am using Excel Read Range to get a list of possible strings from an Excel worksheet. The resulting Datatable has some lines where quotation marks are added at the beginning and end of the string.

This cause a runtime error when trying to use Any function to test a particular string is in the list.

This is the workflow:

The image shows a UiPath workflow that involves opening an Excel file, reading a range from a specified table, and assigning values from the table to a variable. (Captioned by AI)
The image shows a UiPath workflow that involves opening an Excel file, reading a range from a specified table, and assigning values from the table to a variable. (Captioned by AI)510×699 24 KB

The Excel file looks like this:

The image is a screenshot of a spreadsheet showing various messages from specialists, mostly regarding the receipt and acknowledgment of referrals. (Captioned by AI)
The image is a screenshot of a spreadsheet showing various messages from specialists, mostly regarding the receipt and acknowledgment of referrals. (Captioned by AI)777×657 27.3 KB

When the data table is populated lines 21 and 22 get quotation marks:

The image is a screenshot of a text editor window containing various template messages related to the acknowledgment and processing of referrals for appointments, with many spelling and grammar corrections indicated. (Captioned by AI)
The image is a screenshot of a text editor window containing various template messages related to the acknowledgment and processing of referrals for appointments, with many spelling and grammar corrections indicated. (Captioned by AI)656×723 34.6 KB

Then the assign step causes this error: “Assign: Expression Activity type ‘VisualBasicValue`1’ requires compilation in order to run. Please ensure that the workflow has been compiled.”

The error seems to be related to the quotation marks that occur - it does not happen if the datatable does not have lines 21 and 22 etc.

Any Ideas, please. This is causing a real headache in a complicated workflow

2

HI,

If possible, can you share your workflow and the workbook? It’s no problem if dummy data/workflow which can be reproduced this matte

Regards,

3

testload.xlsx (16.9 KB)
load messages.xaml (10.9 KB)

The filepath will need modifying in the Excel Read Range…

Thanks

4

Hi,

This error is not related with excel data. Double quote except 0x22 character (") causes this matter. For now, can you try to replace your expression with the following? (Or replace your double quote with " ?

dt_Test.AsEnumerable.Any(Function (x) x("SpecialistMessage").toString.Trim.Equals(  TestString ))

Regards.

1 Like
5

Thanks @Yoichi , but the issue I have is that the original string in the excel cells does not contain the characters and then UiPath seems to add those characters in some lines when they get added to the datatable.

I have checked the strings in a HEX editor and cannot see any hidden characters in those positions, but they may be there, I guess.

But, you are correct - I copied the assign example from a forum post and the quotation marks were the wrong type.

I have tested the functionality and it now works and returns the right result even for the string in line 21.

Thank you for the quick response!

6

Hi,

I directly checked the string data inside the xlsx file. However, there is no special character including double quote.

image

i guess it’s caused by comma in each data. If you need to remove the double quotes, Trim(Chr(34)) may help you as workaround.

Regards,

1 Like
7

Thank you .

You have solved the mystery for me, and also solved the issue I had with the wrong quotation mark type in my code.

8

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