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, a .xlsx file in a VB.net language project with compatibility Windows and Modern Experience enabled is used. Readapt it based on internal requirements. It should work for other column formats as well.
- Assume that a .xlsx file is created as below and by right-clicking on a column header, select Format Cells...
Save the File.
- Create in UiPath Studio a new Process.
More details about Project compatibility and Project Language can be found here:
- In the Project -> Project Settings, make sure that the Modern Design Experience is enabled. For Studio versions <24.10.x
Starting with Studio 2024.10, the modern design experience is the only design experience available for all projects.
More details about this feature can be found at the Modern Design Experience.
- 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:
- 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 the Assign activity can be found at:
- Add an Excel Process Scope as below:
Read more details about Excel Process Scope.
- Add a 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 in the desired location (make sure that the UiPath robot has the rights to access the .xlslx folder path).
Read more details about using Excel File.
- 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 the Read Range activity.
- 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 the Write Line activity.
- Add a For Each Excel Row activity and configure it as below,
More details about For Each Excel Row activity.
- 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:
- 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:
- 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:
- 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:
- 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)
- 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:
- 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:
ashokkarale