How to convert variable datatype while getting from Excel

Help
,
1

Hi

I want to reconcile two excel files. those excel file columns are in general format.so, it will not be matched correctly.suggest some solution for this.

Thank U.

2

Hi
usually when you read a excel with read range activity the output we get is datatable which will usually have all the values in string format…
so if you want to use it as it is as a string, no worries buddy you can use directly and compare them or validate them with if condition

but if you want to convert them to integer or double or decimal you can use like this buddy
suppose we want to convert the type of column1 to integer which is actually in string format

Convert.ToInt32(row(“Column1”).ToString)
or to double like this
Convert.ToDouble(row(“Column1”).ToString)

now you can change the format you want or

if you want to keep the format as such in the excel, while using read range activity we have a property called preserve format in read range , kindly enable it so that you would get the exact format of what is there in excel

Kindly try this and let know whether this works or not
Cheers @Kalees9486

3

Thanks for your reply .@Palaniyappan
But i have used join datatable activity for comparing those excel files instead of if activity.because its huge amount of data.so inside the join table activity we put column names only.how to check or convert the column name/Value inside the join table activity.

Thank U

4

Hmm in join datatable you dont need to convert at all buddy it will take the value and compare based on the join you mention like Full join ,inner join or left join, and this will be done taking its own format no need to change
Cheers @Kalees9486

5

Yeah correct.i have used left join. so,if the column values are same means it will join automatically,i am correct.i want to check < = Condition in join Datatable .@Palaniyappan

Thank U

6

enable the property preserve format in read range activity buddy @Kalees9486
and try to join the tables
Cheers

1 Like
7

OK I try this.

Thank U

1 Like
8

or if that does not work you can the change the whole column type like this
use a assign acivity like this before mentioning the column name in join datatable @Kalees9486

Yourdatatablename.Columns("Column1").DataType = GetType(system.Int32)

this will change the whole column to the type you mention buddy @Kalees9486
this will work for sure @Kalees9486
i tried
Cheers

1 Like
9

Capture

Is this Correct? @Palaniyappan
I have used this inside assign then what i will do?

In join activity, how to do this?

Thank U

1 Like
10

yes exactly @Kalees9486
then you can mention this column name in join datatable @Kalees9486

11

Column Name? @Palaniyappan

12

is that working buddy @Kalees9486

1 Like
13

Yes mention the columnname in(which was changed with datatype in assign activity) join datatable as you did previously @Kalees9486
now this will join as we have changed the datatype of the whole column as integer
Try this and let know
Cheers @Kalees9486

14

It will throw an error. @Palaniyappan

15

Target Exception

16

Can u share that workflow? or Screenshot.

17

if possible can i have the xaml of yours buddy as it would easy to debug and come out with a solution Cheers @Kalees9486

18

I have attached Xaml file FYR, @Palaniyappan

Flowchart_Inv.xaml (10.3 KB)

1 Like
19

@Palaniyappan any update?

1 Like
20

buddy is quantity is in decimal type @Kalees9486

sure…
image
image

Cheers…