[Solved] OO Calc and ss:Type="DateTime" recognition?

Discuss the spreadsheet application
Post Reply
windowshater
Posts: 78
Joined: Sun Jun 22, 2014 11:47 am

[Solved] OO Calc and ss:Type="DateTime" recognition?

Post by windowshater »

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? ;)
Attachments
test.xls
(12.93 KiB) Downloaded 178 times
Last edited by windowshater on Thu Aug 05, 2021 10:00 am, edited 1 time in total.
Ubuntu / Win10 /Virtualbox
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: OO Calc and "datetime" recognition?

Post by MrProgrammer »

Thank you for the attachment.
windowshater wrote:As an example I'll upload an xls file from the bank …
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.
$ 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                           |........|
windowshater wrote:I've never figured out how to get OO Calc to read the datetime columns correctly? Do you?
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).

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).
XML.png
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).
windowshater
Posts: 78
Joined: Sun Jun 22, 2014 11:47 am

Re: OO Calc and "datetime" recognition?

Post by windowshater »

Thank YOU for the reply :D

Somehow it won't let me post this today ... I have to give up now. An image of my response :bravo:
Wont-let-me-post-as-text-thus-as-image-lol
Wont-let-me-post-as-text-thus-as-image-lol
Ubuntu / Win10 /Virtualbox
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] OO Calc and "datetime" recognition?

Post by robleyd »

from image wrote:What program do you use to get the hex dump of a file
MrProgrammer wrote:
$ hexdump -Cn 8 ~/Downloads/test.xls # Fake XLS file
Available for allmany platforms. Of course, it isn't the only such program available, as you can see via a web search. Some file managers, Midnight Commander for one, also have inbuilt hex viewers.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Solved] OO Calc and "datetime" recognition?

Post by MrProgrammer »

the bank wrote:The files are fine, the problem must be on your end.
The bank says to use Excel, right? You tried to use Calc. The problem is on your end.
windowshater wrote:Please provide simple CSV files …
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:Using Text to columns, column Type was greyed out.
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:Notepad++ which I would assume you use too
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:What program do you use to get the hex dump of a file?
Hex dump
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.
windowshater wrote:General Error - SQL Error
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.
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).
windowshater
Posts: 78
Joined: Sun Jun 22, 2014 11:47 am

Re: [Solved] OO Calc and "datetime" recognition?

Post by windowshater »

bad, this
bad, this
Ubuntu / Win10 /Virtualbox
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] OO Calc and "datetime" recognition?

Post by Villeroy »

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.
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
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] OO Calc and ss:Type="DateTime" recognition?

Post by robleyd »

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.1.2; SlackBuild for 24.2.1 by Eric Hameleers
windowshater
Posts: 78
Joined: Sun Jun 22, 2014 11:47 am

Re: [Solved] OO Calc and ss:Type="DateTime" recognition?

Post by windowshater »

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.
Yes, that may have been the reason! Thanks for the tip!
Ubuntu / Win10 /Virtualbox
windowshater
Posts: 78
Joined: Sun Jun 22, 2014 11:47 am

Re: [Solved] OO Calc and ss:Type="DateTime" recognition?

Post by windowshater »

MrProgrammer wrote:The tutorial says...
Again, your solution is so much/still appreciated. I just had to look it up again :lol: :super: :bravo:
So easy, once one knows of it, thanks!
Ubuntu / Win10 /Virtualbox
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] OO Calc and ss:Type="DateTime" recognition?

Post by Villeroy »

My third sed program (as far as I recall):

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
fixes the format errors in test.xml and writes to 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
windowshater
Posts: 78
Joined: Sun Jun 22, 2014 11:47 am

Re: [Solved] OO Calc and ss:Type="DateTime" recognition?

Post by windowshater »

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! :)
Ubuntu / Win10 /Virtualbox
Post Reply