Issue 80371 - copy table wizard: allow to specify a null date for columns where non-date values are imported as date values
Summary: copy table wizard: allow to specify a null date for columns where non-date va...
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 2.2.1
Hardware: PC Windows XP
: P4 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Keywords: needmoreinfo, oooqa
Depends on:
Blocks: 20748
  Show dependency tree
Reported: 2007-08-06 03:46 UTC by dreadnought1906
Modified: 2013-08-07 15:45 UTC (History)
2 users (show)

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

Calc Spreadsheet containing data table 'Docs' (31.65 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-08-06 04:03 UTC, dreadnought1906
no flags Details
Base Database file with imported table 'Docs' (3.06 MB, application/vnd.sun.xml.base)
2007-08-06 04:11 UTC, dreadnought1906
no flags Details
This is a copy of Coding_5.06.ods with the format of date data changed to 'yyyy-mm-dd' as requested. (49.66 KB, application/vnd.oasis.opendocument.spreadsheet)
2007-08-06 22:57 UTC, dreadnought1906
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description dreadnought1906 2007-08-06 03:46:06 UTC
When dates are imported from a Calc spreadsheet to a Base database table, the
date is changed by two days following the import.

So, if one has a Calc table that includes the date 1991-01-01, the Base table,
following import, will have the date 1991-01-03.

No error is displayed.

The method of import that produces this error is:

1. select data in spreadsheet, including column headers

2. copy using ctrl+c

3. go to 'Tables' section of Base database interface

4. paste using ctrl+v

5. select -either- the option to create a new table OR the option to append data
to an existing table (both options produce the error)

6. if creating a new table, explicitly declare the column of dates to be a
'date' data type; if appending data, append column of dates to a field that is
already a 'date' data type


I hope this description is sufficient to help you track down this problem.  If
you want further details, please don't hesitate to ask.  I would provide example
files, but I can't seem to find a file upload option.  I can, however, email
them anywhere you might ask me to.

Thanks so much, developers, you do a fantastic job!
Comment 1 dreadnought1906 2007-08-06 04:03:41 UTC
Created attachment 47330 [details]
Calc Spreadsheet containing data table 'Docs'
Comment 2 dreadnought1906 2007-08-06 04:11:30 UTC
Created attachment 47331 [details]
Base Database file with imported table 'Docs'
Comment 3 Regina Henschel 2007-08-06 13:48:11 UTC
No error for me. What are your settings in Tools > Options > Calc > Calculate >
Date? It should be "default".
Comment 4 dreadnought1906 2007-08-06 18:10:54 UTC

Tools > Options > Calc > Calculate > Date is, indeed, set to default.

I tried this with some example data and got no error, however the error always
occurs, for me, with the table I provided, above, in Coding_5.06.ods.  It always
occurs, also, in several other tables I have which, unfortunately, I can't post
as they contain confidential data.

Would it be possible for you to attempt to reproduce this with the data in
Coding_5.06.ods?  It might be something odd that is only triggered by certain
kinds of data tables.
Comment 5 Regina Henschel 2007-08-06 19:22:54 UTC
On my system there is no date column in the file Coding_5.06. The column G named
"Date" has only integer numbers. Please provide a file with date formating which
shows the error.

Please try also, whether the import with Edit > Paste Special; Type "RTF" works
better for you.

There had been a problem with copy from calc to base, but that should be fixed
already, see issue 62797. Do you really use OOo2.2.1?
Comment 6 dreadnought1906 2007-08-06 22:54:44 UTC
This seems to be very similar to the problem in 62797.  I am, however,
definitely using version 2.2.1.

After a certain amount of frustration, I have managed to track down a further
condition necessary for the bug to manifest itself:

If the date information is formatted as a date in the spreadsheet (ie. the
'number format' is 'yyyy-mm-dd'), the import error does -not- seem to occur.

If the date is formatted as an integer (ie. number format '0'), then the date
imports as the date +2 days.

The problem turned up in my case because the spreadsheet version of my data
contains various formatting elements (eg. the header cells are a different
colour) which are undesirable to import into the Base database.  I had developed
a habit, then, of first cutting and pasting the data into a blank spreadsheet
using 'paste special' with the 'Formats' box unchecked, then recopying this data
to import it into the database.  This meant that my import was not messed up by
cell colours, but it had the subsidiary effect of getting rid of the 'date'
number format, as you noticed, and triggering the import problem.

It seems intuitive to me, as an end user, that a given integer in the data would
always represent the same date, regardless of the way in which it is formatted.
 Given that the data type has to be explicitly declared when it is imported to
Base, the behaviour I would expect would be that the formatting of the
spreadsheet would play no role.  I have no idea whether this assumption is
correct, but it seems so natural to me that I think that this might be a sign of
an interface problem, even if the software itself is not making a true 'mistake'
along the lines of issue 62797 (which it might be).  Might there be some way of
making the importation of date data format independent?

I will shortly upload a copy of Coding_5.06.ods with the dates formatted as
'yyyy-mm-dd'.  Please note that, if my researches are correct, data from this
file should -not- import incorrectly into Base.

Thanks, so much, for all your help.  If I can provide any further help, please
don't hesitate to ask.
Comment 7 dreadnought1906 2007-08-06 22:57:43 UTC
Created attachment 47350 [details]
This is a copy of Coding_5.06.ods with the format of date data changed to 'yyyy-mm-dd' as requested.
Comment 8 Frank Schönheit 2007-08-21 11:22:14 UTC
There is absolutely no way to *reliably* translate a mere integer value into a
date. In fact, to do so yo need a so-called "null date", which the integers are
an offset to. This date is not available in the clipboard (there are only the
integers), so Base assumes the standard SQL null date. Since this differs from
Calc's default null date by two days, and since you "stripped" the information
about this null date on the Calc side with removing the date formatting, Base
has no chance to properly import the dates.

What one could do is that the import wizard allows to user to specify a null
date, which it should assume when none is (implicitly) specified in the pasted data.

This would be a enhancement to the mentioned wizard, which sounds reasonable,
though I'd say it wouldn't have a high priority.