[Solved] Is there a way to open XML file in Calc?

[alfas]

Hi,

I have a simple XML file and I want to open it with Calc. Is there any way to do this?

For example my file is:

<?xml version="1.0" encoding="UTF-8"?>
<report>
<r><a>275726</a><m>45</m><p>Jul 22, 2008 12:00:00 AM</p></r>
<r><a>275727</a><m>75</m><p>Jul 22, 2008 12:00:00 AM</p></r>
</report>

It has flat structure, easy to map to cells.
I know such functionality is available in Microsoft Office is it available in OpenOffice. It is very useful feature that enables to use XML files instead of coma separated files.

Thanks

[Villeroy]

Yes, of course. This office produces it's own documents as zipped xml. The main purpose of xml is to use data in any program you like. The technology to convert any structure into any other structure is called xslt. Create your xslt filter, register it under menu:Tools>XML and slurp your files through that filter.

[acknak]

Create your xslt filter...

LOL. Reminds me of the Monty Python sketch where they solve all the worlds problems in 30 seconds!

Edit: http://www.youtube.com/watch?v=tNfGyIW7aHM

You might find this thread helpful: [XML Filter] Create XSLT filters for import and export

[Villeroy]

Is there any way to do this?
[...]
It has flat structure, easy to map to cells.

Roll your own!

[acknak]

If you can get me a data file, I'll offer to take a crack at making a filter for you. It's something I've been looking for an excuse to try.

I'm no whiz with XML and I've never used XSLT so it could be a complete flop--I make no promises.

Look in my profile / interests for an email address, or just attach a sample file here.

[acknak]

Well, I almost got the import to work. Yow! That was nearly as much fun as a poke in the eye.

The last point I'm stuck on is handling the dates. Although Calc understands the dates you showed if they are entered into a cell, the XML needs the date in ISO format: 2008-07-23T12:12:12.

I haven't yet figured out how to get the XSL to convert your dates into ISO format.

Other than that, it works.

[acknak]

Ok, here's the import filter.

Calc could really use a generic XML input feature--something analogous to CSV import. This is way too much work for something so simple.

Maybe an XML to CSV converter would be a reasonable workaround. That would be a lot easier and wouldn't require XSLT.

[Villeroy]

I know such functionality is available in Microsoft Office is it available in OpenOffice.

I have never imported XML in Excel and I have no clue if my German version of Excel could import your XML that easily.

I have a simple XML file and I want to open it with Calc.

It could be very, very simple if the exporting application would avoid commas in decimals and local date-times. With ISO dates and dot-decimals it's just a question of the template and styles you prepared for the import.

<r><a>275726</a><m>45</m><p>Jul 22, 2008 12:00:00 AM</p></r>

What does "Jul" and "AM" mean to a dumb software? Even non-US citizens know what it is, but how to tell a machine? Even in my mother tongue (German) Jul stands commonly for the month "July", however the full name is "Juli" and short month "Dec" would be "Dez" ("Dezember"). Most countries use comma as decimal separator (Pi is written as 3,14159 in German) whereas a comma in dot-countries is used as thousands-separator for better readability. It would be easier for any conversion tool if it could assume one single dot per number and avoid commas alltogether. Same problem with local "1:59:59 PM" vs standard "13:59:59"

Working with international ISO dates and dot-decimals it could be very easy to convert your XML (or any other) to perfectly valid spreadsheets or text document tables. This would not even require to have any office application installed (thanks to the free file format ODF). It could be done on any server, running any operating system and the most simple, generic XML-tools.
Let's see, if we can do the trick with your data assuming locale "en-US" or any other locale for that purpose.

Not shure if it helps acknak, but I did a quick test with various number formats and a set of styles for date, time, date-time, scientific numbers, percent values and booleans.
OOo seems to define a default style for the date-time column like this:

Code: Select all   Expand viewCollapse view

<table:table-column table:style-name="co1" table:default-cell-style-name="xDT"/>

["xDT" is the style I defined for date-times]
OOo produces cells in that column like this:

Code: Select all   Expand viewCollapse view

<table:table-cell office:value-type="date" office:date-value="1999-12-31T23:59:59">
      <text:p>31/12/99 23:59</text:p>
</table:table-cell>

[date-value=ISO date with "T" as separator in front of the ISO time portion]

Curiously, I removed all <text> tags in content.xml, for table "Sheet2 " which is a copy of the original sheet, so our date-time cell is defined as:

Code: Select all   Expand viewCollapse view

<table:table-cell office:value-type="date" office:date-value="1999-12-31T23:59:59">
</table:table-cell>

It makes no difference. Calc happily displays the date-time field according to style "xDT" and the used locale. Since none of my styles has a specific locale, the locale of style "Default" applies, if that one is not specified, all depends on the set application locale or the locale of the operating system.
Thus acknak could disregard the text-tags completely and leave it up to the application how the displayed text looks like when it is derived from the office:date-value and a style.

I did the same with <text> only, deleting all the attributes of <table:table-cell> , so our date-time cell is defined as:

Code: Select all   Expand viewCollapse view

<table:table-cell>
      <text:p>31/12/99 23:59</text:p>
</table:table-cell>

Quite obviously, this puts a literal string "31/12/99 23:59" with a text-tag (quote) in the formula bar. Of course, we all know how to convert this to a valid number in Calc, but this would be rather disapointing.

For me the question remains: Is there any way to put a loaclized string, for instance "Dec 31 1999 1:59 AM", together with the information about the locale ("en-US" in this case) and leave it up to the reading office-application what it means?
OOo "knows" thousands of valid number formats for most languages and countries in the world, so the converting script should not assume the obligation to convert anything to dotted decimals or ISO date-times.

[acknak]

You're right, I could have left it up to Calc to use the default date format and saved a lot of aggravation. For no particular reason, I wanted to use a custom date+time format. It took me a long time to get the custom format working properly, including fighting with some sort of glitch in OOo. I finally had to wipe my settings profile to fix it.

As you've pointed out, the XML import is far less flexible than the existing text import. It seems like it would be more flexible, and maybe simpler to implement, if there was a generic XML to columns step, then something like the text import function where you could configure the column types.

The XSLT feature is fine if you've got a lot of data to import and you want full control over all the formatting in the result.

[alfas]

Thanks acknak. That was very useful. Data is not so much of the problem, because I am creating XML file my self. So I can output dates in any format I want. I tent to use ISO format every where anyway. This was only in English format (can not recognize which one) only because English speaking clients got scared when they see ISO format.
I still wish that there would be some plug-in to have this conversion automatically. It is too complex for most of users to use XSL-T template, not to mention writing one. Current functionality opening unrecognised XML file in text editor is really not the best option.
Anyway thanks a lot for the template and the info on functionality.

[acknak]

I still wish that there would be some plug-in to have this conversion automatically. It is too complex for most of users to use XSL-T template, not to mention writing one.

You do realize that, once the filter is installed, the user never needs to do anything but open the data file, right? Your XML file will just like any other file.

You do still have to select the file type, but I have a feeling thats because the data file does not have a distinctive file name suffix. I'd have to test it, but I believe you could name your files anything.foo, and set the filter to open ".foo" files, and OOo would then be able to locate the filter without the user needing to specify a file type.

It should also be possible to use the XSLT apart from OOo, to convert your XML into an actual .ods file, then you don't need the filter at all. (But I have no idea at all how to actually do that. )

[alfas]

Yes, of course I do not need to specify it again and changing file extension works (it is opened correctly). However, having specific XSL-T aside from OpenOffice is not much of benefit, because it will transform to OpenOffice document.
It is possible to write quite general XSL-T template that would be able to open more or less most of flat XML files. However, it is a little outside of scope of XSL-T, this means that template will be quite complex. It also will not be flexible enough to do some customizations during transformation (like deciding should attributes of XML element become cells).
Knowing huge popularity of XML files I assume that there would be a lot of end users who would benefit from simple plug-in (maybe combined with XSL-T template) that would be able to open XML files without any additional effort. There are really not so many people who knows how to write XSL-T.

[acknak]

Yes, absolutely. I agree.

[Villeroy]

I found OOoXMLFiltersInstaller on ooomacros.org
Looks promising, but I don't have time to check this out.

[ClarDold]

I have never imported XML in Excel and I have no clue if my German version of Excel could import your XML that easily.

Yes, Excel would display his xml file. That is the frustration.
I have a program that delivers XML output. It is intended to be consumed by another program that knows what to do with it, but I want to do other things.

MS Excel opens the .xml file, complains that there is no Style, and displays the file.
Sometimes, the first column is a repeated piece of header informatio9n, all the way down.
Sometimes, the first few columns are like a small section of the XML, a few rows deep.
The next few colums are stepped down below the other rows, etc.

It's as if someone took several workbook sheets and arranged them sensibly on one sheet.

OpenOffice Calc, on the other hand, reports a General Input/Output error, and displays nothing.
There are a handful of different xml layouts coming from this program.
I have yet to figure out how to make a .xslt for them, although I would do that, if I could then open the files in Open Office, although that seems like a lot of work just to get the results of opening any xml file in Excel.

I currently use a Windows KVM and MS Office _only_ to open this set of .xml and save them as .xls, which I am then able to use in Open Office Calc.

-- Clarence

[acknak]

... I have yet to figure out how to make a .xslt for them, ...

There are tutorial examples around, although I can't give you a link.

There are working transforms shipped with OO, but the ones I've looked at are rather hairy--way over my head at least.

Tools > XML Filter Settings ...

[Villeroy]

http://basex.org/
http://docs.basex.org/wiki/Main_Page

BaseX is true magic.

[ClarDold]

BaseX is true magic.

That looks like wonderful stuff, but still requires some queries to be crafted.
Maybe I'm missing the simple invocation.

$ basex -i Sample-XML/Preview.xml -o BaseX-Thin.xml

opens an interactive tool, expecting me to do something.

I want basex to take in the Preview.xml, and write a "good" .xml with style, or maybe, separately, write the style map so I can use it.

In the GUI, I can see what I want in the "table", but I don't see how to save it as well formed xml.
-- Clarence

[ClarDold]

Maybe I'm missing the simple invocation.
I want basex to take in the Preview.xml, and write a "good" .xml with style, or maybe, separately, write the style map so I can use it.

Looking further, it seems that I want to "transform" and "export" with "serialization" as either xml, or csv.

[karolus]

Hallo

Libreoffice comes with a generic xml-importer ( →Data→→XML-Source....)

Maybe give it a try.

[Villeroy]

XML is a file format for developers only. It helps to develop user friendly applications with specific file formats. XML is called a meta file format because it specifies how file formats need to be crafted in order to make them useful for developers. If you do not have any application for your specific flavour of XML then you need to write your own (which is very unlikely because someone might have designed this particular XML for some application).

LibreOffice 4.4.2 shows a dialog with a tree of nodes and lets you specify which node value should be copied to which cell which is not particularly useful.

[ClarDold]

Hallo

Libreoffice comes with a generic xml-importer ( →Data→→XML-Source....)

Maybe give it a try.

That, with lots of button clicking that requires a fair amount of mental effort, can produce what MS Excel does by default.
I see it in LibreOffice, but not the Open Office where I need it.

I can see it, but if I save it as .xls or ods, close, and open, all that's there is the header, no data, or "pct" in a1, with nothing else.

[ClarDold]

XML is a file format for developers only.

And MS Excel does a good job at providing me with a useful modeling tool for that raw xml, without programming.

[Villeroy]

XML is a file format for developers only.

And MS Excel does a good job at providing me with a useful modeling tool for that raw xml, without programming.

Great. Then use MS Excel.

[ClarDold]

Great. Then use MS Excel.

Such a sad thought.

[Villeroy]

Great. Then use MS Excel.

Such a sad thought.

What is really, really sad is that in 15 years (since OpenOffice.org 1.0) not a single person had been able to write an extension resembling the missing functionality.
What I can see in various online tutorials looks very clumsy. Every single time you open some XML file, you need to drag and drop the right elements from a tree control to the right places in your sheet. To avoid this, Microsoft recommends writing an XSLT script just like we do.

[ClarDold]

Every single time you open some XML file, you need to drag and drop the right elements from a tree control to the right places in your sheet.

Not at all.
The xml opens in excel, and all elements are displayed.
I can sort, sum, copy, if I wish.
It is fully functional.

It's not pretty, but it is functional.

In AOO, it fails to open.