I have used many For Each Excel Row activities, without problem. But for the specific sheet, I am seeing strange behaviour where the first character of a cell is missing. The cells in that column contain a date (see pic). As you can see in the Debug panel, the 0 of the date (first character) is missing when read by UIPath. This does not occur in the next column, where the number is read correctly. Any idea? Thanks!
Can you share the sample excel file
It appears correct to me.
Read the file using the read range activity. In the intermediate panel view the whole data table
How are you obtaining the input date variable
Even if preserve formatting is used
I just have a For Each Excel Row and just assign the value of the Date cell to a String variable named InputDate. I am not creating a DT variable.
Interestingly, in the data table you have created the date appears as 01/09/22 and not 01-SEP-22 as it does in the Excel spreadsheet. I have a feeling it is an issue with formatting of the data that I copy into the Excel spreadsheet in the first place. I want this date to be pure text, but for some reason Excel does not see it as such (which is why, in the spreadsheet, you can see that the value of the cell appears to be 01-sep-22, but when you click on the cell its value shows as 01-09-22 in the formula bar. Very frustrating…
Thank you very much @kumar.varun2. That workaround works well indeed, by using the value from datable, as opposed to Excel row, and transforming it to the desired format.
I will also post here the solution I have found by playing with the format of the dates in the spreadsheet. It turns out that the copied date values (from a third party app) get automatically turned into a date format as the Paste function defaults to the Excel “General” format (Excel just recognises the value as a date and changes the format to Date all by itself). This explains why the Formula bar shows 01/09/22, whereas on the sheet itself it shows as 01-sep-22.
The way the data was copied into the spreadsheet was using the Text To Columns function in Excel. When I used the wizzard, it defaulted to a value of General for the format of all columns. Instead, in the third step of the wizard, I selected Text for the Date column, instead of General. Now the dates show as 01-sep-22 both in the cell and the formula bar, and my Robot works correctly.
Thank you for your time!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.