Solved

Importing XML Files To Excel

Posted on 2014-09-26
4
156 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
ID: 40347185
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
ID: 40379965
Hey craisin. Any luck with that information?
0
 
LVL 13

Accepted Solution

by:
Chris Raisin earned 500 total points
ID: 40380446
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

To stay competitive, modern businesses must adapt and stay innovative, and this is increasingly only possible by working with outside talent. Managers and executives have understood the power of outsourcing for quite some time, but traditional clien…
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

815 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

13 Experts available now in Live!

Get 1:1 Help Now