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
Solved

Import XML file into Excel

Posted on 2010-11-15
10
390 Views
Last Modified: 2012-05-10
Attached is a XML file and an Excel spreadsheet after I have imported this XML into it. Is it possible to rearrange the resulting spreadsheet so that the columns are Session#, SourceHost, SourcePath, StartTime, etc with the corressponding values listed by session #? Can this be done via the XML import process into Excel Or is VBA code necessary? Either way I would appreciate assistance. The current format is not manageable. Thanks.
BackUpJob1349.xml
Book3.xlsx
0
Comment
Question by:StampIT
  • 4
  • 4
  • 2
10 Comments
 
LVL 13

Expert Comment

by:gbanik
ID: 34137228
Use an XML parser to parse through the XML file and populate the Excel file. There may not be a straight forward way of achieving this. Use the Microsoft XML Library to do so.

Add it as reference in your project
http://msdn.microsoft.com/en-us/library/ms763701(VS.85).aspx

Check this to parse your data
http://msdn.microsoft.com/en-us/library/aa163921(office.10).aspx
0
 
LVL 7

Expert Comment

by:Bruce Cadiz
ID: 34138321
StampIT,

Here's a different approach by replacing your XML tags with HTML then opening in Excel.
Note: It will open the XML file and replace listed text string / tags with HTML tags then open in Excel. Seemed like the simplest approach since your XML was similarly structured to an HTML table.

Bruce ee-Q26615394.xls
0
 

Author Comment

by:StampIT
ID: 34140700
Thanks for the replies. Both of these are way over my head. I thought there might be a simpler way of performing this task.
Bruce: I ran your code with the file I had but it resulted in data in the first row in a language I did not understand. I probably did something wrong.
If I can I should probably withdraw the question.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
LVL 13

Expert Comment

by:gbanik
ID: 34140776
StampIT as I mentioned in my comment, there might not be a simpler way. But actually less difficult than u are thinking. This method is a tried and tested way and u can definitely give it a try.
I am confident about the direction.
0
 
LVL 7

Expert Comment

by:Bruce Cadiz
ID: 34140881
StampIT,

Here is your sample XML file and I've modified the Excel file to save the output in the same directory. It should do exactly what you have asked.

Save them to a folder that you have read/write access to. Then open the Excel file and hit the button to Select the XML File. You are running Windows aren't you? What version of Excel do you have?

Bruce ee-Example.zip
0
 

Author Comment

by:StampIT
ID: 34140958
Bruce,
      I am running Windows Vista and Excel 2007. Thanks
0
 
LVL 7

Accepted Solution

by:
Bruce Cadiz earned 500 total points
ID: 34141334
StampIT,

Then it should work, (I'm running XP and Office 2003 SP3). The funny character output is a question mark in a weird character set on the first line (probably and XML creation error) but essentially it's what is in attached (HTML file). I've also included .jpg's of the output in Excel and the Error message for the weird character.

Bruce ee-files.zip
0
 

Author Closing Comment

by:StampIT
ID: 34146330
Works exactly how I want it to.
0
 
LVL 7

Expert Comment

by:Bruce Cadiz
ID: 34146646
StampIT,

Thanks. I believe the suggestions by gbanik regarding the XML parser is something you should look at. While as he stated it may seem complicated, if you were to eventually incorporate it, you would have more functionality / flexibility in the long run, especially if your XML data structures change in the future.

Bruce
0
 

Author Comment

by:StampIT
ID: 34146807
Bruce,
      Thanks. I will keep that in mind.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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