Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2744
  • Last Modified:

Import XML to MS Access Table using VBA

Hi, we have a customer recall program which generates a lot of data in XML format. What we would like to do is to bring the XML data into MS Access and analyse it.

The recall program generates two XML files, a reminder file and a suppression file. I would like to import each (one at a time) into a single table for each XML dataset, i.e. tblSuppression and tblReminders.

The problem I am having is that a simple import splits the data into several tables. I understand that I may be able to use an XSLT file to map to the XML but I have no idea where to begin with that.

Searching EE I have found a number of good leads but being a complete novice with XML I've found myself getting a little lost.

I have attached two example files (the data has been altered to protect customer identity). Any help you can offer would be great!

Thanks entity---suppression1648.xml sms-reminders1648.xml
0
jonlake
Asked:
jonlake
  • 8
  • 6
  • 3
  • +1
1 Solution
 
NicKnoCommented:
Hi jonlake,

you can read xml-files in VBA like this:

 
Dim oXMLDoc As New MSXML2.DOMDocument
Dim CurrNode As MSXML2.IXMLDOMNode
Dim oNodeList As MSXML2.IXMLDOMNodeList
        
oXMLDoc.LoadXML [File]
        
Set CurrNode = CurrNode.ChildNodes(0)
Set oNodeList = CurrNode.ChildNodes

For Each CurrNode In oNodeList
    'Here you can do the reading stuff e.g.
    CurrNode.Text
    'or
    CurrNode.attributes
Next

Open in new window


You also have to do the "XML-file selection"- and "Save into table"-stuff.
0
 
jonlakeAuthor Commented:
Hi NicKno, I'm not sure I quite follow that. I initially tried the code below to bring in the XML files but it created/appended to individual tables. How would your suggestion work with this?

Private Sub cmdImport_Click()

Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim strPath As String ' Path to file folder

     strPath = "C:\Temp\Marketing XML\"
     strFile = Dir(strPath & "*.XML")

     While strFile <> ""
          'add files to the list
         intFile = intFile + 1
         ReDim Preserve strFileList(1 To intFile)
         strFileList(intFile) = strFile
         strFile = Dir()
     Wend

      'see if any files were found
If intFile = 0 Then
         MsgBox "No files found"
         Exit Sub
End If

     'cycle through the list of files
     For intFile = 1 To UBound(strFileList)
         Application.ImportXML strPath & strFileList(intFile), 2
     Next intFile

     MsgBox "Import Completed"

 End Sub
0
 
NicKnoCommented:
The point with that way is, that you also could import the xml-files manually with the XML-Import-Assistant of access. The result would be the same.

If you want two single tables, especially for the "reminders" you have to analyse the structure by yourself as i descibed above.

I try to give you an short example soon.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
NicKnoCommented:
Hi jonlake,

this code should work for your suppressions. The code requires an existing table [tblSuppressions] and the file at the right location. The table consists of the columns [storenumber], [customernumber], [contactmedium], [contactvalue]. I hope it helps.

 
Public Sub XmlLoad()
    Dim oDoc As MSXML2.DOMDocument
    Dim oStore As IXMLDOMNode
    Dim oCustomer As IXMLDOMNode
    Dim oRoot As IXMLDOMNode
    Dim oCustomers As IXMLDOMNodeList
    Dim oValue As IXMLDOMNode
    
    Set oDoc = New MSXML2.DOMDocument
    
    oDoc.async = False
    oDoc.validateOnParse = False
    oDoc.Load ("Z:\entity---suppression1648.xml")

    Set oRoot = oDoc.documentElement

    For Each oStore In oRoot.childNodes
        Dim storenumber As String
        Dim customernumber As String
        Dim contactmedium As String
        Dim contactvalue As String
        Dim sql As String
        
        storenumber = oStore.Attributes.Item(0).nodeTypedValue
        Set oCustomers = oStore.childNodes
    
        For Each oCustomer In oCustomers
            For Each oValue In oCustomer.childNodes
                Select Case oValue.nodeName
                    Case "customernumber"
                        customernumber = oValue.TEXT
                    Case "contactmedium"
                        contactmedium = oValue.TEXT
                    Case "contactvalue"
                        contactvalue = oValue.TEXT
                End Select
            Next
            sql = "INSERT INTO [tblSuppressions] ([storenumber], [customernumber], [contactmedium], [contactvalue]) VALUES (""" & storenumber & """, """ & customernumber & """, """ & contactmedium & """, """ & contactvalue & """)"
            CurrentDb.Execute (sql)
        Next
    Next
End Sub

Open in new window


1
 
jonlakeAuthor Commented:
Sorry NicKno, but how do I actually run this? Should I be adding to the reference library?
0
 
NicKnoCommented:
You have to add a Module to your AccessDatabase and paste the code into it. For this you have to use a .mdb, not an .accdb

Then you can try to start the Sub by the "play"-Symbol in the VBA-Window.

If it doesn't work. Make sure you added "Microsoft XML, v6.0" to your VBA-Project.

0
 
danishaniCommented:
Another option is using the Application.ImportXML method.

For example:
Application.ImportXML "C:\YourLocation\YourXMLfile.xml", acStructureAndData

Open in new window


If you already have the Tables, you can use the acAppendData option at the end.
Or if you only want to Import the Structure, use the option acStructureOnly.

Hope this helps,
Daniel
0
 
jonlakeAuthor Commented:
Hi danishani, that looks crazily simple, I look forward to testing that. I'm in transit on my way to London at the moment but will try it later. It does look so much simpler than other EE questions and answers I've read over the past couple of days.

Thanks for your suggestion and I will of course let you know how well I got on.

Regards, Jon
0
 
danishaniCommented:
Hi Jon,

Yes, it worked quite well, had a test with your files, and all imported well.

Goodluck and have a safe journey,

Cheers,
Daniel
0
 
jonlakeAuthor Commented:
Hi Daniel, the routine appears to create six separate tables. I'm looking to bring the data into a single table, i.e the reminders.xml needs to append to tblReminders.

Of course I may be doing something quite wrong??
0
 
danishaniCommented:
Hi Jon,

Well, thats how the XML file is build up as 6 different tables with content.
If you want to have them all in one Table, its either do a merge after the Import (append Query).

Or write a custom made import XML with the code NicKno provided using the XML DOM.
Thats however quite some coding, well using a couple of Append Query's will do the job without to much trouble.

Hope this helps,
Daniel
0
 
jonlakeAuthor Commented:
Hi Daniel, it looks like I'll have to do it the hard way.

There is no consistent identifier within each table that would allow appends to work reliably. The store table will only contain a few hundred stores whilst the customer table can contain thousands. I'll make sure to let you know how I get on.

Thanks, and have a great day.

Jon
0
 
aikimarkCommented:
let the import produce separate tables and then join the tables into a single view of the data with a query.
0
 
jonlakeAuthor Commented:
Hi NicKno, I got your code to work beautifully for the suppressions.xml. I'm struggling to apply the same approach to the more complex reminders.xml. Can you help?

Thanks, Jon
0
 
NicKnoCommented:
Hi jonlake,

the reminders.xml has a very relational structure. It is possible to flaten the structure to one table (also via SQL after the fast import). But it doesn't make much sense on database matters.

However, I will try to get to some code for you.
0
 
NicKnoCommented:
I must admit, this was a tough one. See the file attatched. ReadXMLReminder.txt
0
 
jonlakeAuthor Commented:
Thanks NicKno, I'm travelling again for work until Monday so will test your solution then. I really do appreciate all your help with this.

I'll let you know how it goes. Have a great weekend.

Regards, Jon
0
 
jonlakeAuthor Commented:
Excellent solution!

I've married this up to a file location module which now gives me a very rounded solution. Thank you so much for taking the time to help me with this, it's very much appreciated.

Best regards, Jon
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now