Import Multiple XML files into Access

Posted on by

So boss emails me and asks hey can you import 2,900 XML files into this attached Access database. Of course I say sure thing, no sweat. I knew that I was not the first person in the world with the need to import XML files into access so was confident I could find something on Google to facilitate.

The first thought I had was just throwing the data in a SQL Server database (boss mentioned SQL Server is a fine destination also) using an SSIS pacakge. I know SSIS has a sweet foreach loop container. Optimistically I fired up the Visual Studio designer, thinking that I would actually be able to figure this SSIS thing out pretty quick.

Wrong. Man I do have a desire to learn SSIS (well I at least tell myself I do) but every time I sit down to actually do it, I get flustered and think of an easier way to perform the task in a VBScript, DTS Package, etc.

I went back to Google thinking there had to be some freeware software out there to help with the import. The closest I found was some shareware version of an multi-file XML import. However with the shareware version you could only work with one file at a time. No thanks.

After uninstalling the shareware software, I stumbled on this article from Technet. This looks way to easy to actually work was my first thought.

Set objAccess = CreateObject(“Access.Application”)
objAccess.OpenCurrentDatabase “C:\Scripts\Test.mdb”
objAccess.ImportXML “c:\scripts\test.xml”, acAppendData

That’s it??? Really is it that simple? I fired up my Notepad++ and quickly modified the script to line up with my file paths. Double clicked on the resulting VBS file and poof like magic the XML files were importing. So then I just wrapped a Files Collection around it and whamo, I am watching wscript.exe do the rest. So simple.

Final Script:

Set objAccess = CreateObject(“Access.Application”)
objAccess.OpenCurrentDatabase “c:\Scripts\db3.mdb”
set fso = CreateObject(“Scripting.FileSystemObject”)
Set f = fso.GetFolder(“C:\XML”)
Set fc = f.Files
For Each f1 in fc
objAccess.ImportXML f1.Path, acAppendData
Next

7 thoughts on “Import Multiple XML files into Access”

    • Thanks Kenneth. Yeah its either you declare the const or just use the name. Must have been a little lazy that day.

      Reply

      • Having issues using the VBS. Can you address “Ray’s” post….having same issues.

        Thanks

        Rusty

      • Rusty and Ray,

        I just noticed that if you copy the code directly from wordpress, you get a weird looking quote when you paste it into a text editor.

        Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "c:\Scripts\db3.mdb"
set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("C:\XML")
Set fc = f.Files
For Each f1 in fc
objAccess.ImportXML f1.Path, acAppendData
Next

        Can you try the above version.

  3. hey Guys

    I am a not good with Technology. I just saw this post and tried replicating this script for my XML files. It gives me an error , saying VBScript error in compiling in Line 1.

    here is what I wrote , can you help?

    Set objAccess = CreateObject(“Access.Application”)
    objAccess.OpenCurrentDatabase “C:\Users\aray042\Desktop\COX\psft\COX.mdb”
    set fso = CreateObject(“Scripting.FileSystemObject”)
    Set f = fso.GetFolder(“C:\Users\aray042\Desktop\COX\psft”)
    Set fc = f.Files
    For Each f1 in fc
    Const acAppendData = 2
    objAccess.ImportXML f1.Path, acAppendData
    Next

    Reply

    • Ray can you try this version

      Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "c:\Scripts\db3.mdb"
set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("C:\XML")
Set fc = f.Files
For Each f1 in fc
objAccess.ImportXML f1.Path, acAppendData
Next
      Reply

Leave a reply to Kenneth M. Nielsen Cancel reply