Hi,
To know the full story of the problem please read the “Side Info about this process” at below.
2 days ago we notice when the robot read range from excel, it convert the column with date format “dd/MM/yyyy” to “MM/dd/yyyy”. In some cases, the excel column’s format is also one of the contributions of this cause.
Scenario 1:
The column is being filled up in the correct format “dd.MM.yyyy”. The read range gives a translated column into “MM/dd/yyyy”. I had written a invoke code to overcome this, but this code couldn’t solve scenario 2.
The code will check first cell of the column whether the date is in “MM/dd/yyyy hh:mm:ss” then it will convert the whole column in to “dd/MM/yyyy”
If Date.TryParseExact(DTSheet1.Rows(0).Item(1).ToString,“MM/dd/yyyy hh:mm:ss”,Nothing,Globalization.DateTimeStyles.None,Nothing).ToString=“True” Then
DTSheet1.Select(“Column1 NOT is Null”).ToList().ForEach(Sub(row) row(“Column1”)=Convert.ToDateTime(row(“Column1”)).ToString(“dd/MM/yyyy”))
End if
Scenario 2:
The column is being filled up in the correct format “dd/MM/yyyy”. However, the read range gives a different result mixture of “dd/MM/yyyy” and “MM/dd/yyyy”.
I need help on code like below by using invoke code, and not to remove blank rows in between, because it has meaning when we upload into SAP.
For each row in DT,
If row(“column1”) is Null then
Goto Nextrow
Elseif row(“column1”) is “MM/dd/yyyy” then
change to “dd/MM/yyyy”
Elseif row(“column1”) is “dd/MM/yyyy” then
Goto Nextrow
End if
Nextrow:
Next row
Here is the file for your testing.
working_template.xlsx (204.8 KB)
Side Info about this process:
I have a process where users filled up an excel template and submit it through email then the robot will read the attachment, format it to the correct layout and upload it into SAP. This robot process has run for about a year.
- No changes in Virtual Machine Date and Time and the UiPath Studio (last Oct is the latest update of the UiPath Studio)
- Latest update of this process was 10 days ago, but things get weird on 4 days ago.
- Orchestrator version wasn’t upgraded since last year
Thank you in Advance