Date converting to US format when using the Use Excel File activity

Help Studio
1

Good afternoon,
i’m having issues with dates converting to US format when I use the Excel File activity to open a .csv file.
If I manually do it without use of UI Path it works perfectly well and retains UK date formatting. This only happens when i’m using the activity within UI Path.
I hope somebody can help as everything else i’ve tried so far doesnt work.
Thanks

2

Hi @martin.budnarowski

can you please provide some samples here, how you are facing issue and what the exact output you need

3

Unfortunately it won’t let me add a csv file, however basically this is an extract :
Completed
01/08/2023 11:05
10/09/2021 11:14
10/09/2021 11:12
20/04/2023 12:29

The date 01/08/2023 is in the format dd/mm/yyyy however when I use the excel file activity to open the csv file it opens it as mm/dd/yyyy
If I manually open this without UiPath then it works in the desired format. Using the format activity or any other method so far keeps it as mm/dd/yyyy
Thanks

4

since its csv file are you able to read the file using read csv file activity.

basically, are you using which activity.

5

I can do but when I do it still reads the dates in as mm/dd/yyyy
Again, no formatting activities will change it to dd/mm/yyyy

6

I have tried both Read CSV and using the open excel activity.
Both fail to give me dd/mm/yyyy

7

You’re right, UiPath’s “Use Excel File” activity might interpret dates in your CSV file according to the system’s default culture (often US format). Here are a few ways to address this and preserve the original UK date format:

1. Use “Read CSV” Activity (Recommended):

  • If your CSV file doesn’t require complex formatting functionalities of Excel, consider using the “Read CSV” activity instead.
  • This activity offers an option to specify the culture for date interpretation.
    • Set the “Culture” property to “en-GB” (or your specific UK culture code) to maintain the original format.

2. Convert Dates After Reading (Workaround):

  • If you must use the “Use Excel File” activity, you can convert the dates back to UK format after reading them:

    • Option A: Using DateTime.ParseExact:

      1. Use a “For Each Row” activity to iterate through the data table.
      2. Inside the loop, for each date column:
        • Convert the cell value to a string using row("DateColumn").ToString().
        • Parse the string back to a DateTime object using DateTime.ParseExact(dateString, **"dd/MM/yyyy"**, CultureInfo.InvariantCulture).
        • Assign the converted date back to the data table cell using row("DateColumn") = convertedDate.

    • Option B: Using Convert.ToDateTime (Less Control):

      • Similar to Option A, iterate through the data table.
      • For each date column, convert the cell value to a DateTime object using Convert.ToDateTime(row("DateColumn")).
      • Note: This method might assume US format for parsing. If you’re unsure about the original format, use Option A for more control.

3. Regional Settings (Less Reliable):

  • As a last resort, you can temporarily change your system’s regional settings to UK format before running the UiPath automation. This approach affects all applications, so consider its implications.

By following these methods, you should be able to preserve the original UK date format when reading CSV files using UiPath, regardless of the activity used. Remember to choose the approach that best suits your needs and coding style.

Note: “dd/MM/yyyy” take this from config file @martin.budnarowski hope this solve your issue

8

Thankyou for your detailed reply.
Unfortunately i’m unable to find the culture property within the Read CSV properties,
Regards.

9

Could you please let me now how i find the culture property?

10

image
image923×492 52.3 KB

11

@martin.budnarowski,

Welcome t the UiPath Community :tada:

You can use Format Cells activity after opening the file and Assign range of your date column & set the Custom format to dd-mm-yyyy

image

Also you can refer this Knowledgbase.

Thanks,
Ashok :slight_smile:

12

in imports panel system.globalization please add. then u will get culture

13

Thanks for your reply. Unfortunately this does not work