Issue 102810

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".