Lookup Data table values with different data types

Hi Everyone,

Suppose I have 3 columns on an Excel spreadsheet:

  • Column A (contains integers and are recognized as integers by Excel)
  • Column B (contains integers, but are recognized as text by Excel).
  • Column C will be used to store the lookup result as string.

I need to perform a lookup with Column’s B value (string) on Column A (integers).
How can I achieve this? Please keep in mind that it will at least bring one resulting value from the lookup.

Thanks in advance.

Could you provide an example file / table layout etc?

Are you looking to compare column A to column B in the same row?

Hi

Hope the below steps would help you resolve this

  1. Let’s read the excel first with read range activity where in that you got an option in the property named PRESERVE FORMAT

Enable that and get the output as dt

  1. Now use a BUILD DATATABLE activity with same column structure where first is integer and second is also integer and third to the format you need
    And get the output as dt_final

  2. Now use a merge DATATABLE activity and mention dt as source and dt_final as destination

  3. Your dt_final varivale will have the data in the correct format

Let’s use now lookup DATATABLE activity to perform the loop up
Have a view on this doc for how to use it

Cheers @x10khz

@x10khz

Check below for your reference

Reference

Hope this may help you

Thanks

Hi Palaniyappan,

Sorry for the late update.

I follow you suggestion but on the MergeDataTable activity and getting the following error “.Column A and .Column A have conflicting properties: DataType property mismatch”.
Source: dt (looks as below)

target: dt_final (if possible, will like to print the results with their correct formats (Number as integers and Text as Strings).

Once I read the file to be formatted, I check data type of the 1st column “Column A” (vColA.GetType.ToString) and prints “System.String” as type, even though Excel displays the number as “Number” and the Excel cell format was set as “General”.

Additionally,
I’ve been playing around with the MissingSchemaAction properties and set it as “Ignore”, then I get the following error “Type of value has a mismatch with column typeCouldn’t store <12345> in “Column B” Column. Expected type is DataColumn.”

Regardless of the above, I set the Column A from target as “Number” and “String”, but still getting the same error.

Hope you can help me with what I’m doing wrong. Will really appreciate it!!

Regards

Hi TimK,

Thank you for your reply.

Unfortunately I can not provide a file example. Below you are going to find a screenshot of how it looks the file to be formatted.

As mentioned, I’m trying to perform a lookup with the value on Column D on A and bring the value on Column C and print it on Column E.

Hope this clarify the task.

Regards!

I’ve been doing some testing trying to find a way to resolve the format issue.

I know that there are some options like VBA or UiPath packages, for unfortunately I’m working on a Corporate VDI and these external sources aren’t allow it.

I found that if I use a CSV file format, the datatype format issues are resolved. I hope this works for some that have the same issue/conditions as I.

Regards.

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