I am facing a problem after joining tables

@P-A-J

Looks like the given columns are wrong or the tables are having slightly different data

Cheers

Something is different about the formatting of ID and Emp Id so they aren’t matching. Checking PreserveFormat for the Read Range (workbook) Table1.xlsx fixes it.

Emp Id , i took from an excel using Read Range which had it has Object type.
ID , i took from build data table , i kept its datatype as Object type.

Did i do something wrong ?

Don’t use Build Datatable before reading the files. The Read Range creates the datatable for you.

But that doesn’t matter because whatever you did in Build Datatable just gets overwritten by the Read Range.

There is no reason Emp Id would be Object.

Use the WORKBOOK Read Range activity to read the files, not the EXCEL Read Range. In the Read Range that reads Table1.xlsx just check the PreserveFormat box. Then the values will match between the two tables.

Or figure out how the format of the columns is set in both Excel files and why it’s different, and fix that difference.

Table1(Emp Id) is from read range workbook and Table 2(ID) is forming in runtime.
For Table 2 i used build table.
I used this syntax " Datatable.Columns(“Emp Id”).Datatype " to check the datatype and found Emp Id was a object and ID was a string.
So for performing joins , i made ID column a object. So that both ID columns from both table will have same datatype

I tried with preserve format , but i am still getting this error “Column 2(Emp Id) datatype is not supported”

If you already have Table 2 in a datatable then why are you writing it to Excel then reading it back in? That makes no sense.

You’re doing it backwards. You shouldn’t use Object as a column datatype.

Make them both String.

How should i convert object type column to string type ?

Hi @P-A-J ,

I believe the input excel sheets were not shared ? Could you share them so that we get a clear Idea on what is the data/data format that you are dealing with, this would help us to provide accurate suggestions towards your case.

Set them all to string in your Build Datatable for Table 2.

Use workbook Read Range to read the Table 1 Excel file and they’ll be string not object. For whatever reason, I had to check the PreserveFormat box for this one to read properly.

He sent them to me by PM and I had no trouble getting them to work and have given him explicit instructions on how, so I don’t know what the problem still is.

I am using workbook activities and even though i used preserve format , it is still not joining the tables properly

Hi @P-A-J
Please check the code
MergeDatetebles using linq.zip (10.4 KB)

let me know if that works
Happy automation

2 Likes

You said you are building one of the tables in your automation, and that you used Object as the datatype for the column. I keep telling you don’t use Object, use String.

No no , the schema that i built has a ID in string datatype. The Emp Id that is object , is what i got from ReadRange in Excel. I dont know how or why it is giving so.

As I said, use the workbook Read Range and check the PreserveFormat box.

preserve format , Not Working

There are two Read Range activities one under Excel and one under Workbook. Which are you using?

Use the one under Workbook.

image

Workbook read range , which do have preserve format option

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