Importing Multiple XML in Access from Folder

[MastaRelaxa]

Hello-

I have been tasked by my manager to try and create a Macro we can use to pull multiple XML files into Access to review and analyze. All XML Files will have the same headers/tables. I did import the structure of the data from one of the XML files, figuring that all that would need to be done is importing of the XML files and appending the data to the already created tables. After research, this is what i came up with, although I am running into an error that is cannot find the file path. Below is the code that I have:

Sub TEST()
Dim fs
Dim fsFolder
Dim fsFile

Set fs = CreateObject("scripting.filesystemobject")
Set fsFolder = fs.getfolder("C:\Users\pierial\Desktop\XML")

For Each fsFile In fsFolder.files
Debug.Print fsFile.Name
Application.ImportXML "C:\Users\pierial\Desktop\XML" & fsFile.Name, acStructureAndData

Next fsFile

End Sub

Now if I use the Application.ImportXML along with Datasource, and only direct it to one file, then I am able to see results. When I do use the code above and it displays the error, it does show the path along with the file it is looking for. So it seems as if it is getting to the folder but not pulling anything out of the folder. Any help would be much appreciated!

Thanks!

 

[sxschech]

"C:\Users\pierial\Desktop\XML" &

Are you missing the final backslash before the file name?

"C:\Users\pierial\Desktop\XML[B][COLOR="Red"]\[/COLOR][/B]" &

 

[MastaRelaxa]

Even when adding the \ is gives me the same error of not being able to find the File Path:

Run-time error '31527':
Path not found: C:\Users\pierial\Desktop\XML\

Thanks!

 

[theDBguy]

Can't do any testing right now but maybe there's a timing issue. Try adding some sort of a delay or check that the previous import is done before importing the next file. Just a thought...

 

[MastaRelaxa]

TheDBGuy - That is a good suggestion, but the files that are in the folder will have been located there for months. Maybe I am totally misunderstanding what you are saying, so please correct me if I am wrong. Thanks!

 

[arnelgp]

it means put some Delay on your code.
maybe the code is running Asynchronous that it is not finished loading the file and yet
another request is again to fetch another file:

Sub TEST()
Dim fs
Dim fsFolder
Dim fsFile
Dim i As double

Set fs = CreateObject("scripting.filesystemobject")
Set fsFolder = fs.getfolder("C:\Users\pierial\Desktop\XML")

For Each fsFile In fsFolder.files
Debug.Print fsFile.Name
Application.ImportXML fsFile.Path, acStructureAndData

i = Timer + 0.5
While Timer < i
    DoEvents
Wend
Next fsFile

End Sub

 

[theDBguy]

TheDBGuy - That is a good suggestion, but the files that are in the folder will have been located there for months. Maybe I am totally misunderstanding what you are saying, so please correct me if I am wrong. Thanks!

It's like this... (and it's just a guess, as I was trying to say earlier). Let's say Access is humming along importing the XML files one after another. However, one of the files happen to be bigger than normal, so it would take Access a longer time than usual to ingest the data. In the meantime, while it's trying to finish this one job, I was speculating the next file is already knocking at the door and maybe already trying to come in, while Access is still busy with the previous file. This could be causing the error, maybe, somehow. Get the idea? Again, it's all just speculation, and one way to test this theory is to add some delay in the code to make sure Access has enough time to process each file one at a time.

 

[sxschech]

Looks like others answered prior to my post being live.

I think dbguy means the timing between when the file is being imported and the next file is being imported. The first import may not be finished before the second tries to go.

Maybe try this:

Sub TEST()
Dim fs
Dim fsFolder
Dim fsFile

Set fs = CreateObject("scripting.filesystemobject")
Set fsFolder = fs.GetFolder("C:\Users\pierial\Desktop\XML")

For Each fsFile In fsFolder.Files
Debug.Print fsFile.name
Application.ImportXML fsFolder [COLOR="Red"]& "\" &[/COLOR] fsFile.name, acStructureAndData
[COLOR="Red"]DoEvents[/COLOR]
Next fsFile

End Sub

 

[MastaRelaxa]

Thank you Everyone, for the input! I am going to try and few things, and will let you know what happens!

 

[NearImpossible]

does your XML files have schema data?

Try changing acStructureAndData to acAppendData

 

[MastaRelaxa]

It worked! I used SXS alteration to the code, adding in the slash after the folder name. Worked flawlessly. I may have more questions around importing XMLs that have two different layouts. One XML would have 3 different tables (Admin Data, Headers, Patient Response), and the other would have the same tables as well as an additional table if they fell within a certain criteria (in this instance any client who had different sampling rates under one Provider number, we call this DSRS).

Near Impossible, I am glad you brought up schema. When you refer to a data schema, is that the same thing as like a file layout (headers of the layout)? I have been researching quite a bit and have seen references to this a lot.

My final code for what worked is listed below:

Sub TEST()
Dim fs
Dim fsFolder
Dim fsFile

Set fs = CreateObject("scripting.filesystemobject")
Set fsFolder = fs.GetFolder("C:\Users\pierial\Desktop\XML TEST")

For Each fsFile In fsFolder.Files
Debug.Print fsFile.Name
Application.ImportXML fsFolder & "\" & fsFile.Name, acAppendData
DoEvents
Next fsFile
End Sub

 

[theDBguy]

It worked! I used SXS alteration to the code, adding in the slash after the folder name. Worked flawlessly. I may have more questions around importing XMLs that have two different layouts. One XML would have 3 different tables (Admin Data, Headers, Patient Response), and the other would have the same tables as well as an additional table if they fell within a certain criteria (in this instance any client who had different sampling rates under one Provider number, we call this DSRS).

Near Impossible, I am glad you brought up schema. When you refer to a data schema, is that the same thing as like a file layout (headers of the layout)? I have been researching quite a bit and have seen references to this a lot.

My final code for what worked is listed below:

Sub TEST()
Dim fs
Dim fsFolder
Dim fsFile

Set fs = CreateObject("scripting.filesystemobject")
Set fsFolder = fs.GetFolder("C:\Users\pierial\Desktop\XML TEST")

For Each fsFile In fsFolder.Files
Debug.Print fsFile.Name
Application.ImportXML fsFolder & "\" & fsFile.Name, acAppendData
DoEvents
Next fsFile
End Sub

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[arnelgp]

using fsFile.Path will return the path + the filename.

 

[NearImpossible]

Sorry for the late response but was traveling yesterday.

Schema data is the structure of the the XML file, i.e. Coding to tell the reading program how to process it.

Depending on how your exports are setup, it could include the Schema information as well as the data. If not all the files have the same Schema, it could cause the not found error.

In a separate database, use the External Data import wizard to import one of your XML files. Open that table and then open your XML file and view the elements (items within the <>) and compare them to your table columns in access to get a little better understanding of the XML layout

acStructureAndData = Structure and Data option
acAppendData = Append Data to Existing Table(s) option

 

[MastaRelaxa]

NearImpossible-

From what I have gathered, using my code to bring in data from these XML files, it appears there could be a total of 4 tables coming out of the XML. Typically it will be 3 tables, but some XMLs will have the additional table if they have stratified sampling (dont worry about what the stratified sampling is, just know that it is a possible table that will be input). The way the XML is set up is as follows:

<Header> (It looks like the stratified sampling info is within the Header, but when imported into Access it is reading Strata as its own table instead of adding it as a field/column in the header table.
<PatientLevelData> (This is not being brought in as a table. The Administrative Info and Patient Response seem to be nested within the PatientLevelData, and these are being brought in as separate tables)

Explaining this in the best way I can; after the Header, the Administrative Table and Patient Response tables go hand in hand. Currently I have no way to tie the Patient Response Data to the Administrative Data since they are two different tables, and they do not have a common denominator.

I didnt know if there was a way to adjust the code to bring all the data from the Administrative table and the Patient Response table, and put it in one table. The main issue I see is that there will not always be patient response data to tie to the administrative data, which in that case I would expect the cells to blank in the patient response data.

I realize this is moving into more advanced coding, but any help would be much appreciated.

Thanks!

 

[theDBguy]

Hi. Are you saying the previous code you posted earlier is not working for you anymore?

 

[MastaRelaxa]

The DBGuy-

No it is still working. Now that I am able to successfully get the data into Access, I would like to take it a step further by combining two of the tables together (the goal being to get the patient response data tied to the administrative data of the patient). The problem right now is that I do not have a common denominator in the Patient Response Table and the Administrative data, since it has been brought into Access.

Thanks!

 

[theDBguy]

The DBGuy-

No it is still working. Now that I am able to successfully get the data into Access, I would like to take it a step further by combining two of the tables together (the goal being to get the patient response data tied to the administrative data of the patient). The problem right now is that I do not have a common denominator in the Patient Response Table and the Administrative data, since it has been brought into Access.

Thanks!

Hi. In that case, I recommend starting a new thread for the new topic and maybe try to post a sample set of data, so we can evaluate a way to combine them. Cheers!