Issue 102810 - Some dates imported from CSV as text with TZ=America/Sao_Paulo
Summary: Some dates imported from CSV as text with TZ=America/Sao_Paulo
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: OOo 3.0.1
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: oooqa
Depends on:
Reported: 2009-06-15 19:31 UTC by cruzandre
Modified: 2017-05-20 11:33 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Sample data to import in Calc (68.49 KB, text/plain)
2010-06-30 18:17 UTC, cruzandre
no flags Details
The Calc file with the data imported (71.22 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-06-30 18:19 UTC, cruzandre
no flags Details
Calc file after .csv import (without changes) (30.31 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-07-01 19:13 UTC, cruzandre
no flags Details
Screenshot Options Language Settings (59.29 KB, image/png)
2010-07-01 19:14 UTC, cruzandre
no flags Details
Screenshot Text Import Dialog Box (42.29 KB, image/png)
2010-07-01 19:15 UTC, cruzandre
no flags Details
Screenshot Spredsheet with a wrong cell (84.68 KB, image/png)
2010-07-01 19:16 UTC, cruzandre
no flags Details
Screenshot Spredsheet with a test to validate the problem (85.77 KB, image/png)
2010-07-01 19:18 UTC, cruzandre
no flags Details
Screenshot Spredsheet with another way to validate the problem (103.94 KB, image/png)
2010-07-01 19:19 UTC, cruzandre
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description cruzandre 2009-06-15 19:31:11 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:


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.
Comment 1 Rainer Bielefeld 2010-06-27 09:20:03 UTC
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

This is the Issue Tracker system, not a helpdesk! You can get help from the
public users mailing list or on the forum at

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

- 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
Comment 2 Mechtilde 2010-06-28 14:21:31 UTC
@ rainerbielefeld

 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.

Comment 3 Rainer Bielefeld 2010-06-29 04:31:26 UTC
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.
Comment 4 cruzandre 2010-06-30 18:17:22 UTC
Created attachment 70323 [details]
Sample data to import in Calc
Comment 5 cruzandre 2010-06-30 18:19:02 UTC
Created attachment 70324 [details]
The Calc file with the data imported
Comment 6 cruzandre 2010-06-30 18:19:51 UTC
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!
Comment 7 cruzandre 2010-06-30 18:22:53 UTC
I forgot to change the keyword to C4.
Comment 8 Rainer Bielefeld 2010-07-01 06:14:53 UTC
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
I tried with "Ooo-Dev 3.3 multilingual version English UI WIN XP: [DEV300m83
(Build 9511)]" with language settings "English UK", document language setting
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 
   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

Any explications for the strange "MDY" behaviour?

- 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
Comment 9 cruzandre 2010-07-01 19:13:11 UTC
Created attachment 70343 [details]
Calc file after .csv import (without changes)
Comment 10 cruzandre 2010-07-01 19:14:08 UTC
Created attachment 70344 [details]
Screenshot Options Language Settings
Comment 11 cruzandre 2010-07-01 19:15:08 UTC
Created attachment 70345 [details]
Screenshot Text Import Dialog Box
Comment 12 cruzandre 2010-07-01 19:16:54 UTC
Created attachment 70346 [details]
Screenshot Spredsheet with a wrong cell
Comment 13 cruzandre 2010-07-01 19:18:27 UTC
Created attachment 70347 [details]
Screenshot Spredsheet with a test to validate the problem
Comment 14 cruzandre 2010-07-01 19:20:00 UTC
Created attachment 70348 [details]
Screenshot Spredsheet with another way to validate the problem
Comment 15 cruzandre 2010-07-01 19:21:07 UTC
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.

Test environment:
Linux – Fedora Core 13
Windows XP Professional – SP3

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)
another way
2. Checking the result
	Go to line 654
	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.
Comment 16 Rainer Bielefeld 2010-07-02 04:49:58 UTC
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.
Comment 17 Rainer Bielefeld 2010-07-08 06:27:49 UTC
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):

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.

Can you ask on a Portuguese/Brazil user mailing list for help? may be there
someone can reproduce your problem? 
Comment 18 Rainer Bielefeld 2010-07-13 05:52:20 UTC
Did you find anyone else who was able to reproduce your problem?
Comment 19 cruzandre 2010-07-14 15:03:00 UTC

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...
Comment 20 niklas.nebel 2010-07-14 17:48:14 UTC
It depends on the time zone. I see the effect on Linux with TZ=America/Sao_Paulo.
Comment 21 Rainer Bielefeld 2010-07-14 17:57:31 UTC
This one might have same roots like Issue 113028 (only a vague suspect)
Comment 22 ooo 2010-07-14 19:08:42 UTC
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
is sufficient to reproduce.
Comment 23 Marcus 2017-05-20 11:33:39 UTC
Reset assigne to the default "".