Access 2003 xml read failed

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

hi,

On 21.02.2010 11:59, vilmarci wrote:
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 --

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
date2010-02-01/date
Rate270,71/Rate
currEUR/curr
unit1/unit
/Day
Day
date2010-02-01/date
Rate194,59/Rate
currUSD/curr
unit1/unit
/Day
Day
date2010-02-02/date
Rate269,24/Rate
currEUR/curr
unit1/unit
/Day
Day
date2010-02-02/date
Rate193,21/Rate
currUSD/curr
unit1/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" pí¹e v diskusním pøíspìvku
...
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

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


"Petr Danes" wrote in message
...
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
date2010-02-01/date
Rate270,71/Rate
currEUR/curr
unit1/unit
/Day
Day
date2010-02-01/date
Rate194,59/Rate
currUSD/curr
unit1/unit
/Day
Day
date2010-02-02/date
Rate269,24/Rate
currEUR/curr
unit1/unit
/Day
Day
date2010-02-02/date
Rate193,21/Rate
currUSD/curr
unit1/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" pí¹e v diskusním pøíspìvku
...
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

hi,

On 21.02.2010 15:48, vilmarci wrote:
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

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" pí¹e v diskusním pøíspìvku
...
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


"Petr Danes" wrote in message
...
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
date2010-02-01/date
Rate270,71/Rate
currEUR/curr
unit1/unit
/Day
Day
date2010-02-01/date
Rate194,59/Rate
currUSD/curr
unit1/unit
/Day
Day
date2010-02-02/date
Rate269,24/Rate
currEUR/curr
unit1/unit
/Day
Day
date2010-02-02/date
Rate193,21/Rate
currUSD/curr
unit1/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" pí¹e v diskusním pøíspìvku
...
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

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

"Petr Danes" wrote in message
...
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" pí¹e v diskusním pøíspìvku
...
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


"Petr Danes" wrote in message
...
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
date2010-02-01/date
Rate270,71/Rate
currEUR/curr
unit1/unit
/Day
Day
date2010-02-01/date
Rate194,59/Rate
currUSD/curr
unit1/unit
/Day
Day
date2010-02-02/date
Rate269,24/Rate
currEUR/curr
unit1/unit
/Day
Day
date2010-02-02/date
Rate193,21/Rate
currUSD/curr
unit1/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" pí¹e v diskusním pøíspìvku
...
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

jj::!!khbn

"vilmarci" a écrit dans le message de groupe de
discussion : ...
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

"Petr Danes" wrote in message
...
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" pí¹e v diskusním pøíspìvku
...
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


"Petr Danes" wrote in message
...
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
date2010-02-01/date
Rate270,71/Rate
currEUR/curr
unit1/unit
/Day
Day
date2010-02-01/date
Rate194,59/Rate
currUSD/curr
unit1/unit
/Day
Day
date2010-02-02/date
Rate269,24/Rate
currEUR/curr
unit1/unit
/Day
Day
date2010-02-02/date
Rate193,21/Rate
currUSD/curr
unit1/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" pí¹e v diskusním pøíspìvku
...
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