XML Manipulation with Excel

[Guest]

I want to use Excel 2003's XML abilities to download information from Amazon.com directly into MS Excel. Everytime I try to download the data directly though, the format is completely unuseable. How can I get the data in the link below parsed properly so that each of the items are in a seperate row and each item's fields are in a seperate column within their respective rows

http://xml.amazon.com/onca/xml3?t=b...rt=+salesrank&offer=All&type=heavy&page=&f=xm

P.S. Please don't abuse the link above or spam it across the internet or Amazon will shutdown my access to their XML fields. If you are interested in getting this type of access to Amazon.com simply go to the link below, get a developer's token and go from there. Thanks

http://www.amazon.com/gp/browse.html/ref=smm_sn_aws/002-9541831-6765624?_encoding=UTF8&node=343536

 

[Debra Dalgleish]

I clicked on the link, and the page opened. In Netscape, it looked like
an unbroken text string. In Internet Explorer, it looked like a
structured XML file.

In both browsers, I saved the file as an XML file (File>Save As)

Then, in Excel 2002, I opened the saved XML file.

 

[Debra Dalgleish]

To import it directly into Excel (tested in Excel 2002 and 2003), choose
Data>Import External Data
Choose New Web Query
Paste the link into the Address box
Click the Import button
Select a starting cell, and click OK

 

[Guest]

Debra

This doesn't accomplish what I am looking for though. I want to have each product (i.e. SanDisk 256MB . . .) on only one row of the Excel spreadsheet with all of the items other XML data in seperate columns of the same row. Your solution does the same thing as simply going to Data Import Data and pasting the link in the file field to open it up

Please, please help. I want to be able to use this XML data in a useful format

Thanks

David Payne

 

[Harlan Grove]

I want to use Excel 2003's XML abilities to download information from
Amazon.com directly into MS Excel. Everytime I try to download the data
directly though, the format is completely unuseable. How can I get the data
in the link below parsed properly so that each of the items are in a seperate
row and each item's fields are in a seperate column within their respective
rows?

...

I've read your follow-up to Debra. It looks like you define the information
between <Details...> and </Details> tags as items, but it's unclear whether you
want item records in multiple rows in one column or multiple columns in one row.
If you want the latter (rows are records), you may not be able to do this
because it looks like some records could have more than 256 fields since the
BrowseList section of each record appears to be variable size (others may also
vary in size, but this one sticks out).

You may be best off with a two pass approach, the first pulling the XML data
into Excel in whatever format it does, then building the layout you want in a
different area using formulas to refer to the raw data.

P.S. Please don't abuse the link above or spam it across the internet or
Amazon will shutdown my access to their XML fields. . . .

...

You're the one who posted the url to a newsgroup, so it's going to sit in the
Google Groups archives as long as they're maintained. This was a highly unwise
thing to have done!

 

[Debra Dalgleish]

Maybe this, combined with Harlan's suggestions, will help:

In Excel 2003, choose File>Open, and select the XML file
From the Open XML dialog box, choose 'Use the XML Source Task Pane'
Click OK
Drag elements from the map onto your worksheet, in the position you'd
like them to appear.
Select a cell that is mapped
On the list toolbar, click the Import XML Data button
Choose the XML file, click Import
You can use the dropdowns to filter the imported data