Access 2003 xml read failed

[vilmarci]

Dear Community,

The following simple XML file is read correctly by Excel, but not in Access:

<MNBExchangeRates>
<Day date="2010-02-01">
<Rate curr="EUR" unit="1">270,71</Rate>
<Rate curr="USD" unit="1">194,59</Rate>
</Day>
<Day date="2010-02-02">
<Rate curr="EUR" unit="1">269,24</Rate>
<Rate curr="USD" unit="1">193,21</Rate>
</Day>
</MNBExchangeRates>

In Excel, the layout is correct:
date Rate curr unit
2/1/2010 270,71 EUR 1
2/1/2010 194,59 USD 1
2/2/2010 269,24 EUR 1
2/2/2010 193,21 USD 1

In Access, only the "Rate" column is imported.
Please advise.

Thanks,
vm

 

[Stefan Hoffmann]

Dear Community,

The following simple XML file is read correctly by Excel, but not in Access:

<MNBExchangeRates>
<Day date="2010-02-01">
<Rate curr="EUR" unit="1">270,71</Rate>
<Rate curr="USD" unit="1">194,59</Rate>
</Day>
<Day date="2010-02-02">
<Rate curr="EUR" unit="1">269,24</Rate>
<Rate curr="USD" unit="1">193,21</Rate>
</Day>
</MNBExchangeRates>

In Excel, the layout is correct:
date Rate curr unit
2/1/2010 270,71 EUR 1
2/1/2010 194,59 USD 1
2/2/2010 269,24 EUR 1
2/2/2010 193,21 USD 1

In Access, only the "Rate" column is imported.
Please advise.

Thanks,
vm

 

[Stefan Hoffmann]

hi,

The following simple XML file is read correctly by Excel, but not in Access:

<MNBExchangeRates>
<Day date="2010-02-01">
<Rate curr="EUR" unit="1">270,71</Rate>
<Rate curr="USD" unit="1">194,59</Rate>
</Day>
</MNBExchangeRates>
In Access, only the "Rate" column is imported.
Please advise.

Attributes are not handled by the normal import:

http://support.microsoft.com/default.aspx/kb/285329


mfG
--> stefan <--

 

[Petr Danes]

To amplify a bit on Stefan's answer, in case you're not familiar with the
terminology, your XML file would have to look something like this:

<MNBExchangeRates>
<Day>
<date>2010-02-01</date>
<Rate>270,71</Rate>
<curr>EUR</curr>
<unit>1</unit>
</Day>
<Day>
<date>2010-02-01</date>
<Rate>194,59</Rate>
<curr>USD</curr>
<unit>1</unit>
</Day>
<Day>
<date>2010-02-02</date>
<Rate>269,24</Rate>
<curr>EUR</curr>
<unit>1</unit>
</Day>
<Day>
<date>2010-02-02</date>
<Rate>193,21</Rate>
<curr>USD</curr>
<unit>1</unit>
</Day>
</MNBExchangeRates>

If you are unable to affect the format in which you get the XML, an XSL
transform can do the conversion for you.

Pete

 

[vilmarci]

Pete, Stefan,

Thanks for the info. Now I created a table with the required layout and
exported into xml, together with the xsl, expecting that I can use the same
to use if for the transformation. Unfortunately it did not work. I get an
error message:
"The data was transformed in a format that cannot be imported"

I cannot change the xml file as it comes from a web service. Is it possible
to create an xls transformation that supports multiple days and currencies?
I am not familiar with xml.

Thank you
vm

 

[Stefan Hoffmann]

hi,

Thanks for the info. Now I created a table with the required layout and
exported into xml, together with the xsl, expecting that I can use the same
to use if for the transformation. Unfortunately it did not work. I get an
error message:
"The data was transformed in a format that cannot be imported"

I cannot change the xml file as it comes from a web service. Is it possible
to create an xls transformation that supports multiple days and currencies?
I am not familiar with xml.

Have you tried the transform of the KB article? Then you have seen that
this transform only reads attributes. What you also need to do is to
copy the rest:

http://www.itjungle.com/mgo/mgo101003-story01.html


mfG
--> stefan <--

--
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xslutput method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:template match="*">
<xsl:copy>
<xsl:if test="@*">
<xsl:for-each select="@*">
<xsl:element name="{name()}">
<xsl:value-of select="."/>
</xsl:element>
</xsl:for-each>
</xsl:if>
<xsl:apply-templates/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>

 

[Petr Danes]

XML is a huge topic all by itself. The fundamentals are quite simple, but
the range of what you can do with it is not - you have only to look at the
XML section of your favorite bookstore.

In general, what you are asking should be possible without too much effort.
If there is a finite set of days and currencies, it is possible to write XSL
that will cover all the possibilities.

Take a look at what was created by the transform you used and compare it
with your original file. Are there headers missing? Does it maybe lack a
version number? Try manually making a copy of the original file, with all
peripheral data intact, only with the data layed out as I wrote, and see
what the import routine says about that.

You should be able to do it with only one or two lines of data, to avoid
having to do a lot of manual editing. Once you have a layout that the import
routine will accept, work backwards from that, making one change at a time,
until you get an error. It will then be much clearer exactly what caused the
error and you will have a better idea of what to do about it.

Pete

 

[vilmarci]

I gave up the xsl part.
It looks easier to open an excel in the background, import the xml and put
the list to Access.
I really cannot understand, why does excel flawlessly process the original
xml and why Access cannot.
Thanks for all the help,
vm

 

[joelgeraldine]

jj::!!khbn

I gave up the xsl part.
It looks easier to open an excel in the background, import the xml and put
the list to Access.
I really cannot understand, why does excel flawlessly process the original
xml and why Access cannot.
Thanks for all the help,
vm