[Solved] OO Calc and ss:Type="DateTime" recognition?
-
- Posts: 78
- Joined: Sun Jun 22, 2014 11:47 am
[Solved] OO Calc and ss:Type="DateTime" recognition?
Hello all!
For years I've had this OO Calc issue, but I haven't ventured to ask for feedback nor do I know if this is a known issue or unique to my installation:
Do you also struggle to make OO Calc correctly read
ss:Type="DateTime"
of an .xls file?
As an example I'll upload an xls file from the bank that I merely shortened for privacy and relevancy.
When you first notepad++ the .xls file (=CSV it), you see all the "datetime" fields correct: different.
But when you OO calc it, you see that it makes all dates the same: 12:00:00 AM
And when you then Format the date columns, you see that all change to: 12/30/1899
I've never figured out how to get OO Calc to read the datetime columns correctly? Do you?
PS: Checking the Preview, seeing my sig "Ubuntu/ Win10/ Virtualbox", I am wondering: Has anyone of you tried to run OO on Win11 yet?
For years I've had this OO Calc issue, but I haven't ventured to ask for feedback nor do I know if this is a known issue or unique to my installation:
Do you also struggle to make OO Calc correctly read
ss:Type="DateTime"
of an .xls file?
As an example I'll upload an xls file from the bank that I merely shortened for privacy and relevancy.
When you first notepad++ the .xls file (=CSV it), you see all the "datetime" fields correct: different.
But when you OO calc it, you see that it makes all dates the same: 12:00:00 AM
And when you then Format the date columns, you see that all change to: 12/30/1899
I've never figured out how to get OO Calc to read the datetime columns correctly? Do you?
PS: Checking the Preview, seeing my sig "Ubuntu/ Win10/ Virtualbox", I am wondering: Has anyone of you tried to run OO on Win11 yet?
- Attachments
-
- test.xls
- (12.93 KiB) Downloaded 179 times
Last edited by windowshater on Thu Aug 05, 2021 10:00 am, edited 1 time in total.
Ubuntu / Win10 /Virtualbox
- MrProgrammer
- Moderator
- Posts: 4905
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: OO Calc and "datetime" recognition?
Thank you for the attachment.
This is my result. I've turned on View → Value Highlighting to show that the first two columns contain real dates (blue), not text (black). If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
[Tutorial] Ten concepts that every Calc user should know
The bank lies. This isn't a real XLS file. It's XML (ASCII text) which has been given the XLS extension. A real XLS file does not begin with <?xml but begins with unprintable characters D0 CF 11 E0 A1 B1 1A E1 (shown in hexadecimal). If Excel knows how to deal with this garbage, use Excel. It's what the bank expects you to use. I would guess that they don't say they support OpenOffice, only Excel. You're having difficulty because you aren't using the correct program. Following is a dump of the first eight bytes of your attachment and then a real XLS file.windowshater wrote:As an example I'll upload an xls file from the bank …
$ hexdump -Cn 8 ~/Downloads/test.xls # Fake XLS file
00000000 3c 3f 78 6d 6c 20 76 65 |<?xml ve|
$ hexdump -Cn8 S060723.xls # Real XLS file
00000000 d0 cf 11 e0 a1 b1 1a e1 |........|
Use a text editor (even Writer!) to change ss:Type="DateTime" to ss:Type="String". Then open the file. Use Text to Columns to change the text dates to real dates for columns A and B. For this situation, the column type is Date (YMD).windowshater wrote:I've never figured out how to get OO Calc to read the datetime columns correctly? Do you?
This is my result. I've turned on View → Value Highlighting to show that the first two columns contain real dates (blue), not text (black). If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
-
- Posts: 78
- Joined: Sun Jun 22, 2014 11:47 am
Re: OO Calc and "datetime" recognition?
Thank YOU for the reply
Somehow it won't let me post this today ... I have to give up now. An image of my response
Somehow it won't let me post this today ... I have to give up now. An image of my response
Ubuntu / Win10 /Virtualbox
Re: [Solved] OO Calc and "datetime" recognition?
from image wrote:What program do you use to get the hex dump of a file
Available forMrProgrammer wrote:$ hexdump -Cn 8 ~/Downloads/test.xls # Fake XLS file
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
- MrProgrammer
- Moderator
- Posts: 4905
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: [Solved] OO Calc and "datetime" recognition?
The bank says to use Excel, right? You tried to use Calc. The problem is on your end.the bank wrote:The files are fine, the problem must be on your end.
I agree that would be better. But you now have a workaround. If you learn XSLT, it can be used to translate the XML to CSV. A web search for "convert XML to CSV" will no doubt find other ways to do that. XSLT could even convert the bank's XML to the Calc XML so you would ZIP it and create an ODS file, but that project would be very complex. A perl program could do it too. This forum is not for XSLT or perl questions.windowshater wrote:Please provide simple CSV files …
The tutorial says: After indicating your fields, you can select how each of them is to be processed. In the first row of the large box at the bottom of the dialog, select a field by clicking it. You can't set the Column Type until you indicate which field(s) the operation applies to.windowshater wrote:Using Text to columns, column Type was greyed out.
Notepad++ only runs on damned Windoze, which I thankfully do not use. My Mac has at least half a dozen text editors pre-installed already and I surely don't need another. I performed the translation with sed -e 's/ss:Type="DateTime"/ss:Type="String"/' <test.xls >test.txt.windowshater wrote:Notepad++ which I would assume you use too
Hex dumpwindowshater wrote:What program do you use to get the hex dump of a file?
If your Linux system doesn't have the hexdump command, use od, for example od -t x1 -t c «file». Wretched Windoze many not have any hex dump utilities from Microsnot, but you can surely find ones for it and install them.
I've seen that occasionally. I simply try again a few hours later. No one at this forum has the authorization to diagnose or correct the problem. We're all OpenOffice users, just like you. You'd have to report this to Apache, but I suspect that would be a waste of your time.windowshater wrote:General Error - SQL Error
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
-
- Posts: 78
- Joined: Sun Jun 22, 2014 11:47 am
Re: [Solved] OO Calc and "datetime" recognition?
To my surprise the DocView mode of Emacs can parse Excel XML and the result looks exactly as in Calc. There could be something wrong with the file or both programs share the same bug. I don't know.
See also: https://stackoverflow.com/questions/205 ... -dd-format where a DateTime looks like 2021-08-06T00:00:00.000 and the display format is specified in a separate section.
In fact, I can regex-replace
<Data ss:Type="DateTime">(\d\d\d\d-\d\d-\d\d)</Data>
with
<Data ss:Type="DateTime">\1T00:00:00.000</Data>
and Calc shows the correct numeric values.
See also: https://stackoverflow.com/questions/205 ... -dd-format where a DateTime looks like 2021-08-06T00:00:00.000 and the display format is specified in a separate section.
In fact, I can regex-replace
<Data ss:Type="DateTime">(\d\d\d\d-\d\d-\d\d)</Data>
with
<Data ss:Type="DateTime">\1T00:00:00.000</Data>
and Calc shows the correct numeric values.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] OO Calc and ss:Type="DateTime" recognition?
Re problem with posting: if you spend a long while composing a message, the forum software may 'time out'. If you have a lengthy post consider composing your message in e.g. a text editor and then copy/paste to the forum and add formatting and images as necessary.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
-
- Posts: 78
- Joined: Sun Jun 22, 2014 11:47 am
Re: [Solved] OO Calc and ss:Type="DateTime" recognition?
Yes, that may have been the reason! Thanks for the tip!robleyd wrote:Re problem with posting: if you spend a long while composing a message, the forum software may 'time out'. If you have a lengthy post consider composing your message in e.g. a text editor and then copy/paste to the forum and add formatting and images as necessary.
Ubuntu / Win10 /Virtualbox
-
- Posts: 78
- Joined: Sun Jun 22, 2014 11:47 am
Re: [Solved] OO Calc and ss:Type="DateTime" recognition?
Again, your solution is so much/still appreciated. I just had to look it up againMrProgrammer wrote:The tutorial says...
So easy, once one knows of it, thanks!
Ubuntu / Win10 /Virtualbox
Re: [Solved] OO Calc and ss:Type="DateTime" recognition?
My third sed program (as far as I recall):
fixes the format errors in test.xml and writes to fixed.xml
libreoffice fixed.xml opens the file with correct numeric date values.
Code: Select all
sed -E s_'<Data ss:Type="DateTime">'\([4}-[[:digit:]]{2}-[[:digit:]]{2}\)'</Data>'_'<Data ss:Type="DateTime">'\\1T00:00:00.000'</Data>'_ test.xml > fixed.xml
libreoffice fixed.xml opens the file with correct numeric date values.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 78
- Joined: Sun Jun 22, 2014 11:47 am
Re: [Solved] OO Calc and ss:Type="DateTime" recognition?
Thank you Honestly, I am a happy notepad++ user knowing replace function (even regex), and it's sooo quick and easy to make that one change that MrProgrammer had suggested (without the need for regex even).
I merely had forgotten the solution, must be an age thing LOL
Thanks so much!
I merely had forgotten the solution, must be an age thing LOL
Thanks so much!
Ubuntu / Win10 /Virtualbox