Change the column type from a data table with Linq

Help Studio
, ,
1

Hi everyone,

I am trying to change tha data type from a data table and I folow the next steps:

  1. Clone the data table into a new one " dt_TransactionsInfoResult = dt_TransactionsInfo.Clone"
  2. Change the data type of the column “dt_TransactionsInfoResult.Columns(“Due Date”).DataType = GetType(System.DateTime)”
  3. Finally load the info into the new table
    “dt_TransactionsInfoResult = (From row In dt_TransactionsInfo.AsEnumerable()
    Select dt_TransactionsInfoResult.LoadDataRow(New Object(){
    row.Field(Of Object)(“Invoice Number”),
    row.Field(Of Object)(“Order Number”),
    row.Field(Of Object)(“Invoice Date”),
    Convert.ToDateTime(row.Field(Of Object)(“Due Date”)),
    row.Field(Of Object)(“Total AMount”)
    },True)).CopyToDataTable”

But I got the follow mistake “Assign: Conversion from string “20/01/2021” to type ‘Date’ is not valid.”

So, I wanto to change the type of the column because I need to sort the info from newer to older dates.

Any idea to solve this problem ??

2

LINQ doesn’t edit datatable properties.

Use an Add Data Column to add the new column with the datatype you want. Use a For Each or LINQ expression (still loops, by the way) to update the new column with the converted values based on the old column.

Remove Data Column to get rid of the old column.

And you don’t have to do all that cloning etc for this. You can just do it all directly in your datatable.

3

Hi @marquezd

Try this syntax:

dt_TransactionsInfoResult = (From row In dt_TransactionsInfo.AsEnumerable()
                             Select dt_TransactionsInfoResult.LoadDataRow(New Object() {
                                 row.Field(Of Object)("Invoice Number"),
                                 row.Field(Of Object)("Order Number"),
                                 row.Field(Of Object)("Invoice Date"),
                                 DateTime.ParseExact(row.Field(Of String)("Due Date"), "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture),
                                 row.Field(Of Object)("Total Amount")
                             }, True)).CopyToDataTable()

To sort the dates try the below query:

sortedDataTable = dt_TransactionsInfoResult.AsEnumerable().OrderBy(Function(row) row.Field(Of DateTime)("Due Date")).CopyToDataTable()

Hope it helps!!

4

@marquezd

' Clone the DataTable and change the data type of the "Due Date" column
dt_TransactionsInfoResult = dt_TransactionsInfo.Clone()
dt_TransactionsInfoResult.Columns("Due Date").DataType = GetType(System.DateTime)

' Load data into the new DataTable
dt_TransactionsInfoResult = (
    From row In dt_TransactionsInfo.AsEnumerable()
    Select dt_TransactionsInfoResult.LoadDataRow(New Object(){
        row.Field(Of Object)("Invoice Number"),
        row.Field(Of Object)("Order Number"),
        row.Field(Of Object)("Invoice Date"),
        DateTime.ParseExact(row.Field(Of String)("Due Date"), "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture),
        row.Field(Of Object)("Total Amount")
    }, True)
).CopyToDataTable()

In invoke Vba create a argument of dt_TransactionsInfoResult as Out
dt_TransactionsInfo as In argument

5

Tnks guys it works,

have nice day.

1 Like
6

You’re welcome @marquezd

Happy Automation!!

7

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