We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
5 days, 14 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Import XML file into Excel
Posted on 2010-11-15
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
BackUpJob1349.xml
Book3.xlsx
[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
4-
4
2
10 Comments
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
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
Active 3 days ago
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
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
Active 2 days ago
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.
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.
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.
I am confident about the direction.
Active 3 days ago
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
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
Active 3 days ago
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
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
Active 3 days ago
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
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
Featured Post
Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…
Suggested Courses
Course of the Month5 days, 14 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.