Results 1 to 6 of 6
  1. #1
    GeorgeR is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2022
    Posts
    2

    Post Import multiple XML files with different structure into Access DB (only some columns)

    I have to import several XML files, but they have different structures and I would like to import only certain columns from the XML files.


    I use this code to import files with the same structure
    Code:
    Private Sub Command5_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 = Me![Path]
        strPath = "D:\XML\"
        strFile = Dir(strPath & "*.XML")
        strFile = Dir(strPath & A)
        
        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 
    How can I modify the code to import the common columns from all XML files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    did you try: docmd.Transferspreadsheet

  3. #3
    GeorgeR is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2022
    Posts
    2
    haw can i implement this command in code?

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    That IS the code.

    but it might be better to LINK the excel file as an external table, from the same file everytime , say: c:\temp\File1.xlsx
    then make a query to import only certain fields / conditions.

    then all you do is save the new file to: c:\temp\File1.xlsx (already linked)
    run the import query
    done.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How does Transferspreadsheet apply to import of XML file?

    ImportXML does not allow to link nor to pick columns, it's import all or nothing to table. Options I see:

    1. Import to temporary table, run INSERT SELECT action SQL to pull desired fields to permanent table.

    2. Manipulate XML as a text file - open text file, loop lines, parse string to array of data, loop array and commit elements you want to table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Edgar is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    271
    You did not post what the structure looks like, post some xml files so I can give you some use cases.
    For now, I can only advice querying the xml files like you would query an html file, requires a reference to Microsoft XML 6.0 but can be converted to a late bound version by simply changing the msxml references to objects

    I had these xml files at hand that have similar structures. All these files shared columns "invoice:Sender" and "invoice:Receiver" and had an attribute "Name", so this is what I used to query them all.
    Code:
    Sub LoopOverTags()
      Dim fileName As String
      Dim filePath As String
      Dim tagNames As Variant
      Dim i As Long
      Dim attributeValue As String
    
      ' Set the directory path
      filePath = CurrentProject.Path
    
      ' Set the initial file name
      fileName = Dir(filePath & "\*.xml")
    
      ' Set the array of tag names
      tagNames = Array("invoice:Sender", "invoice:Receiver")
    
      ' Loop through all the XML files in the directory
      Do While fileName <> ""
        ' Loop through all the tag names
        For i = LBound(tagNames) To UBound(tagNames)
          ' Get the attribute value
          attributeValue = GetAttributeValue(filePath & "" & fileName, CStr(tagNames(i)), "Name")
    
          ' Do something with the attribute value
          Debug.Print attributeValue
        Next i
    
        ' Get the next XML file name
        fileName = Dir
      Loop
    End Sub
    Code:
    Private Function GetAttributeValue(filePath As String, tagName As String, attributeName As String) As String
      Dim xmlDoc As New MSXML2.DOMDocument60
      Dim tagNodeList As MSXML2.IXMLDOMNodeList
      Dim tagNode As MSXML2.IXMLDOMNode
      Dim attributeValue As String
    
      ' Load the XML document
      xmlDoc.Load filePath
    
      ' Select the element with the specified tag name
      Set tagNodeList = xmlDoc.getElementsByTagName(tagName)
      Set tagNode = tagNodeList.Item(0)
    
      ' Retrieve the value of the attribute with the specified name
      attributeValue = tagNode.Attributes.getNamedItem(attributeName).Text
    
      ' Return the value
      GetAttributeValue = attributeValue
    End Function
    The rest of the code necessary for your use case is basically database code to form an insert/update statement inside the loop.

    The code above expects you to have a list of xml files in the current project path, which can be changed. To use it, open your xml, check its structure, find the tags you're interested in and their attribute and input in the function. As I mentioned, in order to send the data to tables, you need to form the statements, the rest of the work is done.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import multiple Excel Files into 1 access table
    By jurbin in forum Import/Export Data
    Replies: 1
    Last Post: 05-15-2015, 01:45 PM
  2. Import Multiple Excel Files with SAME layout to Access
    By torontoraptor in forum Programming
    Replies: 2
    Last Post: 05-12-2014, 10:29 AM
  3. Replies: 1
    Last Post: 03-25-2014, 08:54 PM
  4. Replies: 3
    Last Post: 08-29-2011, 03:11 PM
  5. Replies: 4
    Last Post: 07-15-2010, 05:58 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums