DateTime.ParseExact the whole Column without looping

[arthurang]

Hello. Is there anyway i can use DateTime.ParseExact the whole column without looping? i have more than 4000 rows convert the date from dd/MM/yyyy to MM/dd/yyyy. and it takes time for the robot to process with loop.

in my manual process, i used to change the format using Convert Text to Column Wizard.

Thank you.

[arjunshenoy]

Hi @arthurang

Use Invoke Code activity & execute the following code:

dt.AsEnumerable.ToList.ForEach(Sub(row)
row(“datecolumn”) = DateTime.ParseExact(row(“datecolumn”).ToString,“dd/MM/yyyy”, Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)
End Sub)

Hope this helps,
Best Regards.

[Tuannna1 (Tuan Anh)]

Hi @arthurang

Yes, you can use the DataTable 's AsEnumerable method along with LINQ to apply the conversion to the entire column without looping. Here’s an example:

// dt is the DataTable containing the date column
var formattedDt = dt.AsEnumerable()
    .Select(row => {
        var dateString = row.Field<string>("DateColumn");
        var parsedDate = DateTime.ParseExact(dateString, "dd/MM/yyyy", CultureInfo.InvariantCulture);
        row.SetField("DateColumn", parsedDate.ToString("MM/dd/yyyy"));
        return row;
    }).CopyToDataTable();

This code uses LINQ to select each row in the DataTable and apply the conversion to the date column. The CopyToDataTable method is then used to create a new DataTable with the updated values.

Note that this will still iterate over each row in the DataTable, but it will be more efficient than using a traditional loop. Also, keep in mind that this code assumes that all values in the date column can be successfully parsed using the specified format. If there are any invalid values, an exception will be thrown.

[arjunshenoy]

@arthurang

The output using the above query will be like:

Hope this helps,
Best Regards.

[arthurang]

hi @arjunshenoy. thank you for your reply. i tried your solution and however it gives me an error “Invoke code: Exception has been thrown by the target of an invocation.”

My code:

dt.AsEnumerable.ToList.ForEach(Sub(row)
row(“Date”) = DateTime.ParseExact(row(“Date”).ToString,“dd/MM/yyyy”, Globalization.CultureInfo.InvariantCulture).ToString(“MM/dd/yyyy”)
End Sub)

My Arguments:

[arjunshenoy]

@arthurang

Everything looks fine. You gotta make sure that all dates in the column “Dates” are absolutely in “dd/MM/yyyy” format. Please verify the same, let me know if you are still facing the issue.

Best Regards.

[arthurang]

Hi @arjunshenoy unfortunately same error i received. Already check the date and the format is dd/MM/yyyy

[arjunshenoy]

@arthurang

I tried it again with your code version as well & it is working.

I would suggest you to debug the process. Place a breakpoint just after initializing the data table & verify the incoming data format. Please let us know about this.

Best Regards.

[fernando_zuluaga]

Hi @arthurang

Give a try with the following

dtDates.AsEnumerable.ToList.ForEach(Sub(row) row("Column1") = DateTime.ParseExact(row("Column1").ToString,"dd/MM/yyyy", Globalization.CultureInfo.InvariantCulture).ToString("MM/dd/yyyy"))

Regards!

[supermanPunch (Arpan)]

Hi @arthurang ,

If the Target Goal for you is to represent the dates in Excel Sheet Column in MM/dd/yyyy format, maybe you could use the Format Cells Activity if you have the latest versions in place.

You would require to know which column the Date column is present (A or B or C, in my case it is J), then we can use the Range as J:J. If we do not know the position of the column in the Excel sheet too, we can dynamically be able to find it as well.

Input :

Output:

Let us know if this method is also a possibility for your case, if unable to find the activities, Enable the Modern Design Experience from the Project Settings Panel and check if you are able to get the activities.

[Manish540 (Manish Shettigar)]

Hi @arthurang

Can you check this below workflow attached and run it for your records,
Uipath_DateFormatConversion.xaml (9.1 KB)

Hope this might help you

[arthurang]

Hi @fernando_zuluaga thank you for your reply. i tried your sample and it works. i’m not quite sure which part did i make it wrong. apologies, i’m new with rpa. i used Excel Application Scope and Read Range activity with output datatable as dt_Final_Sheet. the i followed @arjunshenoy code which is same as yours in Invoke Code activity. i set my arguments correctly.

[arthurang]

i think it’s working. thank you so much