How to change the data type of a column in data table which is having blank values also

Hi Guys,

I want to know how to change the data type of a column in data table which is having few blank field aslo. i am trying “dt = (From row In dt.Select Order By Convert.ToInt32(row(“PRLINENUM”)) Ascending Select row).ToArray.CopyToDatatable()” but getting error because of blank values. please help me

@Shaik.Yezdani Check the below query that handles the Empty values :

 (From row In dt.Select Order By Convert.ToInt32(if(String.IsNullOrEmpty(row(“PRLINENUM”).ToString),"0",row(“PRLINENUM”).ToString)) Ascending Select row).ToArray.CopyToDatatable()

@supermanPunch I am getting below error.

@Shaik.Yezdani Change 0 to “0” and check. I have also updated the Query Above

@supermanPunch Now getting error as CopytoDatatable is not a member of System.Array

@Shaik.Yezdani You might need to perform some extra steps by editing your xaml file as mentioned in the post below :

@supermanPunch it worked, Thanks:-)

1 Like

@supermanPunch even i changes also while writing into excel it is taking as text only.

image

@Shaik.Yezdani Actually the operation that you do is just sorting the Rows based on the Column you have provided, It doesn’t change the type of that column.

I thought you wanted to sort the data based on that column, If you have to convert that column to an Integer, you need to do some other set of steps :sweat_smile:

@supermanPunch can you help me here, i don’t want to go for the loop, i want any select query to do that.

@Shaik.Yezdani But that would be a simple and best way to update the Column, Since it’s just one loop there’s no overhead in it :sweat_smile:

Another way would be to use this Custom Activity I suppose :

@supermanPunch That is also not working :roll_eyes:

@Shaik.Yezdani What was the error ?

@supermanPunch not getting any error, but the output is same as above.

image

@Shaik.Yezdani That shouldn’t be the case :thinking:

@supermanPunch can i provide the input file will you try?

Hi,
It seems problem is with excel file.
Please check datatype of that cell in output file.

1 Like

@poorna_nayak07 Data is directly coming from DB, i wanted to change the datatype to Int or double for that column.

@supermanPunch & @poorna_nayak07 Please find the attached fime sheet1 is the output from DB.Test POCC.xlsx (559.3 KB)

Try appending single quote(’) to your column values (like '100316001882 in 3rd column).