![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
I've got an XML file (I've generated myself, programmatically).
I thought it ought to be easy to import it in LO (3.4.6), somehow. I
thougth Calc would be a good bet, but it just gives me "General I/O
error". Base does the same.
The website
(help.libreoffice.org/Common/About_Import_and_Export_Filters)
suggests it ought to be doable but gives no details whatsoever.
Anyone got any pointers I could use?
Regars
Mark Stanton
Have you tried with a recent release of LibO? 3.4.6 is pretty old
Hi.
I do this with an XSLT filter.
http://user.services.openoffice.org/en/forum/viewtopic.php?t=3490&f=45
Here is a pre-processing method I have not tried as the above works well for me.
http://www.ibm.com/developerworks/xml/library/x-oocalc/
steve
Have you tried with a recent release of LibO? 3.4.6 is pretty old
It's the current release from the Fedora repo.
Mark Stanton
One small step for mankind...
Thanks for that Steve.
It looks like this is saying that OO/LO needs a specific template for a
specific input, is that the case?
I hope not, XML is supposed to be a self defining file format surely? A
general importer ought to be easy, no?
Regards
Mark Stanton
One small step for mankind...
Hi 
Yes, but LO develops so fast that even a version just a couple of months old is 'ancient'.
I think it would be nice to see an LTS that has all security and bug-fixes back-ported to it so that people could stick to one version for a year or more rather than having to re-install every month or so. Unfortunately the BoD are fairly committed to making sure we don't get one.
Regards from
Tom
Mark Stanton wrote
I hope not, XML is supposed to be a self defining file format surely?
It's a meta-file format. You (who defines all the XML tags) are the one and
only person able to develop import and export software for your particular
flavour of XML.
<xml>
<BLAH foo="bar">
<blub x=12>1.43</blub>
<scramble crucifix=True>ertzuiopdfghjklöxcvbnm</scramble>
</BLAH>
</xml>
How would you translate this into a spreadsheet? Is BLAH a row, a column?
What is scramble? How to deal with the attributes?
Nobody ever tells us anything about his/her user-defined file formats such
as CSV or XML. So most of the topics becaome lengthy and some remain
unsolved.
A general importer ought to be easy, no?
Google reveals plenty of examples about how to write XLST scritpts to
import/export user-defined XML formats into/from ODF. This is development
work just like writing macros or setting up a database.
In all those years since OOo 1.0 nobody wrote a generic XML-to-Calc tool. I
would search in the database world for existing XML tools for database
engines. Once you have the data in a connectable database, you have it it in
Calc and Writer as well.
Andreas,
My understanding the problem with importing a generic XML file is that
the document definition does not a (defacto) "standard" that one can
write an import script for? If the XML file uses a known definition (ODF
for example) then it is easier to write an import script because there
is a "standard" defining the document internals.
The extension of this is, if the file format is well documented, even if
it is proprietary, then writing an import script is possible because
there exists a specification for the format. The only issue then is
whether one has the complete format specification.
Ok, I'll put it on the list.
Visual FoxPro (for example) has a built in function, XMLTOCURSOR,
which takes a string or filename and produces a table.
Mark Stanton
One small step for mankind...
FoxPro export exports database fields like this:
<?xml version="1.0"?><Document> <OrderUpdate>
<OrderId>12345</OrderId>
<OrderDate>01/01/2004</OrderDate>
<Confirmation>0000123</Confirmation>
<Storage>
<StorageId>01</StorageId>
<Quantity>1450</Quantity>
</Storage>
<Storage>
<StorageId>02</StorageId>
<Quantity>2480</Quantity>
</Storage>
</OrderUpdate>
</Document>
It might be comparatively easy to write a Base driver for structures like
this one. A spreadsheet does not imply any such structure.
As far as I know, Excel imports arbitrary flavours of tabular XML through a
wizard analog to a CSV import wizard. You specify the column tags, the row
tags and get raw data in a spreadsheet.
In the following example each <record> represents one consecutive
spreadsheet row (starting at row#1) and each <value> represents a
consecutive column value within that row (starting at column A). First row
has column labels.
<xml>
<record>
<value type="String">ID</value>
<value typ="String">Order Date</value>
<value typ="String">Value</value>
</record>
<record>
<value type="Number">13</value>
<value type="Date">2010-12-28</value>
<value type="String">Some Text</value>
</record>
<record>
<value type="Number">14</value>
<value type="Date">2010-12-31</value>
<value type="String">Some Other Text</value>
</record>
</xml>
This one represents the very same data in German tags:
<xml>
<zeile>
<wert typ="Zeichen">ID</wert>
<wert typ="Zeichen">Order Date</wert>
<wert typ="Zeichen">Value</wert>
</zeile>
<zeile>
<wert typ="Zahl">13</wert>
<wert typ="Datum">2010-12-28</wert>
<wert typ="Zeichen">Some Text</wert>
</zeile>
<zeile>
<wert typ="Zahl">14</wert>
<wert typ="Datum">2010-12-31</wert>
<wert typ="Zeichen">Some Other Text</wert>
</zeile>
</xml>
Other flavours of xml may describe rows within columns, railway connections
in time tables, articles and clients in invoices, ... Any tool to import
arbitrary XML into sheets needs to import text, numbers, "special numbers"
and blanks into columns and rows even if there is no rectangular structure
of records and fields.
If there is a database structure (like the above FoxPro XML) it needs to
import each value (number, text, date, blank) under its given field name.
Most people who ask for XML import into Calc mean the XML flavour produced
by Excel 2003. But they do not know that this particular flavour of XML is
specifically made for that particular application. Other applications to
read Excel-XML can be written easily but they need to be written. XML is not
a file format on its own right. XML lets you define your own file formats.
Some of the XML files I import have nested lists, i.e not flat. I like to define how these are imported.
The file also has a lot of fields I don't want imported (not shown). So by using a filter I get what I want displayed how I want.
<?xml version="1.0" encoding="UTF-8"?>
<customers type="array">
<customer>
<salt>c9bf86ac97d3f742f85e9811d338a594af727dbc</salt>
<updated-at type="datetime">2010-02-16T17:03:57-05:00</updated-at>
<valid-access-count type="integer">0</valid-access-count>
<customer-visibility-groups type="array">
<customer-visibility-group>
<id type="integer">10</id>
<name>All Customers</name>
<the-parent-record-id type="NilClass">1</the-parent-record-id>
</customer-visibility-group>
<customer-visibility-group>
<id type="integer">11</id>
<name>OIML customers</name>
<the-parent-record-id type="NilClass">1</the-parent-record-id>
</customer-visibility-group>
<customer-visibility-group>
<id type="integer">258</id>
<name>AE-United Arab Emirates</name>
<the-parent-record-id type="NilClass">1</the-parent-record-id>
</customer-visibility-group>
<customer-visibility-group>
<id type="integer">21</id>
<name>MNA-Middle East & N Africa</name>
<the-parent-record-id type="NilClass">1</the-parent-record-id>
</customer-visibility-group>
</customer-visibility-groups>
</customer>
</customers>
Steve
MySQL can import your <customer-visibility-groups>:
http://dev.mysql.com/doc/refman/5.5/en/load-xml.html
If there is any reason why you want <customer-visibility-groups> in a
calculator software, connect a registerd Base document to your MySQL server
and dump the imported data from the data source pane into a sheet which
creates a refreshable link.
XSLT and ODF office suites:
http://www.ibm.com/developerworks/xml/library/x-oocalc/
http://www.ibm.com/developerworks/xml/tutorials/x-xsltopenoff/index.html [9
pages]
There are various topics on the OOo forums:
http://www.oooforum.org/forum/viewtopic.phtml?t=67906
http://forum.openoffice.org/en/forum/viewtopic.php?t=3490&f=45
Hi Andreas. The file with that XML snippit is uploaded to a MySQL database behind Rails every 15 minutes.
From time to time I load it into calc to provide quick lists of extracted data to our sales guys who cope fare easier with a spread sheet.
Steve
Am 03.10.2012 20:54, Mark Stanton wrote:
I've got an XML file (I've generated myself, programmatically).
I thought it ought to be easy to import it in LO (3.4.6), somehow. I
thougth Calc would be a good bet, but it just gives me "General I/O
error". Base does the same.The website
(help.libreoffice.org/Common/About_Import_and_Export_Filters)
suggests it ought to be doable but gives no details whatsoever.Anyone got any pointers I could use?
Regars
Mark Stanton
Well, if you implemented your own XML format then you should know the
appropriate software for your particular data structures.
If there is some reason why you want to open your file format in this
office suite then you have to write an XSLT script translating your
specific XML flavour into ODF for import and possibly vice versa for export.
Hi Mark,
I've got an XML file (I've generated myself, programmatically).
I thought it ought to be easy to import it in LO (3.4.6), somehow. I
thougth Calc would be a good bet, but it just gives me "General I/O
error". Base does the same.
Without a filter to transform it into something readable/formattable by
one of the apps of the suite, at best you could possibly open it in
Writer as plain text.
The installed XML/XSLT filters that come with LO are :
Docbook from SXW (old binary StarOffice/OOo format) - import/export
MediaWiki - export
Mediawiki_web - export
MS Excel 2003 XML - import/export
MS Word 2003 XML - import/export
UOF Presentation - import/export
UOF Spreadsheet - import/export
UOF Text - import/export
XHTML Calc - export
XHTML Writer - export
XHTML Draw - export
XHTML Impress - export
Alex