Solved

Importing XML Files To Excel

Posted on 2014-09-26
4
159 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

733 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