Hi, my source document is a CSV file but has a file format of “Filename.csv.yyyymmdd”, so it cannot be read by read workbook or excel application scope. I’m currently asking the robot to open the file with excel and uses Excel’s “text to column” function to delimit it with comma to get my datatable.
I have a column which usually would contain a date in mm\dd\yyyy format. Usually, excel would not be able to recognize it (because my machine is in dd\mm\yyyy format), and thus when it does “text to column” delimitting, the format of the column would be in General format.I then use a system.globalization.invariantculture to let robot know that the current cell is in mm\dd\yyyy format for future conversions.
Recently Im facing an issue when excel manage to recognize that cell as date and thus changed the cell content to “dd\mm\yyyy” format, but if it does, my invariantculture would be wrong. And I can’t change my machine’s date format, so is there anyway where I can determine the cell format before i decide which format I should use each time?
To better explain, consider this example:
Scenario 1: Date in column : “06/24/2018”, Excel tries to read it using machine’s dateformat (dd/mm/yyyy), does not recognize 24 as month, keep cell format as General. Robot later uses system.globalization.invariantculture to read cell with provided format “mm/dd/yyyy”
Scenario 2 (Error): Date in column: “06/05/2018” 5 June 2018, Excel manages to recognize 05 as month, change cell format to Date thus becomes 05/06/2018 6 May 2018. When robot uses system.globalization.invariantculture with “mm/dd/yyyy” it would recognize 05 as month instead.
So, I am wondering if it is possible to get cell format of the cell, if it is in general format, I would provide “mm/dd/yyyy” as format; if it is not, then “dd/mm/yyyy”