Preserve Format in the Read Range Activity is making the iteration slow

So I have the Date format YYYY-MM-DD (example: 2020-03-01) in my excel file.

When i use the Read Range it converts the date to the format to DD-MM-YYYY(example: 01-03-2020)

I need it to stay in the format YYYY-MM-DD, but the problem is when I use the “Preserve Format” tool it gets really slow because it reads it cell-by-cell.

Why does it convert it in the first place?

Is there any faster way to keep the format I want?

Hi @atomic,

Read the excel the way it is and convert the format using the following method
Create an array variable and assign following values into it
{“dd-MM-yyyy”,“dd/MM/yyyy”,“dd/MM/yy”,“dd.MM.yy”,“dd.MM.yyyy”}

DateTime.ParseExact(row(0).ToString, arr_DateFormat, new CultureInfo(“en-US”), DateTimeStyles.None).ToString(“dd-MM-yyyy”)

image

above method to convert date into a single format in comes through in the various format
to use above method you need to add following namespace to uipath otherwise it wont recognize culture and style info

System.Globalization

or you can use the easiest method just

DateTime.Parse(row(0).ToString).ToString(“yyyy-MM-dd”)

Assumption:- row(0).ToString is the value of datatable date value you get from excel

Happy Automation

2 Likes

Thank you.

I assigned “arr_DateFormat” the variable type"String[]" is this correct?

image

I get this Error, I dont use this inside of a for each row activity. The last part .ToString("dd-MM-yyyy")
is that correct or will i get the wrong output in the new datatable?

in toString("This the pattern of date you need to change that in your format ")
and these assigns need to place in the Iteration means fore each row loop otherwise you can’t access the row value

How do I make it so that it gets converted into a Datatable again. I need to iterate through DT_utförare first and then for each row convert it the date to the format yyyy-MM-dd

Also do i need to add “yyyy-MM-dd” to the arr_DateFormat variable?
And will the “en-US” make it so “yyyy-MM-dd” is used?

you need to assign that value into Data table again ?
coz in here buddy you assigning convert date String into a Data Table

Exactly i have many columns. One of them are a Date column with many rows.
I need to make so the format is in “yyyy-MM-dd”. The Preserve format does the trick but is slow as i mentioned above.

What you are going to do by reading data table ?

I need to filter out specific columns then in another sequence i am doing some calculations based on the filtered columns.

just you want to filter out data from a Data table right same time you want to convert date right

Example of my excel file:
image

When i use the read range without “Preserve Date” selected the output for the “Date” column becomes “dd-MM-yyyy”. I just want it to stay the same when I output it into a datable activity. I can use the “Preserve format” but it is slow.

Ok then use an assign activity and convert date into ur format using DateTime.Parse(The date comes from excel).ToString(“Date Type format u want to convert(YYYY-MM-DD)”) and assign that values into a new String variable that’s it

1 Like