Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error importing date from ODS file #2224

Closed
msaby opened this issue Nov 20, 2019 · 4 comments · Fixed by #2843
Closed

Error importing date from ODS file #2224

msaby opened this issue Nov 20, 2019 · 4 comments · Fixed by #2843
Assignees
Labels
import About importers in general - add a label for the data format if available ODS Import/export from/to LibreOffice spreadsheets Type: Bug Issues related to software defects or unexpected behavior, which require resolution.
Milestone

Comments

@msaby
Copy link
Member

msaby commented Nov 20, 2019

Describe the bug

A date in a cell in a ODS file is imported as a "java.util.GregorianCalendar" and displayed as

 java.util.GregorianCalendar[time=1546297200000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="Europe/Paris",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=184,lastRule=java.util.SimpleTimeZone[id=Europe/Paris,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]],firstDayOfWeek=2,minimalDaysInFirstWeek=4,ERA=1,YEAR=2019,MONTH=0,WEEK_OF_YEAR=1,WEEK_OF_MONTH=1,DAY_OF_MONTH=1,DAY_OF_YEAR=1,DAY_OF_WEEK=3,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=0,SECOND=0,MILLISECOND=0,ZONE_OFFSET=3600000,DST_OFFSET=0] |  

To Reproduce

Create a ODS file with Libreoffice

Put a date in a cell (like 1/1/2000)

Import into openrefine

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
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

  • OS: Win10
  • Browser Version: Chrome
  • JRE or JDK Version: 1.8.0.231

OpenRefine (please complete the following information):

  • Version : OpenRefine 3.3 Beta

Datasets
If you are allowed and are OK with making your data public, it would be awesome if you can include or attach the data causing the issue or a URL pointing to where the data is.
If you are concerned about keeping your data private, ping us on our mailing list

Additional context
Add any other context about the problem here.

@wetneb wetneb added Type: Bug Issues related to software defects or unexpected behavior, which require resolution. ODS Import/export from/to LibreOffice spreadsheets labels Nov 20, 2019
wetneb added a commit that referenced this issue Dec 12, 2019
@wetneb wetneb added the pull request pending For issues that will be fixed by an open PR label Dec 12, 2019
@wetneb wetneb added this to the 3.3 milestone Dec 12, 2019
@wetneb
Copy link
Member

wetneb commented Dec 20, 2019

Related: #533 proposes to add support for storing dates (without time) in cells.

@wetneb wetneb added the import About importers in general - add a label for the data format if available label Dec 22, 2019
@wetneb wetneb modified the milestones: 3.3, 3.4 Jan 31, 2020
@wetneb wetneb removed this from the 3.4 milestone May 30, 2020
@wetneb
Copy link
Member

wetneb commented May 30, 2020

Clearing milestone since PR is abandoned

@wetneb wetneb removed the pull request pending For issues that will be fixed by an open PR label May 30, 2020
@tfmorris
Copy link
Member

This likely has the same root cause as #1908 and is another regression which was introduced by the Java 8 Date/Time changes in OpenRefine 3.0, as described in #1908 (comment).

@tfmorris tfmorris self-assigned this Jun 29, 2020
tfmorris added a commit to tfmorris/OpenRefine that referenced this issue Jun 29, 2020
tfmorris added a commit to tfmorris/OpenRefine that referenced this issue Jun 30, 2020
tfmorris added a commit to tfmorris/OpenRefine that referenced this issue Jul 3, 2020
wetneb pushed a commit that referenced this issue Jul 4, 2020
* Truncate any completely empty columns on the right

Fixes #565
The current versions of Open Office create default spreadsheets
with over 1000 empty columns. Keep track of the rightmost
non-empty column when importing and truncate everything else.

Also adds a basic ODS import test.

* Fix dates in ODS spreadsheets

Fixes #2224
@wetneb wetneb added this to the 3.5 milestone Jul 4, 2020
@tfmorris
Copy link
Member

tfmorris commented Aug 9, 2020

Although I changed this to an OffsetDateTime, which is what OpenRefine currently uses internally, using LocalDateTime might be more appropriate since OpenOffice Calc doesn't have the concept of timezones.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
import About importers in general - add a label for the data format if available ODS Import/export from/to LibreOffice spreadsheets Type: Bug Issues related to software defects or unexpected behavior, which require resolution.
Projects
None yet
3 participants