Date format in excel query

Help Activities
1

Hi all,

I am facing issue with date format. Can you please help me.

I have an excel in open date column in that few rows have date in dd-MM-yyyy format and few rows have MM-dd-yyyy format. I want the all the dates to be in dd-MM-yyyy format only.

How do I convert all the rows to dd-MM-yyyy format without using for each row activity.

Please help me, thanks in advance.

2

Hi @0bb4628e217fd43ac86ac9294

How about the following?

image
image790×576 17.8 KB

inputDataTable.AsEnumerable().ToList().ForEach(Sub(r) r("Open Date") = DateTime.ParseExact(r("Open Date").ToString().Trim(), {"dd-MM-yyyy", "MM-dd-yyyy"}, System.Globalization.CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None).ToString("dd-MM-yyyy"))

Regards!

3

@fernando_zuluaga Thank you but I am getting error saying Datetimestyles is not declared

4

Please can anyone help me

5

Hi @0bb4628e217fd43ac86ac9294

Try this:

inputDataTable.AsEnumerable().ToList().ForEach(Sub(r) r("Open Date") = DateTime.ParseExact(r("Open Date").ToString().Trim(), {"dd-MM-yyyy", "MM-dd-yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None).ToString("dd-MM-yyyy"))

Regards

6

Hi @0bb4628e217fd43ac86ac9294

Try again with

inputDataTable.AsEnumerable().ToList().ForEach(Sub(r) r("Open Date") = DateTime.ParseExact(r("Open Date").ToString().Trim(), {"dd-MM-yyyy", "MM-dd-yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None).ToString("dd-MM-yyyy"))

Regards!

7

Hi @0bb4628e217fd43ac86ac9294

=> Read Range Workbook
Output-> dt

=> Use below code in Invoke Code:

inputDataTable.AsEnumerable().ToList().ForEach(Sub(r) r("Open Date") = DateTime.ParseExact(r("Open Date").ToString().Trim(), {"dd-MM-yyyy", "MM-dd-yyyy"}, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None).ToString("dd-MM-yyyy"))

And configure Invoke Arguments like below

image
image994×599 20.6 KB

=> Write Range Workbook dt back to excel.

Regards

8

Thank you but I am getting error exception has been thrown by the target of an invocation.
And when I use the same code in assign activity I am getting error as expression does not produce a value.

9

Hi @0bb4628e217fd43ac86ac9294

Use the below code in Invoke Code:

' Assuming dt is your datatable variable containing the dates

' Loop through each row in the datatable
For Each row As DataRow In dt.Rows
    ' Check if the date format is MM-dd-yyyy
    If DateTime.TryParseExact(row("Open Date").ToString(), "MM-dd-yyyy", System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, Nothing) Then
        ' If it is MM-dd-yyyy, convert it to dd-MM-yyyy
        row("Open Date") = DateTime.ParseExact(row("Open Date").ToString(), "MM-dd-yyyy", System.Globalization.CultureInfo.InvariantCulture).ToString("dd-MM-yyyy")
    End If
Next

Invoke Arguments:

image
image882×256 8.72 KB

Regards

Make sure to place correct column names