[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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
0
pitaman
Asked:
pitaman
  • 4
  • 4
2 Solutions
 
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
Industry Leaders: We Want Your Opinion!

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!

 
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
 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now