[Solved] Is there a way to open XML file in Calc?
[Solved] Is there a way to open XML file in Calc?
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
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
Last edited by alfas on Mon Oct 13, 2008 3:07 am, edited 1 time in total.
OOo 2.4.X on Ubuntu 8.x
Re: Is there a way to open XML file in Calc?
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.
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: Is there a way to open XML file in Calc?
LOL. Reminds me of the Monty Python sketch where they solve all the worlds problems in 30 seconds!Create your xslt filter...
Edit: http://www.youtube.com/watch?v=tNfGyIW7aHM |
AOO4/LO5 • Linux • Fedora 23
Re: Is there a way to open XML file in Calc?
Roll your own!Is there any way to do this?
[...]
It has flat structure, easy to map to cells.
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: Is there a way to open XML file in Calc?
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.
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.
AOO4/LO5 • Linux • Fedora 23
Re: Is there a way to open XML file in Calc?
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.
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.
AOO4/LO5 • Linux • Fedora 23
Re: Is there a way to open XML file in Calc?
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.
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.
- Attachments
-
- ucf-f9-t1053.zip
- XSLT import filter
- (2.22 KiB) Downloaded 4151 times
AOO4/LO5 • Linux • Fedora 23
Re: Is there a way to open XML file in Calc?
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 know such functionality is available in Microsoft Office is it available in OpenOffice.
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.alfas wrote:I have a simple XML file and I want to open it with Calc.
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"alfas wrote:<r><a>275726</a><m>45</m><p>Jul 22, 2008 12:00:00 AM</p></r>
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
<table:table-column table:style-name="co1" table:default-cell-style-name="xDT"/>
OOo produces cells in that column like this:
Code: Select all
<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>
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
<table:table-cell office:value-type="date" office:date-value="1999-12-31T23:59:59">
</table:table-cell>
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
<table:table-cell>
<text:p>31/12/99 23:59</text:p>
</table:table-cell>
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.
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: Is there a way to open XML file in Calc?
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.
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.
AOO4/LO5 • Linux • Fedora 23
Re: Is there a way to open XML file in Calc?
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.
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.
OOo 2.4.X on Ubuntu 8.x
Re: Is there a way to open XML file in Calc?
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.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 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. )
AOO4/LO5 • Linux • Fedora 23
Re: Is there a way to open XML file in Calc?
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.
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.
OOo 2.4.X on Ubuntu 8.x
Re: Is there a way to open XML file in Calc?
I found OOoXMLFiltersInstaller on ooomacros.org
Looks promising, but I don't have time to check this out.
Looks promising, but I don't have time to check this out.
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] Is there a way to open XML file in Calc?
Yes, Excel would display his xml file. That is the frustration.Villeroy wrote: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 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
OpenOffice 4.1.1 on Redhat 6.6
Re: [Solved] Is there a way to open XML file in Calc?
There are tutorial examples around, although I can't give you a link.ClarDold wrote:... I have yet to figure out how to make a .xslt for them, ...
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 ...
AOO4/LO5 • Linux • Fedora 23
Re: [Solved] Is there a way to open XML file in Calc?
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] Is there a way to open XML file in Calc?
That looks like wonderful stuff, but still requires some queries to be crafted.Villeroy wrote:BaseX is true magic.
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
OpenOffice 4.1.1 on Redhat 6.6
Re: [Solved] Is there a way to open XML file in Calc?
Looking further, it seems that I want to "transform" and "export" with "serialization" as either xml, or csv.ClarDold wrote: 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.
OpenOffice 4.1.1 on Redhat 6.6
Re: [Solved] Is there a way to open XML file in Calc?
Hallo
Libreoffice comes with a generic xml-importer ( →Data→→XML-Source....)
Maybe give it a try.
Libreoffice comes with a generic xml-importer ( →Data→→XML-Source....)
Maybe give it a try.
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
Re: [Solved] Is there a way to open XML file in Calc?
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.
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.
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] Is there a way to open XML file in Calc?
That, with lots of button clicking that requires a fair amount of mental effort, can produce what MS Excel does by default.karolus wrote:Hallo
Libreoffice comes with a generic xml-importer ( →Data→→XML-Source....)
Maybe give it a try.
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.
OpenOffice 4.1.1 on Redhat 6.6
Re: [Solved] Is there a way to open XML file in Calc?
And MS Excel does a good job at providing me with a useful modeling tool for that raw xml, without programming.Villeroy wrote:XML is a file format for developers only.
OpenOffice 4.1.1 on Redhat 6.6
Re: [Solved] Is there a way to open XML file in Calc?
Great. Then use MS Excel.ClarDold wrote:And MS Excel does a good job at providing me with a useful modeling tool for that raw xml, without programming.Villeroy wrote:XML is a file format for developers only.
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] Is there a way to open XML file in Calc?
Such a sad thought.Villeroy wrote: Great. Then use MS Excel.
OpenOffice 4.1.1 on Redhat 6.6
Re: [Solved] Is there a way to open XML file in Calc?
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.ClarDold wrote:Such a sad thought.Villeroy wrote: Great. Then use MS Excel.
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.
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] Is there a way to open XML file in Calc?
Not at all.Villeroy wrote: 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.
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.
OpenOffice 4.1.1 on Redhat 6.6