How to import XML files through form?

[RSW]

I am okay with Access itself, but my knowledge of VB is very limited. I am trying to create an Access form that will allow a user to pull in new .xml log files into a database.

So if I have an XML file like

-part-Widget-/part-
-order-A123-/order-
-serial-1001-/serial-

I want to add them into an Access table with columns Part, Order, Serial. I already have a check built to see if the log files are new, I am just struggling with the .xml parsing bit.

I have been searching online for over an hour and the examples I am finding are making my head spin. Does anyone know of a simple example--no checking for errors, no nodes, etc.--of VBA code that pulls XML data into an Access table?

Thanks in advance!

 

[RSW]

I should also note that I am trying to pull in a bunch of .xml files, into the same table.

So even Get External Data--Import (which is not the way I want to do it) doesn't work, because it's only letting me bring in one file at a time??

 

[KenHigg]

Hopefully your xml structure is going to remain consistant because you'll need to get past a significant learning curve using DAO/recordsets and text manipulation functions. How much experience do you have with these?

 

[RSW]

Hopefully your xml structure is going to remain consistant because you'll need to get past a significant learning curve using DAO/recordsets and text manipulation functions. How much experience do you have with these?

Virtually none

 

[RSW]

Oh and--the xml structure will definitely remain consistent. The files I want to load in are already all there, and all in the same format.

 

[KenHigg]

If this is a once in a while thing then you can simply import the xml and let Access do all the work. But if you're want a push button tool to do it say everyday, then I'm guessing you need to build the script from scratch.

The basic deal is that you would attach to the source xml as a text file, and using DAO/recordsets, navigate down through the rows one by one and using built in text functions such as trim(), len(), etc, parse out the data the way you would ultimately need it...

Hopes this all makes sense...

 

[RSW]

If this is a once in a while thing then you can simply import the xml and let Access do all the work. But if you're want a push button tool to do it say everyday, then I'm guessing you need to build the script from scratch.

The basic deal is that you would attach to the source xml as a text file, and using DAO/recordsets, navigate down through the rows one by one and using built in text functions such as trim(), len(), etc, parse out the data the way you would ultimately need it...

Hopes this all makes sense...

It does...and that's what I'm slowly working on.

But if I were to import the xml, is there any way to do a whole directory's worth of files at once?

Thanks for your help.

 

[KenHigg]

I've never done it but I could only do it off the menu importing one file at a time... Someone else may know how. How many files are there and is it a one time deal?

 

[KernelK]

Once you can parse one file, all you need to do is to loop your function over again for each file you wish to parse. You can use Access' Open File Dialog to select your multiple files and then pass this list of files one at a time to your parser which does all the work.
BTW - manually parsing an XML file is definitely not the easiest way to go, especially if you are just going to be using string manipulation functions. The Microsoft XML Library (check your references in the VBA editing window) has all of the functions required to properly parse an XML document, but there is a learning curve to it as you have to know how to use the objects properly. But if you are already teaching yourself VBA to do this, then why not teach yourself the VBA required to do the task properly....instead of trying to reinvent the wheel? Check out the thread John Woody and I had on this topic, there are some interesting code snippets there: http://www.access-programmers.co.uk/forums/showthread.php?t=116258

Depending on how far along you are with the parsing of your document, I would not mind helping to use the MS XML Library if you would like. Just my thoughts....

 

[RSW]

Hi KernelK,

Since I posted this, I have found someone who is knowledgeable about xMII software, and says he can help me out and that this would be an ideal way to handle this. Are you familiar with the software, and if so, do you agree?

(As you may have seen in another thread, I am also working with a program that parses flat files, and that had been what I was hoping to do at first.)