Importing multiple XML files in bulk via append

[Awesome!]

I have multiple XML files that I would like to import in one step into Access 2010. The files append to each other and each XML file has the same structure. Each file is given to me with the same file name but the ending will be numbered (_1, _2, _3, etc...)



I have seen many different ways to import XML files in bulk on several websites but I just can't get it to work.

Can someone redirect me to a method that has worked for them? Thanks.

[rpeare]

Are your files importing correctly if you do them one at a time using the import function from the menus in Access?

XML files can be rather picky about how well the import.

If you are able to import the files by hand without a problem you can cycle through files in a folder using filesystem object

dim fs
dim fsFolder 
dim fsFile

set fs = createobject("scripting.filesystemobject")
set fsfolder = fs.getfolder("PUT YOUR PATH HERE")

for each fsfile in fsfolder
    debug.print fsfile.name
next fsfile

you can use filesystemobject commands to move the files to a 'completed' location as well so that anything your 'import' folder is considered a good file.

[Awesome!]

Hi rpeare,

My XML files import perfectly when done manually. I am still a novice when it comes to VBA coding.

I plugged this code into the module and entered the path in the place specified with the quotes included - nothing else was changed. I get the following error:

"Run-time error '438': Object doesn't support this property or method"

What am I doing wrong?

Thanks.

[rpeare]

oops forgot one tiny thing

change

for each fsfile in fsfolder

to

for each fsfile in fsfolder.files

[Awesome!]

Thanks again. So this time the file names appeared in the immediate window but I can't see the tables when I return to the Access interface from the VBA module.

Again, I am still a novice with this VBA thing so maybe I am entering into the wrong place in VBA?

Should the tables appear in Access after running the code or do I have to do another step to get this done?

Thanks again for your help...

[rpeare]

The code I gave you was to cycle through files in a folder, you said you had an import process that worked so where you have the debug.print fsfile.name you can adopt the fsfile.name as a variable, dump it into your import expression and process all the xml files at one time which was your stated goal.


so for instance if you have a line:

application.importxml "c:\testfolder\" & fsfile.name, xmlswitch

where xmlswitch is acstructureanddata or acstructureonly

[Awesome!]

Hi rpeare,

Sorry for being so slow at this - my VBA skills are very basic. My import process was really just the ability to import the various XML through the normal import button in Access. Sorry for not being clearer.

Could you give me the whole code that would allow me to import the various XML files and bring in into Access with all the tables showing? I need it in this format so that I can export the various tables as an excel file and import it into STATA to do some analysis.

Thanks again for your time and patience.

[rpeare]

... I pretty much did I thought, I do not use xml files so I have nothing to test with, if you attached some xml files to this thread I could take a look but your original post said you had a working import, it should be as easy as cycling through files (the code I gave you) and just substitute in the file names in the application.importxml function.

[Awesome!]

So I think I managed to make it work. I tried the code first with xmlswitch but I got an error. I tried it without but I got separate tables for each XML (TABLE, TABLE1, TABLE2, etc...) instead an appended single TABLE.

I used acAppendData instead. I think it worked. Here is my final code:

Sub XMLImport()
Dim fs
Dim fsFolder
Dim fsFile

Set fs = CreateObject("scripting.filesystemobject")
Set fsFolder = fs.getfolder("D:\XML")

For Each fsFile In fsFolder.files
    Debug.Print fsFile.Name
    Application.ImportXML "D:\XML\" & fsFile.Name, acAppendData
    
Next fsFile

End Sub

I will look it over to see if it works but I think it is fine. Thanks again for your help.

[rpeare]

note in my post #6

where xmlswitch is acstructureanddata or acstructureonly

you have to define what you want to do with the import

[Awesome!]

Cool. I will play around with the different xmlswitch options. Thanks again for your help.