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

Help Studio
1

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

2

@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()
1 Like
3

@supermanPunch I am getting below error.

WhatsApp Image 2020-08-24 at 2.49.40 PM
WhatsApp Image 2020-08-24 at 2.49.40 PM1280×720 98.5 KB

4

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

5

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

6

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

7

@supermanPunch it worked, Thanks:-)

1 Like
8

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

image

9

@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:

10

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

11

@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 :

12

@supermanPunch That is also not working :roll_eyes:

13

@Shaik.Yezdani What was the error ?

14

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

image

15

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

16

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

17

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

1 Like
18

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

19

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

20

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