Solved

Importing XML Files To Excel

Posted on 2014-09-26
4
160 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

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…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

717 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