LINQ Query to change the datatype of few columns of a datatable

Help Studio
1

i have table of data as below
The image shows a spreadsheet with six columns labeled A to F, containing a mix of text and numbers. (Captioned by AI)
i need to change the datatype of column C,E and F to ineteger type, kindly help with a LINQ query for the same

2

@Amrutha.mg,

If you are looking for changing the format in excel, use Format Cells activity.

3

i want to change in datatable

4

Hi,

  1. For Each Row:
  • Input: ‘YourDataTable’
  1. Assign Activity for Column C:
  • ‘row(“C”) = If(IsNumeric(row(“C”).ToString), Convert.ToInt32(row(“C”)), 0)’
  1. Assign Activity for Column E:
  • ‘row(“E”) = If(IsNumeric(row(“E”).ToString), Convert.ToInt32(row(“E”)), 0)’
  1. Assign Activity for Column F:
  • ‘row(“F”) = If(IsNumeric(row(“F”).ToString), Convert.ToInt32(row(“F”)), 0)’

This will iterate over each row in the ‘DataTable’ and convert the values in columns ‘C’, ‘E’, and ‘F’ to integers. If the value cannot be converted, it will assign to ‘0’ as default.

This is probably the shortest and the most readable way to achieve your goal.

5

You can basically use this query:

DT_Table = DT_Table.AsEnumerable().Select(Function(row)
row(“A”) = row(“A”).ToString() ’ Convert column A to String
row(“B”) = row(“B”).ToString() ’ Convert column B to String
row(“D”) = row(“D”).ToString() ’ Convert column D to String
row(“C”) = If(IsNumeric(row(“C”).ToString()), Convert.ToInt32(row(“C”)), 0) ’ Convert column C to Integer
row(“E”) = If(IsNumeric(row(“E”).ToString()), Convert.ToInt32(row(“E”)), 0) ’ Convert column E to Integer
row(“F”) = If(IsNumeric(row(“F”).ToString()), Convert.ToInt32(row(“F”)), 0) ’ Convert column F to Integer
row
).CopyToDataTable()

Regards,

Kardelen Cihangir

1 Like
6

@Amrutha.mg
keep in mind

  • when datatable C column is defining the used DataType: e.g. String
  • then: we cannot convert the string into int32 and assigning it back

In such case we do the conversion with a helper column like

  • Add helper Col: C_asInt, ColumnDatatype: Int32
  • Convert C to int - e.g. CInt(row(“C”).toString.Trim) and assign it to C_asInt Col of the same row
  • afterwards
    • remove C Column
    • rename: the helper Column to “C” (or the other origin column name)
    • reorder columns to the origin orders
7

Thank you for the Response, i don’t want 0 if the field is blank. if the value is blank previously i want it blank after conversion as well. Kindly respond.

8

Hello @Amrutha.mg

So suppose you data table is dt_data here is the LinQ query
Use Assign Activity
dt_data = dt_data.AsEnumerable().Select(Function(x) dt_data.Rows.Add _
({x(“A”), x(“B”), If(String.IsNullOrEmpty(x(“C”).ToString), Nothing, CInt(x(“C”))), _
x(“D”), If(String.IsNullOrEmpty(x(“E”).ToString), Nothing, CInt(x(“E”))), _
If(String.IsNullOrEmpty(x(“F”).ToString), Nothing, CInt(x(“F”)))})).CopyToDataTable()

Hope this helps!

9

Hi thank you for your response but this is throwing me following error
Assign: Collection was modified; enumeration operation might not execute.

10

Oh sorry!
First clone the datatable use assign declare a new data table variable dt_result
dt_result = dt_data.clone()
And then use this
dt_result = dt_data.AsEnumerable().Select(Function(x) dt_result.Rows.Add _
({x(“A”).ToString, x(“B”).ToString, If(String.IsNullOrEmpty(x(“C”).ToString), Nothing, CInt(x(“C”))), _
x(“D”).ToString, If(String.IsNullOrEmpty(x(“E”).ToString), Nothing, CInt(x(“E”))), _
If(String.IsNullOrEmpty(x(“F”).ToString), Nothing, CInt(x(“F”)))})).CopyToDataTable()

Try this !