Importing Multiple XML in Access from Folder (1 Viewer)

MastaRelaxa

New member
Local time
Today, 16:20
Joined
Oct 24, 2019
Messages
8
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

Registered User.
Local time
Today, 15:20
Joined
Mar 2, 2010
Messages
791
"C:\Users\pierial\Desktop\XML" &

Are you missing the final backslash before the file name?
Code:
"C:\Users\pierial\Desktop\XML[B][COLOR="Red"]\[/COLOR][/B]" &
 

MastaRelaxa

New member
Local time
Today, 16:20
Joined
Oct 24, 2019
Messages
8
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

I’m here to help
Staff member
Local time
Today, 15:20
Joined
Oct 29, 2018
Messages
21,358
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

New member
Local time
Today, 16:20
Joined
Oct 24, 2019
Messages
8
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:20
Joined
May 7, 2009
Messages
19,169
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:
Code:
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

I’m here to help
Staff member
Local time
Today, 15:20
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 15:20
Joined
Mar 2, 2010
Messages
791
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:
Code:
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

New member
Local time
Today, 16:20
Joined
Oct 24, 2019
Messages
8
Thank you Everyone, for the input! I am going to try and few things, and will let you know what happens!
 

NearImpossible

Registered User.
Local time
Today, 17:20
Joined
Jul 12, 2019
Messages
225
does your XML files have schema data?

Try changing acStructureAndData to acAppendData
 

MastaRelaxa

New member
Local time
Today, 16:20
Joined
Oct 24, 2019
Messages
8
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:

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

I’m here to help
Staff member
Local time
Today, 15:20
Joined
Oct 29, 2018
Messages
21,358
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:

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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:20
Joined
May 7, 2009
Messages
19,169
using fsFile.Path will return the path + the filename.
 

NearImpossible

Registered User.
Local time
Today, 17:20
Joined
Jul 12, 2019
Messages
225
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

New member
Local time
Today, 16:20
Joined
Oct 24, 2019
Messages
8
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

I’m here to help
Staff member
Local time
Today, 15:20
Joined
Oct 29, 2018
Messages
21,358
Hi. Are you saying the previous code you posted earlier is not working for you anymore?
 

MastaRelaxa

New member
Local time
Today, 16:20
Joined
Oct 24, 2019
Messages
8
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

I’m here to help
Staff member
Local time
Today, 15:20
Joined
Oct 29, 2018
Messages
21,358
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!
 

Users who are viewing this thread

Top Bottom