Issue 24671 - Text string such as 1/5 becomes date from text data source
Summary: Text string such as 1/5 becomes date from text data source
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 1.1
Hardware: All Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2004-01-22 07:56 UTC by kelvine
Modified: 2013-08-07 15:45 UTC (History)
4 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description kelvine 2004-01-22 07:56:22 UTC

Today I was trying to provide a user with access to a pipe delimited file with 
the following type of format.

1/5|Smith Street|Melbourne|3000

The 1/5 is typically used in real estate to represent Unit 1, Number 5 of 
Smith Street. The number is often kept separate in real estate applications to 
aid sorting on the street then number.

There appears to be no way to tell the data source to treat the column as a 
text string. It is treated as a date in OOo text data sources.

I have fallen over this trap a couple of times. In all cases I resort back to 
Microsoft products to work around the product which is a pity.

In case the response is to use a different output format in the product that 
produces the text file, this is not always possible or desirable.

Anyway I thought I would let you guys know what I come across in the real 
world application of OOo and text data sources.


Comment 1 marc.neumann 2004-01-22 10:51:48 UTC

yes I can reproduce this. However this is not realy a defect, because the 1/4 is
indicate correctly as date. The competitor do the same, but they can change the
format in the import wizard. The default for such a field is date. 
So we need also such a wizard for importing text files.

Set target and send to the right developer.

Bye Marc
Comment 2 rblackeagle 2004-01-22 18:40:51 UTC
I note that this problem with automatic conversion to dates being extremely
difficult for even an experienced user to correct crops up repeatedly.  Why not
take a hint from MS Word and Excel and have a much simpler way to prevent the
conversion to a date?  At least then we'd only be having problems with
inexperienced users.
Comment 3 kelvine 2004-01-22 23:57:29 UTC

I just thought I would add some more comments as I don't wan't this issue to 
get confused with the general import issues.

The issue was raised as a Database Access issue as this is about direct access 
to an existing datasource and not about importing the data.

With importing it is possible to assign a data type to a column, but that is 
not what we are doing in this situation.

With a text file as a data source there is no ability in to 
treat this fairly common type of text field and have it correctly available 
through data sources (without of course having the application generate a 
different format for the text file or using other technologies).

Each time I have hit this problem I have simply resorted to Microsoft 
technologies to work around this issue.

If the text file contains row headers as in the following:

1/5|Smith Street|Melbourne|3000|Australia|Brick Veneer

then MS Word can handle this 100% correctly with no additional effort from the 
user, whereas cannot handle it correctly and still forces the 
PropertyNumber to become a date field.

Somehow I think there should be an option under the Text Tab in the Data 
Source screen which simply allows dates to be treated as text.

I agree that since OOo can't currently handle this type of data correctly it 
could be considered an enhancement request. Just as equally given that it 
handles the value in the field incorrectly it could be considered a bug.

I hope this further clarification is of use.

Comment 4 Frank Schönheit 2004-01-23 08:50:29 UTC
I agree that this is a pretty annoying behavior with no good workaround at all.

off topic side note: A "Bug" is something where the behavior does not match the
specification - this is not the case here. It's specified to "automagically"
recognize dates (which undoubtly implies bad consequences such as this one here).
Comment 5 kelvine 2004-01-24 23:36:41 UTC

Thanks for the side note.

I appreciate the difference between a bug and a feature. I felt it was just 
better to ensure it was seen as a data source issue which had affected me and 
not a text import issue, which OOo provides methods to handle.

I also appreciate this is not an easy issue due to the number of combinations 
with which things occur. A user may want one column not to be interpretted 
automatically and another column to be interpretted automatically.

My first thought is to suggest an option to turn off recognition of date like 
values. I don't think I can recall a system which exported a text file with 
dates that were date like.  That is, most text files produced from other 
systems would have a fully formatted date such as 'dd/mm/yyyy', 'mm/dd/yyyy' 
or the numerous other variations.

None that I have come across export a date such as 'dd/mm', but that is not to 
say it can't be done. If the user manually creates the text file it is very 
easy to do. If they use a format function it is also easy to do. If the 
application allows just exporting the date without the year it would be 
possible too.

So my first suggestion is not comprehensive enough to cover variations. My 
second suggestion is some map of columns and the column data types. (Providing 
a map with data types and column headings would give the greatest flexibility.)

Yet what I like about the text data source is the simplicity. I don't have to 
worry about how many columns there are and if I add a new column to the 
output, the data source still works.

The data source without a heading record like the one provided is handled 
better in than it is in MS Word and to me that is a winning 
feature. (I believe MS Word can handle such a data source, but given that I 
cannot easily work out how, I doubt many users would find it easy either.)

Again I am only providing additional feedback from a consultants perspective 
working with ends users. In this case the user does use MS Word as their main 
product. In this area Writer/data sources is easier to use and thus they are 
happy to use both Writer and Word.

A final comment is that had there been a header row in this text file, MS Word 
would handle the text file correctly from our perspective and that would have 
meant there would be no need for at all.

Again I hope this further feedback helps.


Comment 6 Frank Schönheit 2004-01-26 08:46:21 UTC
The "automagic" recognition of the column type is done with the number
formatter. Is there any way to tell the formatter to be less generous when
recognizing dates?
I.e., can we tell the number formatter to *not* recognize incomplete dates such
as "1/5", but only complete ones such as "1/5/2003"?
Or, is there a possibility to define precedences? Finally, the number formatter
should also recognize the fraction, since it knows a number of formattings for
fractions. It just seems it *first* matches the date format, though this is a
worse match than the fraction format which it perhaps would find later on.
Comment 7 ooo 2004-01-26 10:39:24 UTC
Hi Frank,

1/5 isn't necessarily an invalid incomplete date, at least not in en_US, and
maybe other locales too. They do write 1/5 and expect it to transform to
1/5/2004. In de_DE locale a 5.1. (note the second trailing separator) would be
similar, but then again the notation clashes with numbered list items. We'd have
to specify behavior for each locale to solve that, which could end up in quite a
mess.. A more general way could be setting an option at the number formatter
whether to recognize incomplete dates. But that would be yet another option in
the configuration forest, it could have even different settings for different
applications.. do we want that? The easiest would of  course be "don't recognize
any incomplete date whatsoever", but I don't know how many users would complain
if they couldn't enter a date as 1/5 anymore. I leave this up to the User
Experience department.

Regarding the fraction thingy, I don't think the number formatter should
recognize them. Users already have problems with the recognition of 1/5 as a
date, why should we extend that?

Comment 8 Frank Schönheit 2004-01-26 14:15:21 UTC
Eike, thanks for the clarifications. I think we should consider a
per-data-source or per-column or whatsoever setting then for our particular
problem ...

(for the fraction: I simply assumed the formatted recognizes this, since it's
also a valid output format ... never mind :)
Comment 9 hans_werner67 2004-02-02 12:10:51 UTC
change subcomponent to 'none'
Comment 10 Rainer Bielefeld 2004-10-10 18:59:39 UTC
I agree with Additional comments from clu Tue Sep 14 01:39:18 -0700 2004. A RFE
is the best way.

Comment 11 bettina.haberer 2010-05-21 14:58:57 UTC
To grep the issues easier via "requirements" I put the issues currently lying on
my owner to the owner "requirements".