Problems importing date fields from excel to Entity date fields.

We have to export a target list from mendix to excel, the excel work sheet gets updated and imported back into mendix. The date columns are giving errors when this work sheet is imported back into mendix even if they are not edited. It looks like the time portion of the date is lost when exporting to excel. How would i get around this. The error. com.mendix.core.CoreException: com.mendix.core.CoreException: com.mendix.core.CoreException: Document could not be imported, because: XLS_DATA-2ndPass: an exception occured near row: 2, the exception is: The value in column: 2 is not valid the error message was: java.text.ParseException: Unparseable date: "03/12/2010"
3 answers

I'm actually running into this same issue right now with an application. I understand that there are many notations and it would be difficult to program them all. However, the date format that exports in the standard "Export to Excel" button should be supported.


On the form i exported to excel i showed the date and the time, so when i imported it again the date format was correct and it worked. I will put a rule in excel to force date and time aswell.


Dates in excel are giving a lot of problems.

The trick is to validate your file and check if all your date cells ar marked as cell with the type date to. If excel knows the value is of the type date it can store it properly and it can be imported without any problems.
If excel doesn't know the type of the cell it will be presented to the module as a string.
Because there are a lot of different date notations the module can't assume the notation of the date so the only solution is to throw an exception. If anyone has a suggestion how i can program something to parse all different dateformats please let me know.