How to add data from multiple xml files into a excel sheet (2003) or ms access (2003) file?

I'm trying to add data from a bunch of xml files (approx 1600!) into a more readable and workable excel file, or ms access file, or actually to any kind of format that would be in a "table" format rather than in xml.  It's a parts catalog, so that's why I want to have the data in a table format instead for future upload into our legacy system. The solution must loop all the files in the directory to read in all the data from the files in there (all xml files).

The key data I'm looking for in these files are the values for:
- "pnr"
- "mfr"
- "kwd"

found under the "pnrmfr" tag or whatever it's called.

Pls see if either one of you guys are able to sort this out, would be super!

Thanks  / Peder



 11-13-01-01.xml 11-16-01-01.xml
pitamanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

quizwedgeCommented:
I've never used the program, but would something like http://www.xml-converter.com/ work for you? Looks like it has an option to output to Excel.
0
quizwedgeCommented:
Also found a free converter at http://www.cobynsoft.com/products/freeware/cobynsofts-data-viewer/ Again, I haven't used the program, but I figure free is better, if it works for you.
0
pitamanAuthor Commented:
Thanks quizwedge for your quick reply. But, I simply cannot get either of these two softwares to produce a listing with the values for pnr, kwd and mfr on the same line in, for example, an excel sheet. Any more suggestion of programs (free or not free) is highly appriciated!
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

quizwedgeCommented:
Can't figure out how to do it with the free one, but the paid one should work like this

1. Select the file
2. Under tools, choose Global Tables
3. Click on pnrmfr. That should be what you want.

Then, to export it,
1. Click on file -> export tables
2. Uncheck all but pnrmfr
3. Choose your file type
4. Click convert

I think that's what you're looking for. Let me know if I missed something.
0
pitamanAuthor Commented:
Hi again,

it works just fine when working with one file at a time, I get the columns and rows that I want with great ease. But it seems like the batch processing of files doesn't have that feature to pick only a few of the available informations, with that function I get the entire information from each file unfortunatelly. Maybe the full version has some other features in it..
0
quizwedgeCommented:
You'll have to do a little bit of cleanup work on the file, but if you choose the "Flat Table" option from File -> Convert Files From Folder it appears to have all of the pnr information. You'll just want to sort by the columns you're interested in so that you can delete the rows that have no data for those columns.

Another option, since it sounds like you're okay with spending money for the solution is to check out something like http://www.vworker.com/ I knew a little about them back when they were called Rent A Coder. From there you could ask for something that had the functionality of the converter, but tailored specifically for your file format. I figure it shouldn't take a knowledgeable coder more than an hour or two if they limit it to the simple inputs of "choose a folder", "choose a destination" and then only output to Excel. Unfortunately, I'm running around crazy enough I don't have that hour or two to whip up the solution for you.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pitamanAuthor Commented:
Hello!
I found a way to do it in the end. I started to use the software "Join (Merge, Combine) Multiple XML Files into one software" by Sobolsoft first to join the files.

Then I used "Replace Pioneer" to get rid of the extra tag's that every file brought into the combined file. It turns out that Replace Pioneer got lots of great functionality, more than I ever will use I think..

Thanks quizwedge for the hint about vWorker, might come in handy in a project soon..
0
pitamanAuthor Commented:
Great replies.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.