Mapping XML to columns in Excel 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JackASP - 30 May 2007 19:44 GMT

Hi. I've been trying to figure out a way to map an XML file to columns in an
Excel 2003 Spreadsheet. The issue I am having is that the xml file is
designed much like the following:

<item>
...
<description lang="EN"></description>
<description lang="FR"></description>
...
</item>

Now, I have two columns in the spreadsheet that are as follows: "Description
(English)" and "Description (French)". I would like these two columns
associated with the two "description" tags in the XML file. The problem is,
in the XML data tree in the right window, it shows only one "Description"
element. But, I need 2; one for the English, and one for the French. I have
though of using something like:

<item>
...
<description>
<English></English>
<French></French>
</description>

but, I would prefer it the other way. Any help would be much appreciated.

Thanks,

Jack

Reply to this Message

JE McGimpsey - 31 May 2007 04:34 GMT

> Hi. I've been trying to figure out a way to map an XML file to columns in an
> Excel 2003 Spreadsheet.

You may have more luck posting to a WindowsXL oriented newsgroup, rather
than this one dedicated to MacXL, say

microsoft.public.excel.misc

I wish I were more familiar with XL03's XML import capabilities -
perhaps someone else will happen along who knows...

Reply to this Message

gimme_this_gimme_that@yahoo.com - 31 May 2007 05:38 GMT

Excel has some XML Reading/Parsing capability via MSXML4.0 on the PC,
but that reference library isn't available on the Mac.

The way I'd approach this problem (strickly speaking based upon the
clip in the post) is to transform the file using Java and Xalan from
the command line. The tool would be XSL not Excel.

Alternatively, one could also parse the file using Perl's
XML::Mini::Document module and from there one could either:

Write a complete Excel 2003 worksheet using
Spreadsheet::WriteExcelXML, or

Insert the data into an Excel Workbook (right from the same Perl
script) with Mac::Applescript

I have a hunch that if I posted either solution the original poster
would either not find the code useful (because he posted a very small
set of the XML file he is dealing with ) or else he'd be at a loss
about installing the necessary Perl modules and using Perl.

Reply to this Message

Mac Forum / Applications / Excel / May 2007

Mapping XML to columns in Excel 2003