How To Get Dates, Times And Custom Format Columns From Excel In The Right Format With UiPath Studio?

News Knowledge Base
, ,
1

How to obtain Dates, Times and Custom format columns from Excel in the right format with UiPath Studio?

Issue Description: While attempting to automate in UiPath Studio, the reading of Excel rows data, the Cell format is not retained. Thus how to get Dates, Times, and Custom format columns from Excel in the right format with UiPath Studio?

Resolution:

Note: In the below example we will use a .xlsx file in a VB.net language project with compatibility Windows-Legacy and Modern Experience enabled. Readapt it based on internal requirements. It should work for other column formats as well.

  1. Assume that a .xlsx file is created as below and by right-clicking on a column header, select Format Cells...

Save the File.

  1. Create in UiPath Studio a new Process.

More details about Project compatibility and Project Language can be found here:

  1. In the Project -> Project Settings, make sure that the Modern Design Experience is enabled.

More details about this feature can be found at the Modern Design Experience .

  1. Double click on the Main.xaml and add a Write Line activity at the beginning of this sequence that will display the Process start time.

"Process started at: " + DateTime.Now.ToString("hh:mm:ss tt MM/dd/yyyy zzz")


The results for this activity will look as below:

  1. Create a System.TimeSpan variable using the Assign activity that will hold the start time value of this process.

System.DateTime.Now.TimeOfDay

  • Go to Variables and make sure that the startTime variable is set to be a System.TimeSpan variable type

Results:

More details about Assign activity can be found at

  1. Add an Excel Process Scope as below,

Read more details about Excel Process Scope .

  1. Add an Use Excel File as below ( specify its folder path in the Workbook path):


In this example, insert the .xslx file in the same project path, but place it at desired location (make sure that the UiPath robot has the rights to access the .xlslx folder path).

Read more details about Use Excel File .

  1. Add a Read Range activity and configure it as below,

  • Create a new variable (Ctrl + k) named for example as in_DT in the Save to.

Read more details about Read Range activity.

  1. Add Write Line that will display the rows and column count.

  • String.Format("Total number of rows: {0} and Total number of columns: {1}", in_DT.RowCount.ToString, in_DT.ColumnCount.ToString)

The results for this activity will look like below,

More details about Write Line activity .

  1. Add a For Each Excel Row activity and configure it as below,

More details about For Each Excel Row activity .

  1. Add a Write Line that will display the current index of the row.
  • "Row: " + CurrentIndex.ToString

The results for this activity will look something like this:

  1. Add a Write Line that will display the Date cell format.

  • "Date column format: " + CurrentRow.Item("Date").ToString

The results for this activity will look something like this:

  1. Add a Write Line that will display the Time cell format.

  • "Time column format: " + CurrentRow.Item("Time")

The results for this activity will look something like this:

  1. Add a Write Line that will display the custom Date and cell format.

  • "Custom Date and Time column format: " + CurrentRow.Item("Date and Time")

The results for this activity will look something like this:

  1. Add an Assign activity that will allow you to create a System.TimeSpan type variable named for example endTime. This variable will hold the end time value of this process. It's similar to step 5.

  • System.DateTime.Now.TimeOfDay.Subtract(startTime)

  1. Add a Write Line that will display the Process end time.

  • "Process finished at: " + DateTime.Now.ToString("hh:mm:ss tt MM/dd/yyyy zzz")

The results for this activity will look something like this:

  1. Add a Write Line that will display the process duration in seconds.

  • "The process took: " + String.Format("{0:N3} seconds", endTime.TotalSeconds)

The results for this activity will look something like this:

image.png