Results 1 to 11 of 11
  1. #1
    JVH is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    5

    Importing XML with XSL or XSLT

    Hi All,
    I love my Access 2016 but working with XML makes my head hurt.
    I have two XML files downloaded from a vendor.
    I need to import them into Access so I can manipulate them.
    Here is a sample of the first one.

    <Root xmlns:sql='urn:schemas-microsoft-com:xml-sql'><Truserv>
    <Item rdc_nbr="01" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="02" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="03" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="04" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="07" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="08" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="09" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="10" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="12" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="13" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="26" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="01" item_nbr="100024" Inventory="0"/>
    <Item rdc_nbr="02" item_nbr="100024" Inventory="0"/>
    <Item rdc_nbr="03" item_nbr="100024" Inventory="0"/>
    <Item rdc_nbr="04" item_nbr="100024" Inventory="10"/>
    <Item rdc_nbr="07" item_nbr="100024" Inventory="0"/>
    <Item rdc_nbr="08" item_nbr="100024" Inventory="17"/>


    </Truserv></Root>


    If I try to import it I get a table with the field "Item" only and no data.
    I suspect I need to create an XSLT transform file.
    But I am having trouble understanding how to build one.
    I was under the impression that XML was suppose to be record based but this does not have a record identifier.

    Any help would be appreciated.

    John

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may find this youtube video helpful. I haven't done this for a few years, but did find this video (he has others on xml also).
    Good luck.

  3. #3
    JVH is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    5
    Thank you.
    I had already watched that video and it is helpful at explaining the import process.
    However that file has an XSD file which contains the structure of the XML file and there is a line in the XML file that links it to the XSD file.
    My file has no structure file with it.
    And since we need to import a new copy daily, I need to automate the process.
    It looks like you can have a transform template XSLT that will correct the file during import and can be added to the import command.

    What I really need is some help figuring out how to build an XSLT file.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not promising anything, but can you post a copy of each of the xml files? I'll look at them, but haven't done this sort of thing for 10+ years.
    If you get these from a website, you may be able to access/copy directly.
    Do you have a table design in Access that is intended to accept this xml data?
    Any other info on the set up would be helpful.
    Good luck.

  5. #5
    JVH is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    5
    Hi and thanks.
    Lets just start with the first xml file as it only has 3 fields.
    I copied a portion of the file in my first message. It is 120,000 lines so I just put a little.
    You can copy and paste it into notepad and save it as inventory.xml.
    Try and import it into access (I have 2016) and see what you get.
    It should be a table called Truserv with no data in it.

    Thanks
    John

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    if the whole file is as consistant as your example data it may be easier to just parse it out.

    Code:
        Dim v1, v2, v3
        Dim strSql As String
        Dim strFilename As String
        Dim strTextLine As String
        Dim iFile As Integer
    
    
        iFile = FreeFile
    
    
        strFilename = Me.FileN    'substitute the path to file
    
    
        Open strFilename For Input As #iFile
    
    
        Do Until EOF(1)
        
            Line Input #1, strTextLine
            
            If InStr(1, strTextLine, "Item rdc_nbr=") Then
            
                v1 = Mid(strTextLine, 15, 4)
                v2 = Mid(strTextLine, 29, 8)
                v3 = Replace(Mid(strTextLine, 48, InStrRev(strTextLine, """")), "/>", "")
    
    
                strSql = "Insert into YourTable(Field1,Field2,Field3) values(" & v1 & "," & v2 & "," & v3 & ")"  ' insert your table and field names
                
               ' Currentdb.Execute strSql,dbFailOnError
    
                Debug.Print strSql
    
            End If
        Loop
        
        Close #iFile
    Result:
    Code:
    Insert into YourTable(Field1,Field2,Field3) values("01","100008","0")
    Insert into YourTable(Field1,Field2,Field3) values("02","100008","0")
    Insert into YourTable(Field1,Field2,Field3) values("03","100008","0")
    Insert into YourTable(Field1,Field2,Field3) values("04","100008","0")
    Insert into YourTable(Field1,Field2,Field3) values("07","100008","0")
    Insert into YourTable(Field1,Field2,Field3) values("08","100008","0")
    Insert into YourTable(Field1,Field2,Field3) values("09","100008","0")
    Insert into YourTable(Field1,Field2,Field3) values("10","100008","0")
    Insert into YourTable(Field1,Field2,Field3) values("12","100008","0")
    Insert into YourTable(Field1,Field2,Field3) values("13","100008","0")
    Insert into YourTable(Field1,Field2,Field3) values("26","100008","0")
    Insert into YourTable(Field1,Field2,Field3) values("01","100024","0")
    Insert into YourTable(Field1,Field2,Field3) values("02","100024","0")
    Insert into YourTable(Field1,Field2,Field3) values("03","100024","0")
    Insert into YourTable(Field1,Field2,Field3) values("04","100024","10")
    Insert into YourTable(Field1,Field2,Field3) values("07","100024","0")
    Insert into YourTable(Field1,Field2,Field3) values("08","100024","17")

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Moke's suggestion and example. Seems straightforward and doesn't involve XLST/XSL.
    Are the field values all text? If inventory represents a quantity then it's probably a number.
    Nice example moke.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    JVH,

    Have you resolved this? What is status? Next step?

    Here is an approach using XLST.

    Your xml file --- I named it inventory.xml
    Code:
    <?xml version="1.0" encoding="UTF-8"?> 
    <Root xmlns:sql='urn:schemas-microsoft-com:xml-sql'><truserv>
            <Item rdc_nbr="01" item_nbr="100008" Inventory="0"/>
            <Item rdc_nbr="02" item_nbr="100008" Inventory="0"/>
            <Item rdc_nbr="03" item_nbr="100008" Inventory="0"/>
            <Item rdc_nbr="04" item_nbr="100008" Inventory="0"/>
            <Item rdc_nbr="07" item_nbr="100008" Inventory="0"/>
            <Item rdc_nbr="08" item_nbr="100008" Inventory="0"/>
            <Item rdc_nbr="09" item_nbr="100008" Inventory="0"/>
            <Item rdc_nbr="10" item_nbr="100008" Inventory="0"/>
            <Item rdc_nbr="12" item_nbr="100008" Inventory="0"/>
            <Item rdc_nbr="13" item_nbr="100008" Inventory="0"/>
            <Item rdc_nbr="26" item_nbr="100008" Inventory="0"/>
            <Item rdc_nbr="01" item_nbr="100024" Inventory="0"/>
            <Item rdc_nbr="02" item_nbr="100024" Inventory="0"/>
            <Item rdc_nbr="03" item_nbr="100024" Inventory="0"/>
            <Item rdc_nbr="04" item_nbr="100024" Inventory="10"/>
            <Item rdc_nbr="07" item_nbr="100024" Inventory="0"/>
            <Item rdc_nbr="08" item_nbr="100024" Inventory="17"/>
        </truserv>    
            </Root>
    Here is the xsl ( from reviewing/google and trial and error) - I named it inventory.xsl

    Code:
    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
         <xsl:output method="text"/>
          <xsl:variable name="quote">&quot;</xsl:variable>
          <!-- This stylesheet is used to 
          transform  inventory.xml to inventoryTruserv.csv which is then
          imported into your database as table Truserv  -->
          
      <xsl:template match="/Root/truserv">
          <xsl:text>"rdc_nbr","item_nbr","Inventory"</xsl:text><xsl:text>
    </xsl:text>
          <xsl:apply-templates select="Item"/>
      </xsl:template> 
      <xsl:template match="/Root/truserv/Item">
          <xsl:value-of select="concat($quote, @rdc_nbr, $quote, ',',
                                     $quote, @item_nbr, $quote, ',', 
                                     $quote, @Inventory, $quote)"/>
        <xsl:if test="position()!=last()"><xsl:text>
    </xsl:text></xsl:if>
      </xsl:template>
         
    </xsl:stylesheet>
    And here is the vba procedure to do the transformation and import.

    Code:
    ' ----------------------------------------------------------------
    ' Procedure Name: ProcessXML
    ' Purpose:To import an xml file into Access using XLST
    ' - xml file  inventory.xml
    ' -an xsl stylesheet    inventory.xsl
    ' -save as a csv   inventorytruserv.csv  (intermediate step)
    ' -use tranferText to import the csv into database as table  Truserv
    ' Procedure Kind: Sub
    ' Procedure Access: Public
    ' Author: Jack
    ' Date: 18-Sep-20
    '
    '****NOTE:  This requires a reference to Micrsoft XML, v6.0
    '
    'Helpful links:
    ' http://www.functionx.com/vbaccess/Lesson61.htm
    ' http://www.xmlpitstop.com/
    ' ----------------------------------------------------------------
    Sub ProcessXML()
    10        On Error GoTo ProcessXML_Error
          Dim domIn As New DOMDocument60, domStylesheet As New DOMDocument60
          Dim xFile As String, StyleSheet As String, NewStock As String, xmlText As String
          Dim fso As Object, oFile As Object
    
          'sample I worked with these values during testing
          '10    StyleSheet = "C:\users\jack\documents\storeitems.xsl" '"path to XSL"
          '20    xFile = "C:\users\jack\documents\storeitems.xml"      '"path to xml"
          '30    NewStock = "C:\users\jack\documents\storeitems.csv"   '"path to intended csv"
    
          'real test with inventory xml from forum
          ' https://www.accessforums.net/showthread.php?t=81758
    
    20    StyleSheet = "C:\users\jack\documents\inventory.xsl" '"path to XSL"
    30    xFile = "C:\users\jack\documents\inventory.xml"      '"path to xml"
    40    NewStock = "C:\users\jack\documents\inventoryTruserv.csv"   '"path to intended csv"
    
          'LOAD INPUT XML
    50    domIn.async = False
    60    If domIn.Load(xFile) Then
    70     Debug.Print "using Stylesheet: " & StyleSheet
          'LOAD STYLESHEET
    80        domStylesheet.Load StyleSheet
    
          'APPLY TRANSFORMATION, SAVE TO TEXT
    90        If Not domStylesheet Is Nothing Then
    100          xmlText = domIn.transformNode(domStylesheet)
    
                 ' SAVE CSV
    110          Set fso = CreateObject("Scripting.FileSystemObject")
    120          Set oFile = fso.CreateTextFile(NewStock)
    130              oFile.WriteLine xmlText
    140          oFile.Close
    
                 'IMPORT CSV INTO MS ACCESS
    150          DoCmd.TransferText acImportDelim, , "Truserv", NewStock, True, , 65001
    160       End If
    170   End If
    
          'CLEAN UP
    180   Set domIn = Nothing: Set domStylesheet = Nothing
    190   Set oFile = Nothing: Set fso = Nothing
              
    200       On Error GoTo 0
    ProcessXML_Exit:
    210       Exit Sub
    
    ProcessXML_Error:
    220       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ProcessXML, line " & Erl & "."
    230       GoTo ProcessXML_Exit
    End Sub
    In the attached zip file is an accdb and the xml, xsl and csv files used.

    The procedure could be turned into a function if your requirement is for a consistent import.
    Good luck.
    Attached Files Attached Files
    Last edited by orange; 09-19-2020 at 01:46 PM. Reason: update with an XLST example and database

  9. #9
    JVH is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    5
    Hi,
    I have been out of town over the weekend.
    Both methods look very workable.
    I will download the work with them today.

    Thanks,
    John

  10. #10
    JVH is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    5
    Hi,
    I ended up using the code to parse the file.

    Code:
    Function ImportInventoryXML()
    
    
    iFile = FreeFile
        strFilename = "C:\DATA\INVENTORY.XML"    'substitute the path to file
    
    
       Open strFilename For Input As #iFile
       Line Input #1, strTextLine                'handle screwy first line
          If InStr(1, strTextLine, "Item rdc_nbr=") Then
                v1 = Mid(strTextLine, 77, 2)
                v2 = Mid(strTextLine, 91, 6)
                v3 = Replace(Mid(strTextLine, 110, InStrRev(strTextLine, """")), """/>", "")
                If v1 = "01" Or v1 = "41" Or v1 = "14" Or v1 = "04" Or v1 = "27" Then
                   strSql = "INSERT INTO tblINVENTORY(RDC, SKU, QTY) VALUES (" & v1 & "," & v2 & "," & v3 & ")"  ' insert your table and field names
                   CurrentDb.Execute strSql, dbFailOnError
                   'Debug.Print strSql
                End If
               
          End If
    
    
        Do Until EOF(1)
            Line Input #1, strTextLine
            If InStr(1, strTextLine, "Item rdc_nbr=") Then
                v1 = Mid(strTextLine, 16, 2)
                v2 = Mid(strTextLine, 30, 6)
                v3 = Replace(Mid(strTextLine, 49, InStrRev(strTextLine, """")), """/>", "")
                If v1 = "01" Or v1 = "41" Or v1 = "14" Or v1 = "04" Or v1 = "27" Then
                   strSql = "INSERT INTO tblINVENTORY(RDC, SKU, QTY) VALUES (" & v1 & "," & v2 & "," & v3 & ")"  ' insert your table and field names
                   CurrentDb.Execute strSql, dbFailOnError
                   'Debug.Print strSql
                End If
                
            End If
        Loop
    Close #iFile
    End Function
    I added some filters to only grab the locations we wanted.
    Also the actual file is as below so I had to add code to handle the first line.
    So far it is working good.


    Code:
    <Root xmlns:sql='urn:schemas-microsoft-com:xml-sql'><Truserv><Item rdc_nbr="01" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="02" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="03" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="04" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="07" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="08" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="09" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="10" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="12" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="13" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="26" item_nbr="100008" Inventory="0"/>
    <Item rdc_nbr="01" item_nbr="100024" Inventory="8"/>
    <Item rdc_nbr="02" item_nbr="100024" Inventory="0"/>
    <Item rdc_nbr="03" item_nbr="100024" Inventory="0"/>
    Handling the catalog file will be a lot more work to setup.

    Code:
    <Root xmlns="sql='urn:schemas-microsoft-com:xml-sql'"><Truserv>
    <Item item_nbr="100081" srp_cost="0.01" member_cost="6.95" ds_cost="0.00" short_description="25PK Rain Check" vendor_id="35543" dpt_code="Z " class_code="3" subclass_code="6" vendor_name="CENTURION INC" upc="701844123534" long_description="25 Pack, 3-Part Rain Check, Back Ply To Be Used To Mail To Customer." weight="0.02" length="11.80" width="8.90" height="5.30" pack_weight="30.30" pack_length="11.80" pack_width="8.90" pack_height="5.30" retail_pack_qty="1" member_pack_qty="1" member_pack_type="" member_break_pack="Y" model="100081" item_picture_id="100081" country_code="US" to_be_discontinued="N" substitute_item_nbr="" retail_uom="EA" Edit_Divisor="25" Exclusive_Brand_Code=" " Prop65Flag="N" Prop65WarningText="" MAPP="N" IMAPP="N" OnlineRestriction="N" MAPPPrice="" IMAPPPrice="" />
    <Item item_nbr="100164" srp_cost="0.89" member_cost="0.25" ds_cost="0.29" short_description="2&quot; BLK Refl Letter L" vendor_id="49601" dpt_code="H " class_code="23" subclass_code="7" vendor_name="HY-KO PROD CO" upc="84100004243" long_description="2&quot;, Black Adhesive Reflective Vinyl Letter L, Silver Background, Weather Resistant, For Commercial Or Residential Use." weight="0.02" length="2.00" width="1.50" height="0.02" pack_weight="0.02" pack_length="2.00" pack_width="1.50" pack_height="0.12" retail_pack_qty="1" member_pack_qty="10" member_pack_type="F" member_break_pack="N" model="RV-25/L" item_picture_id="100164" country_code="US" to_be_discontinued="N" substitute_item_nbr="" retail_uom="EA" Edit_Divisor="1" Exclusive_Brand_Code=" " Prop65Flag="Y" Prop65WarningText="Cancer and Reproductive Harm-www.P65Warnings.ca.gov" MAPP="N" IMAPP="N" OnlineRestriction="N" MAPPPrice="" IMAPPPrice="" />
    <Item item_nbr="100917" srp_cost="43.99" member_cost="25.81" ds_cost="0.00" short_description="Sportsman's Axe" vendor_id="30080" dpt_code="L " class_code="14" subclass_code="30" vendor_name="ESTWING MFG CO" upc="34139612414" long_description="Sportsman's Axe With Leather Grip, Fully Polished, Vinyl Sheath Included." weight="1.56" length="13.00" width="6.10" height="1.70" pack_weight="3.83" pack_length="13.87" pack_width="6.18" pack_height="1.81" retail_pack_qty="1" member_pack_qty="2" member_pack_type="B" member_break_pack="Y" model="E24A" item_picture_id="100917" country_code="US" to_be_discontinued="N" substitute_item_nbr="" retail_uom="EA" Edit_Divisor="1" Exclusive_Brand_Code=" " Prop65Flag="N" Prop65WarningText="" MAPP="N" IMAPP="N" OnlineRestriction="N" MAPPPrice="" IMAPPPrice="" />
    <Item item_nbr="101535" srp_cost="19.99" member_cost="14.87" ds_cost="13.99" short_description="Prop Safe Gas Gauge" vendor_id="53751" dpt_code="N " class_code="19" subclass_code="33" vendor_name="ENERCO/MR. HEATER" upc="89301763425" long_description="Propane Safety Gas Gauge/Leak Detector. Features Acme tank connection, female POL with Acme thread, and gas gauge for propane level. Easy to install. 1 year limited warranty." weight="1.00" length="6.00" width="3.50" height="5.25" pack_weight="6.00" pack_length="10.50" pack_width="6.00" pack_height="10.50" retail_pack_qty="1" member_pack_qty="6" member_pack_type="B" member_break_pack="Y" model="F276342" item_picture_id="101535" country_code="US" to_be_discontinued="N" substitute_item_nbr="" retail_uom="EA" Edit_Divisor="1" Exclusive_Brand_Code=" " Prop65Flag="Y" Prop65WarningText="This product can expose you to chemicals including lead and lead compounds, which are known to the State of California to cause cancer and birth defects or other reproductive harm. Wash your hands after handling this product. For more information go to www.P65Warnings.ca.gov" MAPP="N" IMAPP="N" OnlineRestriction="N" MAPPPrice="" IMAPPPrice="" />

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Importing
    By RustyRick in forum Import/Export Data
    Replies: 2
    Last Post: 04-04-2020, 03:21 PM
  2. XSLT not working in Access 2013
    By exharris in forum Import/Export Data
    Replies: 2
    Last Post: 07-10-2017, 06:41 AM
  3. Importing CSV
    By mike02 in forum Access
    Replies: 5
    Last Post: 08-01-2012, 05:21 PM
  4. Importing XML with XSLT to multiple tables without unique key
    By ObstructedBelt in forum Import/Export Data
    Replies: 3
    Last Post: 06-27-2012, 01:21 PM
  5. Need help importing to SQL
    By winterh in forum SQL Server
    Replies: 14
    Last Post: 03-28-2012, 12:36 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