Excel dates imported as java.util.Date, not OpenRefine date

[dcabo]

Describe the bug
Date columns from Excel files are imported as text into Refine.

To Reproduce
Steps to reproduce the behavior:

1. Create a new project using an Excel file with dates in it, e.g. the one attached below.
2. The configuration screen for project creation will show the date column in black. Create the project anyway to try to convert it later on.
3. The date is read correctly from the Excel file (December 11th, 2018), but displayed in black, not green, i.e. not recognised as date.
4. Apply Common Transforms > To Date to the "Date" column. The date is still stored as text. See screenshot below.

Current Results
What results occured or were shown.

Expected behavior
A clear and concise description of what you expected to happen or to show.

Screenshots

Desktop (please complete the following information):

• OS: macOS Mojave
• Browser Version: Safari 12.02
• JRE or JDK Version: 1.8.0_181

OpenRefine (please complete the following information):

• Version 3.1 [b90e413]

Datasets
Minimal file: Date test.xlsx

Additional context
This same file worked fine in Refine 2.8.

[ostephens]

I suspect this may not just be an Excel issue but rather an issue with dates starting with a weekday name - I can't get Tue Dec 11 00:00:00 GMT 2018 to parse with toDate() currently (but Dec 11 00:00:00 GMT 2018 works fine)

We need to add a test case for this and then fix the toDate to work

Once this is working we can re-test the Excel import - there may be other issues but I think fixing the date parsing for this form of date is the starting point

[ostephens]

OK - I may need to revise my first opinion as @ettorerizza has pointed out in #1287 that he can get dates of this form to parse OK in 3.1

I'll investigate further

[ettorerizza]

Yes, I confirm that Tue Dec 11 00:00:00 GMT 2018 can be parsed with value.toDate("EEE MMM dd h:m:s z yyyy"), at least in OR 3.1.

[ostephens]

It looks like this issue is specifically connected with the import from Excel. If I use the supplied xlsx file from above then after import toDate() does not work. If apply a transform to the cell with just value (i.e. should be no change) then apply toDate() this then works.

This suggests there is something about the cell content after the import that is causing a problem

[thadguidry]

@ostephens what about other importer formats (.xls .ods) and not just .xslx?

[ettorerizza]

@ostephens Looks like the problem with the Excel file comes from the blank spaces between each part of the date-time string, since you can parse it using this Grel formula :

value.replace("\p{Blank}", " ").toDate("EEE MMM dd h:m:s z yyyy")

Edit

This one works too :

value.toString().toDate("EEE MMM dd h:m:s z yyyy")

When directly imported from Excel, the date "string" is not really a string, but a java.util.Date object. The difference is indistinguishable to the naked eye.

[msaby]

Still an issue in 3.3

[thadguidry]

Maybe this issue is pertaining to if dateCompatibility attribute is defined in the original .xlsx ? (not sure what POI is doing with this)
Can you look with Notepad or text editor for that attribute in the .xlsx file?

I found this note in https://interoperability.blob.core.windows.net/files/MS-OI29500/[MS-OI29500].pdf#page=247

The standard states that the dateCompatibility attribute determines whether the date base should be treated as a compatibility date base or should support the full date range of [ISO-8601]. Office ignores the dateCompatibility attribute. If workbook@conformance equals "strict", the 1900 date system is used. Otherwise the 1900 compatibility date system is used. Excel does not support negative serial numbers. This note applies to the following products: Office 2013 Client (Strict), Office 2013 Server (Strict), Office 2013 Client (Transitional), Office 2013 Server (Transitional).

[ettorerizza]

When importing the attached Excel file, OR automatically changes 00:00:00 to Sun Dec 31 00:00:00 CET 1899 and 2020 to 2020.0 (visible only in facets). Retransforming the time to 00:00:00 is not trivial, since the GREL formula value.toString().toDate("EEE MMM dd h:m:s z yyyy").toString("hh:mm:ss") can return 11:00:00 and not 00:00:00 according to the user's locale.

See for example this post on StackOverflow.

IMHO, this issue should be prioritized as Excel is the reference tool for most new users of OR. This kind of hard to fix bug can discourage them from going further.

In the meantime, I suggest advising users faced with the problem to export their Excel file in CSV. It seems that a .ODS export can't be opened in OpenRefine anymore (but this is another bug).

Classeur1.xlsx

[wetneb]

Yeah, yet another reason to just import these things as plain strings, I would say.