Import multiple XML files into MS Access

[sam28bul]

I've my small office phone calls DB, saved now into multilevel enclosed folders structure by year, month, date (i.e. folder 2017 contains month folders 1,2,3...12, each of those contains day folders 1,2,3...30,31). Each day folder contains multiple pairs of XML and WAV files (named like this: 20180824181025-0000-P7D53Z.xml, 20180824181025-0000-P7D53Z.wav), each pair is a recording of a single phone call. All XML files have the same structure:

    <?xml version="1.0"  encoding="ISO-8859-1"?>
    <CALL>
      <ID>20180824181025-0000-P7D53Z</ID>
      <SIP-ID>1be206584b8130a605a71b867f990e8d@82.212.65.23</SIP-ID>
      <SUCCEED>true</SUCCEED>
      <CALLER>
        <IPADDR>82.212.65.23:5030</IPADDR>
        <NAME>"4256781713" <sip:14256781713@sip.ca.bonco.com:5030></NAME>
        <AUDIO>G711(ulaw)</AUDIO>
      </CALLER>
      <CALLEE>
        <IPADDR>192.168.1.195:8550</IPADDR>
        <NAME><sip:2195272326@sip.ca.bonco.com:5030></NAME>
        <AUDIO>G711(ulaw)</AUDIO>
      </CALLEE>
      <TIME>
        <INIT>2018-08-24 18:20:25</INIT>
        <BEGIN>2018-08-24 18:20:38</BEGIN>
        <END>2018-08-24 18:20:47</END>
        <DURATION>9</DURATION>
      </TIME>
      <RECORD>
        <ROOT>K:\Phone Recorder</ROOT>
        <PATH>K:\Phone Recorder\2018\08\24\20180824181025-0000-P7D53Z.wav</PATH>
        <FILENUM>1</FILENUM>
      </RECORD>
      <DTMF/>
     </CALL>

I tried to manually import the DB from folders to Access, but it creates separate tables for each imported XML file, and takes very long time. How would I automate content import from such multilevel folder structure into MS Access 2016 Calls DB, possibly using an XSLT file if required? The goals is to view a flat calls list, sorted by year / month / date / time, with separators for each year and month. Each record should show parties phone numbers, date&time in/out, call duration, and a linked WAV file, clicking on it should open a default audio player to listen to the recording. I couldn't find any similar question or solution here on the web. Where would I start?

 

[June7]

I did a test import with the wizard with your sample and resulted in 5 new tables. Subsequent imports can be imported to these existing tables. Why would you get new tables with each import? What's annoying is that the tables have no linking keys. The XML is not structured for relational database.

Can use VBA to open the XML as a text file object and process one line at a time. The following code will read one line at a time and print to the immediate window.

Dim sText As String
Open "path\filename.xml" For Input As #1
Do While Not EOF(1)
    Line Input #1, sText
    Debug.Print LTrim(sText)
Loop
Close #1

The code to identify the lines with data and then parse it from the string won't be simple. Here is one approach:

Sub ImportXML()
Dim sText As String, sString As String, x As Integer
Open "path\Calls.xml" For Input As #1
For x = 1 To 28
    Line Input #1, sText
    Select Case x
        Case 3, 4, 5, 7, 8, 9, 12, 13, 14, 17, 18, 19, 20, 23, 24, 25
            sText = Mid(sText, InStr(sText, ">") + 1, Len(Left(sText, InStr(sText, "</"))) - InStr(sText, ">") - 1)
    End Select
    Select Case x
        Case 3, 4, 7, 8, 9, 12, 13, 14, 23, 24
            sString = sString & "'" & sText & "',"
        Case 5, 20, 25
            sString = sString & sText & ","
        Case 17, 18, 19
            sString = sString & "#" & sText & "#,"
    End Select
Next
sString = Left(sString, Len(sString) - 1)
CurrentDb.Execute "INSERT INTO Calls(CallID,SIP_ID,Succeed,CallerIP,CallerName,CallerAudio," & _
                    "CalleeIP,CalleeName,CalleeAudio,INIT,Begin,End,Duration,WAVRoot,WAVPath,WAVFileNum)" & _
                    "VALUES(" & sString & ")"

Close #1
End Sub

That imports one file from one known folder location. If you want to automate pulling in multiple files from multiple folders, that gets more complicated. If you also need to break up the Caller and Callee <NAME> data to separate pieces, another level of complication.

 

[sam28bul]

I did a test import with the wizard with your sample and resulted in 5 new tables. Subsequent imports can be imported to these existing tables.

Thanks for the simplified sample code. Yes, it seems possible to manually import subsequent XMLs to the same tables.

the tables have no linking keys. The XML is not structured for relational database.

I wonder if adding a basic XSLT code for XML file import transform would be a simpler approach to accomplish the entire task? It can be called from a VBA code iterating through folders structure. How would such XSLT code look like?

 

[June7]

I don't know what you mean by 'XSLT' code. Perhaps should be XLST but I still don't what you mean.

 

[sam28bul]

XSL is a language for transforming XML. I'm not allowed to post links for now to show some Stack Overflow site examples for XML file transforms when importing them into Access.

 

[June7]

Now 'XSL transformation' does sound vaguely familiar. Maybe about 5 years ago I encountered this.

However, I expect code to iterate folders and grab files will be the same regardless of the import method. Why don't you get that part figured out. Already have some code to do import. Can switch to XSLT later.

 

[Gasman]

I believe XLST is a template that defines the structure of the xml file.?

 

[sam28bul]

Can switch to XSLT later.

Of course its an option. But I need to show fields in tables and queries in a certain way. For example, links to audio files in the PATH field must be hyperlinks with title truncated to a file name only. I can edit it after importing, but it looks like XSLT provides a way to import data into a certain format like this. I just want to learn how to do it right.

I believe XLST is a template that defines the structure of the xml file.?

Yes. How would such template look like? Also the folder iteration code? Also I need to run this script periodically to amend and existing DB with new records, while ignoring already existing in the DB records.


These are some examples I found: 1 , 2 , 3 , 4 .

 

[June7]

Please attempt building iteration code that suits your file structure. There are many examples. Here is one http://allenbrowne.com/ser-59.html

And since you have examples of XSLT code why don't you attempt that as well.

 

[sam28bul]

Thanks for the link. I'm just trying to learn XSLT, so its not easy to attempt. With iteration code, I need to check if a found record already exists in the DB before importing it, since the folder structure is amended with new records daily, and the import code needs to run periodically.

 

[June7]

Then adapt code to include a check against records for existing filename. A simple DLookup can accomplish that.

Change the sub to have file path\name as argument.

My import code can be adjusted to format link string as hyperlink. String manipulation functions can extract filename. Line 24 would have its own Case:

Case 24
     sString = sString & "'" & <extract filename from sText or file path\name passed by argument>  & "#" & sText & "#',"

And if another field is needed for filename, that can be accommodated as well

 

[Cronk]

Use the XMLT file if you have it. If not, I've used the Microsoft XML parser by setting a reference to MS XML6. Set a reference to the XML file being read

 Set xDoc = New MSXML2.DOMDocument
   xDoc.validateOnParse = False
   If xDoc.Load(strXMLFileName) = False Then  'Check if valid XML format
      booFailLoad = True
      Exit Sub
   Else
      booFailLoad = False
   End If


  varData= TagValue("ReceiptNumber", xDoc)

Code for the function TagValue above

Function TagValue(TagName As String, xDoc As MSXML2.DOMDocument) As Variant
   Dim var As Variant
   Dim nNode As MSXML2.IXMLDOMNode
   
   On Error GoTo TagValue_Err
   
   Set nNode = xDoc.getElementsByTagName(TagName).Item(0)
   If IsEmpty(nNode.childNodes(0).Text) Then
      TagValue = Null
   Else
      TagValue = nNode.childNodes(0).Text
   End If
   Exit Function
   
TagValue_Err:
   If Err = 91 Then  'No data
      TagValue = Null
   End If
   
End Function

 

[sam28bul]

Thank you guys. Will try your suggestions.