Apache OpenOffice (AOO) Bugzilla – Issue 80371
copy table wizard: allow to specify a null date for columns where non-date values are imported as date values
Last modified: 2013-08-07 15:45:41 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!
Created attachment 47330 [details] Calc Spreadsheet containing data table 'Docs'
Created attachment 47331 [details] Base Database file with imported table 'Docs'
No error for me. What are your settings in Tools > Options > Calc > Calculate > Date? It should be "default".
Thanks, 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.
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?
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.
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.
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.