Solved

Importing XML Files To Excel

Posted on 2014-09-26
4
149 Views
Last Modified: 2014-11-14
Can anyone recommend something that tells how to import XML files to Excel (or Access) that starts at the "for dummies" level? I'm pretty conversent with both Excel and Access but don't know much about XML files, or "Schema" or any of that stuff.

Thanks.
0
Comment
Question by:Buck_Beasom
  • 2
4 Comments
 
LVL 13

Expert Comment

by:Chris Raisin
Comment Utility
XML is basically text that has data seperated by tokens.
If we know the names of the tokens (as showing in the XML) we can ascertain the valu by reading in the XML file and searching for the required Token.

There is code somewhere in my library that does this, and I will work on this later tonight after I return from my duties at church this afternoon.

If you can hold on for a few hours, I think I might have a solutoion for you.

Cheers

Chris (craisin) - Melbourne, Australia
0
 

Author Comment

by:Buck_Beasom
Comment Utility
Hey craisin. Any luck with that information?
0
 
LVL 13

Accepted Solution

by:
Chris Raisin earned 500 total points
Comment Utility
Thanks for getting back to me....I forgot all about it! Sorry!

Can you supply some dummy XML code that looks like your XML? I definately have some code and I can easily whip it up foryou and supply a step by step way of processing the data.

BTW, will the XLM you use be changeing in structure often?

Look inside your XML using a text editor and your will see what are called "Tags". They are text items starting with "<" (Open arrow-head) followed by one or more words and then finishing with ">" (Closed arrow-head).
Some Tags take on thsis sort of format:   "<TITLE>This is a title</TITLE>" where the "<TITLE> is the opening Tag  and "</TITLE> is the closing Tag for the data item (also known as "element") called TITLE. The value contained in the element is contained between the opening Tag and the closing Tag.

The code locates the tags and processes them, finding the opening Tag and Closing Tag, and ascertaing the values therein. It can become quite involved, because there are often "Tags within Tags" (so to speak).

An example of a "Purchase order Item might be like the following,with multiple items in the element "Item" as well as more than one element contained in the element "ShipTo". (I dragged this out of some sample code
I had hanging around). LOL

<po:purchaseOrder orderDate="2001-01-01" xmlns:po="http://www.ibm.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.ibm.com PurchaseOrder.xsd">
  <shipTo country="US">
    <name>Alice Smith</name>
    <street>125 Maple Street</street>
    <city>Mill Valley</city>
    <state>CA</state>
    <zip>90952</zip>
  </shipTo>
  <billTo country="US">
    <name>Robert Smith</name>
    <street>8 Oak Avenue</street>
    <city>Old Town</city>
    <state>PA</state>
    <zip>95819</zip>
  </billTo>
  <po:comment>Hurry, my lawn is going wild!</po:comment>
  <items>
    <item partNum="872-AA">
      <productName>Lawnmower</productName>
      <quantity>1</quantity>
      <USPrice>148.95</USPrice>
      <po:comment>Confirm this is electric</po:comment>
    </item>
    <item partNum="926-AA">
      <productName>Baby Monitor</productName>
      <quantity>1</quantity>
      <USPrice>39.98</USPrice>
      <shipDate>2001-07-21</shipDate>
    </item>
  </items>
</po:purchaseOrder>

Open in new window


It is 3.50am and I had better get to bed, but I will get back to you tomorrow....promise! (Sorry)  (blush)   :-)
Just let meknow what your HTML looks like (avoid email addresses or confidential information though).

Cheers
Chris (Melbourne, Australia)
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Outline Suppose you have some simple text based data in Excel that you would like to display as a PowerPoint presentation. Of course it would be possible to write some fairly complex VBA code that created a new slide for each line of the Excel data…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now