Apache OpenOffice (AOO) Bugzilla – Issue 102810
Some dates imported from CSV as text with TZ=America/Sao_Paulo
Last modified: 2017-05-20 11:33:39 UTC
When you import a CSV file with some dates in MM/DD/YYYY format and you chose de DD/MM/YYYY format, the Cal mistake the import in some october dates like: 10/14/2001 10/19/2003 10/16/2005 10/15/2006 10/21/2007 10/19/2008 10/18/2009 10/17/2010 10/16/2011 10/21/2012 10/20/2013 10/19/2014 10/18/2015 To reproduce the problem, create a text file with a date list in MM/DD/YYYY format, save the file with .csv extension and then open it in Calc. When text import assistent open, select the colun and set the colun type as DD/MM/YYYY date format. I tested all dates between 01/01/2000 and 12/31/2015 and I got a wrong result in the list above, when the Calc misinterpretation the dates as a text. I think that the problem occur only in october month, buy as I don't check the font code, I don't have sure about this.
Observed effect is reproducible with "Ooo 3.1.1 WIN XP DE[OOO310m19 (Build 9420)]", but that's not unexpected, for this localization "MM/DD/YYYY" is not a valid date formatting. If I select Column formatting "Date (MDY)" in import dialog, Date number information will be correct. So it seems to be some user error, ==> INVALID @cruzandre: This is the Issue Tracker system, not a helpdesk! You can get help from the public users mailing list users@openoffice.org or on the forum at <http://user.services.openoffice.org/en/forum/> Pls. feel free to reopen the issue if discussion with other users shows that there is a real problem in a current stable version (3.1.1 or later). May I ask you to read our guidelines on <http://qa.openoffice.org/issue_handling/pre_submission.html> and <http://www.openoffice.org/bugs/bug_writing_guidelines.html> before you file further issues or post again here? Then please contribute a clear step by step instruction containing all observations (error messages ...), EVERY key press and EVERY mouse click how to reproduce the problem, and explain why you believe that your results are unexpected. That means (for example): do not write something like "I am not able to ...", but 6. left mouse click on … expected: …, color of … changes, … actual: no …., color remains white, no … Screenshots might help to explain the problem. Please - always specify your OOo download source, version, LOCALIZATION, locale and language settings, OS and Platform you used for your tests! - attach a sample document (always with ODF source document)! - Always contribute a meaningful Summary
@ rainerbielefeld Please: If you set an issue as duplicate, wontfix, worksforme or invalid, please also close the issue Only a closed issue is a good issue ;-) It wastes a lot of time to close all such issues. thanks
@mechtilde: There is no need to touch issues set to RESOLVED by me if you do not want to contribute additional information. I will close them within 1-2 weeks. During this time additional information (that might show that the issue should not be CLOSED) can be added.
Created attachment 70323 [details] Sample data to import in Calc
Created attachment 70324 [details] The Calc file with the data imported
I have the impression that Mr. “mechtilde” didn't know the difference between a question and a bug. The Bug still exists in OOO321m12 (Build: 9483) version. I think that the informations below will help Mr. “rainerbielefeld” to understand more about this bug. My Calc have the “Locale Setting” configured to Portuguese (Brazil), where the date have the format DD/MM/YYYY (now you understand, mechtilde?). The list of dates that I try to import, by .csv file, is in the DD/MM/YYYY format. Therefore that I use the column format “Date (MDY)” and the Calc do as I expected for all dates, except the dates in October month. The Cal import the .csv file in format MM/DD/YYYY, converts the text to date internal format and shows the cells in DD/MM/YYYY format as expected, except some dates in October month, in distinguished years. Mr. “rainerbielefeld” if you can help me to fix this bug I'll appreciate it so much. If Mr. “mechtilde” wants help too, he can help me telling the specific classes that the Calc uses to the text import, mainly 'Date Formats', and not closing the issue!
I forgot to change the keyword to C4.
Still not reproducible in all details, but may be I found some first indicatons that there might be a problem and the column number type selector does not work well. I tried with "Ooo-Dev 3.3 multilingual version English UI WIN XP: [DEV300m83 (Build 9511)]" with language settings "English UK", document language setting "German": 0. Menu 'File - Open - Type "csv"' - <open> 1. Text import dialog: Chacacter set "Iso-8859-15 - German - From Row 1 - Separated by nothing - Text delimiter=', All "other options" selected Column marked, Column Type "MDY" selected <okY Document will be imported 2. Select column B Format "D MMMM YYYY" Select 'B3', Inser formula "=A3" <enter> Result: "2 January 2000" expected: "1 February 2000" due to "MDY" 3. Select column B Format "D MMMM YYYY" Select 'B359', Inser formula "=A359" <enter> expected: "1 February 2000" due to "MDY" actual: 23 December 2000 expected: Text "23/12/00", because "23/12/00" is not a valid "MDY" date. May be that there is some unexpected Column type selection override, that causes cruzandre's problems? Some more results: Import with "DMY" seems to work corrrectly, "01/13/2000" 2ill be imported as Text as expected. Some other modifications (character set) of the settings in import dialog seem to be without influence @all: Any explications for the strange "MDY" behaviour? @cruzandre: Please: - Contribute the imported .csv as "dates1.ods" without any further modifications - A step by step instruction containing every mouse click and every key press - Information concerning all settings in "Options" and Import dialog that might be relevant - do not touch IZ status fields if you do not know exactly for what they are
Created attachment 70343 [details] Calc file after .csv import (without changes)
Created attachment 70344 [details] Screenshot Options Language Settings
Created attachment 70345 [details] Screenshot Text Import Dialog Box
Created attachment 70346 [details] Screenshot Spredsheet with a wrong cell
Created attachment 70347 [details] Screenshot Spredsheet with a test to validate the problem
Created attachment 70348 [details] Screenshot Spredsheet with another way to validate the problem
@rainerbielefeld: Rainerbielefeld, thanks a lot 4 your help. But your test in lines B3 and B359 return the correct values. Remember that you local setting is configured to show dates in format DD.MM.YY, so the result expected in cell B3 is 2 January 2000 and your test returns it well. The import module should convert the format MM/DD/YYYY of the .csv file in internal date/time Calc format. In this case (cell B3 and B359), Calc did it. Please, check the lines 276 to 306 and tell me if they correspond of October month. After that, check the lines of the October month of other years and verify if all dates are as expected. @all: Test environment: OS Linux – Fedora Core 13 Windows XP Professional – SP3 OO OOO320m12 (Build: 9483) – English (US) OOO300m15 (Build: 9379) – English (US) The problem occur in all of 4 combinations and compilations above. To reproduce the problem: 0. Main language settings (Screenshot-Options-Language Settings-Languages.png) Menu Tools, Options, Language settings Language of session User Interface: Default - English (USA) Locale Setting: Portuguese (Brazil) Decimal Separator Key: (Checked) Same as local setting (,) - “comma” Default Currency: BRL R$ Portuguese (Brazil) Default Language for Documents Session Western: Portuguese (Brazil) 1. Opening file Menu File, Open and select dates.csv file Default options Character set: Unicode (UTF-8) From row 1 Separated by comma (checked) and all others options unchecked Text delimiter '“' (quotes) Select single column Column type: Date (MDY) (Screenshot-TextImport-[dates.csv].png) (dates1.ods) another way 2. Checking the result Go to line 654 (Screenshot-dates.csv-OpenOffice.orgCalc.png) The A654 cell has a text, not a date/time content, in format MM/DD/YYYY In cell B654 put the formula '=A654+1' and the result will be '#VALUE!' Do the same in cell B655 (=A655+1) and the result will be '16/10/01' The same problem occur in lines 654, 1389, 2117, 2845, 3216, 3580, 3944, 4308, 4679, 5043, 5407 and 5771. Another way to test is sort the column A descending. It'll put the text cells above the dates cells. In line of the test suggested by 'rainerbielefeld', I fill the column B with the formula =A1, =A2, … , =A5845. The column was format as date (D. MMM. YYYY) and in line 283, the result is 10. Ago (Aug). 2000 and the expected was 08. Out (Oct). 2000. (Screenshot-dates.csv-OpenOffice.orgCalc-2.png)
@cruzandre: Thank you for additional information. Today I am too busy, I will do several further tests during the weekend. I saw the "October-Effect" in your "dates1.ods", but I will have to check influence of locale settings and other additioonal aspects.
In "dates1.ods" I see mistaken dates: 283: 08/10/00 should be 10/08/00 654: 10/14/2001 should be 14/10/01 (is text instead of No.!) 1039: 03/11/02 should be 11/03/02 1389: 10/19/2003 should be 10/19/03 (is text instead of No.!) 1769: 02/11/04 should be 11/02/04 2117: 10/16/2005 should be 10/15/05 (is text instead of No.!) 2502: 05/11/06 should be 11/05/06 2845: 10/14/2007 should be 10/14/07 (is text instead of No.!) 3216: 10/19/2008 should be 10/19/08 (is text instead of No.!) 3580: 10/18/2009 should be 10/18/09 (is text instead of No.!) and further wrong dates (all text instead of date): 10/17/2010 10/16/2011 10/21/2012 10/20/2013 10/19/2014 10/18/2015 I have some difficulties to find a pattern. And I can't reproduce that problem with "Ooo-Dev 3.3 multilingual version English UI WIN XP: [DEV300m84 (Build 9512)]" and "dates.csv", import works fine for me. @cruzandre: Can you ask on a Portuguese/Brazil user mailing list for help? may be there someone can reproduce your problem?
@cruzandre: Did you find anyone else who was able to reproduce your problem?
@rainerbielefeld: Yes, I know. Many people that use OO in my office can reproduce the problem but nobody knows OO sufficiently to work in code to solve this issue. I did some tests in different OO versions and in different platforms (Windows, Linux and Mac). I did tests changing the 'Locale Setting' also and I saw the 'October effect' in German, English (UK) and Italian for example. So, I don't know which way I should follow to solve this problem...
It depends on the time zone. I see the effect on Linux with TZ=America/Sao_Paulo.
This one might have same roots like Issue 113028 (only a vague suspect)
I don't see the effect in OOo3.2.1, but I can reproduce it in DEV300_m84. Strangely enough when editing such textual date cell and removing the ' apostrophe text indicator, the date is accepted. A single date in the .csv file 10/14/2001 is sufficient to reproduce.
Reset assigne to the default "issues@openoffice.apache.org".