Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151

    Question What is the correct way/approach to import a section of an XML file


    Hi all. Seems like I only get around to this stuff on a Friday.

    I've had assistance from this forum previously in relation to importing an XML file in to my DB. Whilst what I have now works well, the issue is that the bulk of the information being imported from the XML file is not required, so gets deleted (automatically) after the import and I am left with what I want. The issue here is that this process is seriously blowing out the DB. Once the project is completed and rolled out to the multiple users I can foresee that the DB will rapidly become enormous.

    e.g The DB is initially 0.55MB, however after importing the data from just 5 XML files, creating 5 rows of data in the destination table, the DB is now 1.86MB in size. Running Compact & Repair returns the DB to .55MB (yes the data is there, there just isn't a lot of it - mostly numbers.)

    The data from the XML file ultimately ends up in 1 table with 28 fields. This table will then be linked to a a couple of other tables in the project.

    I toyed with the idea of running Compact & Repair after each import from the XML file, but this is apparently not possible through VBA and can be problematic if there are multiple users logged in to the DB at the time. Manually running Compact & Repair works, but most of the end users are beyond this sort of activity and I'd prefer if they didn't have access to the Access menus.

    I feel like the Application.ImportXML function is the root of the issue, because it imports the whole of the XML file, creating 20 tables, when I only want to import a couple of sections of the XML file.

    This is the SQL for the query that imports the information in to the destination table.

    Code:
    INSERT INTO XMLMachineSettingsImportTemp ( MachineName, A2MCNo, MachineNo, FeedRateMAX, PlungeRateMAX, TravelRateMAX, TravelRate, PlungeRate, FeedRate, JogSpeedMode, SeekXYSpeed, SeekZSpeed, JerkGrate, AccelerationG, AccelMAX, JerkMAX, CentripetalG, BrakeG, ArcError, MinLength, G56x, G56y, G56z )
    SELECT MachineInformation.MachineName, Model.Code, Left([MachineName],5)+Right([MachineName],4) AS Expr1, MotionParameters.FeedRateMAX, MotionParameters.PlungeRateMAX, MotionParameters.TravelRateMAX, MotionParameters.TravelRate, MotionParameters.PlungeRate, MotionParameters.FeedRate, MotionParameters.JogSpeedMode, MotionParameters.SeekXYSpeed, MotionParameters.SeekZSpeed, MotionParameters.JerkGrate, MotionParameters.AccelerationG, MotionParameters.AccelMAX, MotionParameters.JerkMAX, MotionParameters.CentripetalG, MotionParameters.BrakeG, MotionParameters.ArcError, MotionParameters.MinLength, Origin.x, Origin.y, Origin.z
    FROM MachineInformation, MotionParameters, Model, Origin;
    I guess my question is, is there a way of just importing sections of the XML file (based on their section names), or parsing the XML file to pick out the data I want?

    Should I be taking another approach?

    BTW, i have no control over how the XML file is created, they will be emailed from sources all around the world.

    Any suggestions greatly appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You use wizard to import into MachineInformation then selectively insert data into XMLMachineSettingsImportTemp?

    If the External Data > XML import wizard does not do what you want, this will probably get quite complicated.

    An XML file is just a text file. Can read text file line by line to parse. Review https://www.thespreadsheetguru.com/b...ide-text-files and https://www.excel-easy.com/vba/examp...text-file.html

    An XSLT (T = Transform) file is used to restructure XML into an arrangement that can be imported into relational database. I am not sure if this technique would be helpful for your situation if your data is already imported in normalized structure. For a start, review https://stackoverflow.com/questions/...transform-file

    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.

  3. #3
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi June7. Thanks for the reply. I'll have a look at your suggestions. I hadn't considered treating the file as a text document.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.

  5. #5
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi June7

    Thanks again for your assistance. I've checked out your referrals (had found a couple similar prior to making my initial post) and have something that works, sort of..

    My reservations are as follows;


    1. The assumption in the expression Mid(text, MachineName + 13, 11) is that the the value of the element you want is always the same length. In the case of numerical values, this won't always be true. Converting the XML file to one long string (in this example it is 10,314 characters in length) and searching for the start tag of the element I want, without checking for the end tag seems a bit dangerous. Maybe this can be modified to look for the end tag as well.
    2. I'm going to end up collecting 30 odd pieces of data from various sections of the XML file and writing to the table in the DB, and this approach seems a bit long-winded, not very efficient and poorly coded - maybe I'm wrong here, it certainly runs fast enough.


    I'd be interested in your comments.


    Code:
    Private Sub Form_Open(Cancel As Integer)
    Dim XMLFile As String, text As String, textline As String
    Dim MachineName As String, A2MCNumber As String
    
    XMLFile = "c:\XMLMachineAdjustableParams.xml"
    Open XMLFile For Input As #1
    Do Until EOF(1)
        Line Input #1, textline
        text = text & textline
    Loop
    Close #1
    MachineName = InStr(text, "<MachineName>")
    A2MCNumber = InStr(text, "<Code>")
    
    
    ' manipulate the result for MachineName because the returned value isn't in our current format (eg. returned value is 1234-MM1234 but I need 1234-1234 - so strip out the MM)
    MachineName = Mid(text, MachineName + 13, 11)
    MachineName = Left([MachineName], 5) + Right([MachineName], 4)
    
    A2MCNumber = Mid(text, A2MCNumber + 6, 14)
    
    CurrentDb.Execute "INSERT INTO [XMLMachineSettings]([MachineNo], [A2MCNo], [ImportDate])" & "VALUES ('" & MachineName & "', '" & A2MCNumber & "', '" & Now() & "');"
    
    End Sub

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Really can't advise specifics without better understanding of data structure. Yes, could search for end tag.
    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.

  7. #7
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hello Again

    I've spent a fair bit of time on this project but not really getting anywhere.

    I have made some good progress on parts of it, and am now just stuck on one problem.

    The XML file I need to work with has a section it it like this. (the entire XML file is much bigger)

    <OriginList>
    <Origin>
    <x>0</x>
    <y>0</y>
    <z>0</z>
    </Origin>
    <Origin>
    <x>17.65</x>
    <y>8.283</y>
    <z>0</z>
    </Origin>
    <Origin>
    <x>66.146</x>
    <y>7.598</y>
    <z>0</z>
    </Origin>
    <Origin>
    <x>0</x>
    <y>0</y>
    <z>0</z>
    </Origin>
    <Origin>
    <x>0</x>
    <y>0</y>
    <z>0</z>
    </Origin>
    <Origin>
    <x>0</x>
    <y>0</y>
    <z>0</z>
    </Origin>
    </OriginList>

    What I need to do is extract the x, y and z values for each origin so that I end up with a result like...

    posn1 x = 0
    posn1 y = 0
    posn1 z = 0

    posn2 x = 17.65
    posn2 y = 8.23
    posn2 z = 0

    posn3 x = 66.146
    posn3 y = 7.598
    posn3 z = 0

    posn4 x = 0
    posn4 y = 0
    posn4 z = 0

    posn5 x = 0
    posn5 y = 0
    posn5 z = 0

    posn6 x = 0
    posn6 y = 0
    posn6 z = 0

    I feel like I need to use some sort of loop to extract the results and assign them to variables, but don't know how to go about it. I've been hacking away at this for some time now, but am not making any headway. Any suggestions would be greatly appreciated.

    Attached is the XML file for reference.


    XMLMachineAdjustableParams.zip

  8. #8
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi sheusz!

    Give a try to this function, at first in your immediate window:
    Code:
    Function XMLValues(ByVal strURL As String, ByVal strNodeTag As String) As String
        'Need a reference to Microsoft XML, v#.0 for early binding
        Dim http As MSXML2.XMLHTTP
        Dim xml As MSXML2.DOMDocument
        Dim nodes As MSXML2.IXMLDOMNodeList
        Dim node As MSXML2.IXMLDOMNode
        Dim item As MSXML2.IXMLDOMNode
        Dim strR As String
        Dim strV As String
    
        On Error GoTo ErrH
        Set http = New MSXML2.XMLHTTP
        http.Open "GET", strURL, False
        http.send
        strR = http.responseText
    
        If (http.readyState = 4) And (Len(strR) > 0) Then
            Set xml = New MSXML2.DOMDocument
            xml.async = False
            xml.validateOnParse = False
            xml.loadXML strR
    
            Set nodes = xml.documentElement.getElementsByTagName(strNodeTag)(0).childNodes
            If nodes.length = 1 Then
                With nodes(0)
                    strR = vbCrLf & .parentNode.nodeName & ":" & .nodeValue
                End With
            Else
                strR = ""
                For Each node In nodes
                    strV = ""
                    If node.childNodes.length = 1 Then
                        strR = strR & vbCrLf & node.nodeName & ":" & node.childNodes(0).nodeValue
                    Else
                        For Each item In node.childNodes
                            strV = strV & ";" & item.nodeName & ":" & item.Text
                        Next item
                        strR = strR & vbCrLf & node.nodeName & " " & Mid(strV, 2)
                    End If
                Next node
    
            End If
        End If
    ExitHere:
        On Error Resume Next
        http.abort
        Set http = Nothing
        Set xml = Nothing
        Set nodes = Nothing
        XMLValues = Mid(strR, 3)
        Exit Function
    ErrH:
        MsgBox Err.Description, vbExclamation, "XMLValues Error(#" & Err & ")"
        Err.Clear
        Resume ExitHere
    End Function
    For example, with the line bellow:
    Code:
    ?XMLValues(CurrentProject.Path & "\XMLMachineAdjustableParams.xml","ToolingList")
    you will get this:
    Code:
    Tool Description:12.7mm Comp;Thickness:0;ToolNumber:1;ATCIndex:1;FeedRate:944;QualifiedHeight:-2.0632499116756251;FiringOutput:1;Position:1;SpindleSpeed:18000;ToolType:Router Bit;UserParam1:0;UserParam2:0;UserParam3:0;UserParam4:0;UserParam5:0;UserParam6:0;UserParam7:0;UserParam8:0;UserParam9:0;UserParam10:0;UserParam11:0
    Tool Description:5mm Drill;Thickness:0;ToolNumber:2;ATCIndex:2;FeedRate:944;QualifiedHeight:-2.31039714405256;FiringOutput:1;Position:1;SpindleSpeed:18000;ToolType:Router Bit;UserParam1:0;UserParam2:0;UserParam3:0;UserParam4:0;UserParam5:0;UserParam6:0;UserParam7:0;UserParam8:0;UserParam9:0;UserParam10:0;UserParam11:0
    Tool Description:8mm Drill;Thickness:0;ToolNumber:3;ATCIndex:3;FeedRate:944;QualifiedHeight:-1.951879310223182;FiringOutput:1;Position:1;SpindleSpeed:18000;ToolType:Router Bit;UserParam1:0;UserParam2:0;UserParam3:0;UserParam4:0;UserParam5:0;UserParam6:0;UserParam7:0;UserParam8:0;UserParam9:0;UserParam10:0;UserParam11:0
    Tool Description:V Cutter;Thickness:0;ToolNumber:4;ATCIndex:4;FeedRate:944;QualifiedHeight:-2.6506878756019443;FiringOutput:1;Position:1;SpindleSpeed:18000;ToolType:Router Bit;UserParam1:0;UserParam2:0;UserParam3:0;UserParam4:0;UserParam5:0;UserParam6:0;UserParam7:0;UserParam8:0;UserParam9:0;UserParam10:0;UserParam11:0
    and with the "MachineName" as second argument, you will get this:
    Code:
    MachineName:0913-MM1198
    In your code, you can work with this function as follows:
    Code:
    Sub TestXMLValues()
        Dim strTag As String
        Dim strRet As String
        Dim strSec As String
        Dim strNode As String
        Dim strTab As String
        Dim varNodes As Variant
        Dim varPairs As Variant
        Dim i As Integer, n As Integer
    
        strTag = "ToolingList"
        strRet = XMLValues(CurrentProject.Path & "\XMLMachineAdjustableParams.xml", strTag)
    
        If Len(strRet) Then
            varNodes = Split(strRet, vbCrLf)
            Debug.Print strTag
            For n = LBound(varNodes) To UBound(varNodes)
                strSec = Split(strRet, vbCrLf)(n)
                strNode = Trim(Mid(strSec, 1, InStr(1, strSec, " ")))
                strSec = Trim(Mid(strSec, Len(strNode) + 1))
                varPairs = Split(strSec, ";")
                strTab = "+---"
                If Len(strNode) Then
                    Debug.Print strTab & strNode
                    strTab = vbTab & strTab
                End If
                For i = LBound(varPairs) To UBound(varPairs)
                    Debug.Print strTab & Split(varPairs(i), ":")(0) & "=" & Split(varPairs(i), ":")(1)
                Next i
            Next n
        End If
    End Sub
    Of course, the output notation of the function is just an example.
    You can change the returned value as you like.

    I don't know if this is the best way but, at least in your case, seems to work well.

    I hope it helps!

    Cheers,
    John

  9. #9
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi John
    Thank you very much. This will take me some time to digest, but looks like it could be what I am looking for. I'll play with it over the next few days and update you all.

  10. #10
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi John

    I'm really liking you suggestion. I've created the function and have made a few changes to output the results I want..

    Code:
    Sub TestXMLValues()
        Dim strTag As String
        Dim strRet As String
        Dim strSec As String
        Dim strNode As String
        Dim strTab As String
        Dim varNodes As Variant
        Dim varPairs As Variant
        Dim i As Integer, n As Integer
    
        'strTag = "ToolingList"
        strTag = "OriginList"
        strRet = XMLValues(CurrentProject.path & "\XMLMachineAdjustableParams.xml", strTag)
    
            varNodes = Split(strRet, vbCrLf)
            ' Debug.Print strTag
            
            ' seed the starting value
            strTab = 53
            For n = LBound(varNodes) To UBound(varNodes)
                strSec = Split(strRet, vbCrLf)(n)
                strNode = Trim(Mid(strSec, 1, InStr(1, strSec, " ")))
                strSec = Trim(Mid(strSec, Len(strNode) + 1))
                varPairs = Split(strSec, ";")
               
                strTab = strTab + 1
                If Len(strNode) Then
                    ' Debug.Print "G" & strTab & " " & strNode
                    ' strTab = vbTab & strTab
                End If
                For i = LBound(varPairs) To UBound(varPairs)
    
                    ' convert values to metric
                    ' Debug.Print "G" & strTab & Split(varPairs(i), ":")(0) & "=" & (Split(varPairs(i), ":")(1)) * 25.4
                    
                    FieldValue = "G" & strTab & Split(varPairs(i), ":")(0)
                    SaveValue = LTrim((Split(varPairs(i), ":")(1)) * 25.4)
                    
                    Debug.Print FieldValue
                    Debug.Print SaveValue
    
                    ' Write value to appropriate field in Table ??
                    CurrentDb.Execute _
                    "INSERT INTO [XMLMachineSettings]" _
                    & "(" & FieldValue & ")" & _
                    " VALUES('" & SaveValue & "');"
    
                Next i
            Next n
        End If
    
    
        
    End Sub
    Gives me this result..
    Code:
    G54x
    0
    G54y
    0
    G54z
    0
    G55x
    448.31
    G55y
    210.3882
    G55z
    0
    G56x
    1680.1084
    G56y
    192.9892
    G56z
    0
    G57x
    0
    G57y
    0
    G57z
    0
    G58x
    0
    G58y
    0
    G58z
    0
    G59x
    0
    G59y
    0
    G59z
    0
    My table (XMLMachineSettings) has record fields (short text) G54x, G54y, G54x ..... G59x, G59y, G59z that I need to populate with the values I now have obtained from the XML file. So each record has to have an entry for each field There are also a stack of other fields that are populated elsewhere

    E.g G55x would be written to the table as 448.31 and G55y would be written to the table as 210.3882 for the same record.

    I'm thinking that this could be done during the looping, something like this. However this creates a new record for each value, instead of placing each value in the same record..

    Code:
    CurrentDb.Execute _
    "INSERT INTO [XMLMachineSettings]" _
    & "(" & FieldValue & ")" & _
    " VALUES('" & SaveValue & "');"
    Tantalizingly close, but can't figure out how to accomplish this.

  11. #11
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi sheusz!

    There are two ways (at least) to do this job:

    SQL style:
    Code:
    Sub TestXMLValuesSQL()
        Dim strTag As String
        Dim strRet As String
        Dim strSec As String
        Dim strNode As String
        Dim strTab As String
        Dim varNodes As Variant
        Dim varPairs As Variant
        Dim i As Integer, n As Integer
        Dim FieldValue As String
        Dim SaveValue As String
        Dim strSQL As String
    
        'strTag = "ToolingList"
        strTag = "OriginList"
        strRet = XMLValues(CurrentProject.Path & "\XMLMachineAdjustableParams.xml", strTag)
    
        varNodes = Split(strRet, vbCrLf)
        ' Debug.Print strTag
    
        ' seed the starting value
        strTab = 53
        For n = LBound(varNodes) To UBound(varNodes)
            strSec = Split(strRet, vbCrLf)(n)
            strNode = Trim(Mid(strSec, 1, InStr(1, strSec, " ")))
            strSec = Trim(Mid(strSec, Len(strNode) + 1))
            varPairs = Split(strSec, ";")
    
            strTab = strTab + 1
            If Len(strNode) Then
                ' Debug.Print "G" & strTab & " " & strNode
                ' strTab = vbTab & strTab
            End If
    
            For i = LBound(varPairs) To UBound(varPairs)
    
                ' convert values to metric
                ' Debug.Print "G" & strTab & Split(varPairs(i), ":")(0) & "=" & (Split(varPairs(i), ":")(1)) * 25.4
    
                FieldValue = FieldValue & ", G" & strTab & Split(varPairs(i), ":")(0)
                SaveValue = SaveValue & "', '" & LTrim((Split(varPairs(i), ":")(1)) * 25.4)
    
            Next i
        Next n
    
        strSQL = "INSERT INTO [XMLMachineSettings] (" _
                 & Mid(FieldValue, 3) & ") VALUES(" & Mid(SaveValue, 4) & "');"
    
        Debug.Print strNode & strTab & vbTab & strSQL
        CurrentDb.Execute strSQL, dbFailOnError
    End Sub
    DAO style:
    Code:
    Sub TestXMLValuesDAO()
        Dim strTag As String
        Dim strRet As String
        Dim strSec As String
        Dim strNode As String
        Dim strTab As String
        Dim varNodes As Variant
        Dim varPairs As Variant
        Dim i As Integer, n As Integer
        Dim FieldValue As String
        Dim SaveValue As String
        Dim rs As DAO.Recordset
    
        'strTag = "ToolingList"
        strTag = "OriginList"
        strRet = XMLValues(CurrentProject.Path & "\XMLMachineAdjustableParams.xml", strTag)
    
        varNodes = Split(strRet, vbCrLf)
        ' Debug.Print strTag
    
        ' seed the starting value
        strTab = 53
        Set rs = CurrentDb.OpenRecordset("XMLMachineSettings")
        rs.AddNew
        For n = LBound(varNodes) To UBound(varNodes)
            strSec = Split(strRet, vbCrLf)(n)
            strNode = Trim(Mid(strSec, 1, InStr(1, strSec, " ")))
            strSec = Trim(Mid(strSec, Len(strNode) + 1))
            varPairs = Split(strSec, ";")
    
            strTab = strTab + 1
            If Len(strNode) Then
                ' Debug.Print "G" & strTab & " " & strNode
                ' strTab = vbTab & strTab
            End If
    
            For i = LBound(varPairs) To UBound(varPairs)
    
                ' convert values to metric
                ' Debug.Print "G" & strTab & Split(varPairs(i), ":")(0) & "=" & (Split(varPairs(i), ":")(1)) * 25.4
    
    
                FieldValue = "G" & strTab & Split(varPairs(i), ":")(0)
                SaveValue = LTrim((Split(varPairs(i), ":")(1)) * 25.4)
                Debug.Print FieldValue & "=" & SaveValue
    
                'Put value <SaveValue> in field <FieldValue>
                rs(FieldValue) = SaveValue
    
            Next i
        Next n
        
        'Save, close and exit
        rs.Update
        rs.Close
        Set rs = Nothing
    End Sub
    Let me know how close we are.

    Cheers,
    John

    P.S.
    Always remember to declare your variables.
    Last edited by accesstos; 06-15-2020 at 08:18 AM. Reason: Code editing and... rolled back

  12. #12
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi John

    For my edification, of your suggested methods (SQL or DAO) is there a preferred choice? Does one offer any advantage over the other?

    I've chosen the former and built it in to my code, made a few changes and have a result that works Though I still have to decide on the error checking, bearing in mind future expansion of the project.

    It actually uses a combination of methods to extract the information I want from the XML file. The original approach offered by June7, and yours.

    Although it works just fine, I think it looks a bit heavy-handed and not very elegant or efficient. Some of the XML information I need is easy to get using the string search method, and I don't know if there is any benefit in re-writing this in your style. Perhaps you could explain otherwise.

    Anyway, this code works, as I said. I'd be OK if you think it requires some tidying up re-working. I have attached the DB if you want to have a look at it.

    Just want to add a huge thank you to you, June7 and everyone else on this forum who has been of assistance with this project (it's much larger than this section on which we have been working, and still evolving). Your expertise, knowledge and generosity of time leaves me humbled and grateful.

    [code]
    Option Compare Database

    Private Sub Form_Open(Cancel As Integer)
    Dim XMLFile As String, text As String, textline As String
    Dim MachineName As String, MachineNo As String
    Dim A2MCNumber As String, FeedRateMAX As String
    Dim LPosition As Integer, RPosition As Integer, StringLen As Integer
    Dim strTag As String
    Dim strRet As String
    Dim strSec As String
    Dim strNode As String
    Dim strTab As String
    Dim varNodes As Variant
    Dim varPairs As Variant
    Dim i As Integer, n As Integer
    Dim FieldValue As String
    Dim SaveValue As String
    Dim strSQL As String


    ' Browse for XML File
    Dim stDocName As String
    Dim f As Object
    Dim strFile As String
    Dim strFolder As String
    Dim varItem As Variant
    Dim strFilePath

    ' On Error GoTo MyErrorHandler:

    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    f.Title = "Select XML MachineAdjustableParams File to Import"
    f.ButtonName = "Select"
    f.Filters.Clear
    f.Filters.Add "MachineAdjustableParams File", "*.xml"

    If f.Show Then
    For Each varItem In f.SelectedItems
    strFile = Dir(varItem)
    strFolder = Left(varItem, Len(varItem) - Len(strFile))
    strFilePath = strFolder + strFile

    MsgBox "Importing: " & strFile & vbCrLf & _
    "From: " & strFolder, vbExclamation, "Confirm Import"
    Next

    If strFilePath = Null Then
    Set f = Nothing
    Exit Sub
    End If
    Else
    Set f = Nothing
    DoCmd.Close acForm, Me.Name, acSaveNo
    Exit Sub
    End If

    ' Exit_MyErrorHandler:

    ' Open XML file
    Open strFile For Input As #1
    Do Until EOF(1)
    Line Input #1, textline
    text = text & textline
    Loop
    Close #1

    MachineName = InStr(text, "<MachineName>")
    LPosition = InStr(1, text, "<MachineName>")
    RPosition = InStr(1, text, "</MachineName>")
    StringLen = RPosition - LPosition - 13



    MachineName = Mid(text, MachineName + 13, StringLen)

    ' Check there is a valid machine name in the XML
    If StringLen < 9 Then
    MsgBox "Invalid File Format." & vbCr & "The machine name " & MachineName & " format is not recognised." & vbCr & "Check the XML file and try again.", vbExclamation, "Import File Error"
    Set f = Nothing
    DoCmd.Close acForm, Me.Name, acSaveNo
    Exit Sub
    End If

    MachineNo = Left([MachineName], 5) + Right([MachineName], 4)

    A2MCNumber = InStr(text, "<Code>")
    LPosition = InStr(1, text, "<Code>")
    RPosition = InStr(1, text, "</Code>")
    StringLen = RPosition - LPosition - 6
    A2MCNumber = Mid(text, A2MCNumber + 6, StringLen)

    FeedRateMAX = InStr(text, "<FeedRateMAX>")
    LPosition = InStr(1, text, "<FeedRateMAX>")
    RPosition = InStr(1, text, "</FeedRateMAX>")
    StringLen = RPosition - LPosition - 13
    FeedRateMAX = Mid(text, FeedRateMAX + 13, StringLen) * 25.4

    PlungeRateMAX = InStr(text, "<PlungeRateMAX>")
    LPosition = InStr(1, text, "<PlungeRateMAX>")
    RPosition = InStr(1, text, "</PlungeRateMAX>")
    StringLen = RPosition - LPosition - 15
    PlungeRateMAX = Mid(text, PlungeRateMAX + 15, StringLen) * 25.4

    TravelRateMAX = InStr(text, "<TravelRateMAX>")
    LPosition = InStr(1, text, "<TravelRateMAX>")
    RPosition = InStr(1, text, "</TravelRateMAX>")
    StringLen = RPosition - LPosition - 15
    TravelRateMAX = Mid(text, TravelRateMAX + 15, StringLen) * 25.4

    TravelRate = InStr(text, "<TravelRate>")
    LPosition = InStr(1, text, "<TravelRate>")
    RPosition = InStr(1, text, "</TravelRate>")
    StringLen = RPosition - LPosition - 12
    TravelRate = Mid(text, TravelRate + 12, StringLen) * 25.4

    PlungeRate = InStr(text, "<PlungeRate>")
    LPosition = InStr(1, text, "<PlungeRate>")
    RPosition = InStr(1, text, "</PlungeRate>")
    StringLen = RPosition - LPosition - 12
    PlungeRate = Mid(text, PlungeRate + 12, StringLen) * 25.4

    FeedRate = InStr(text, "<FeedRate>")
    LPosition = InStr(1, text, "<FeedRate>")
    RPosition = InStr(1, text, "</FeedRate>")
    StringLen = RPosition - LPosition - 10
    FeedRate = Mid(text, FeedRate + 10, StringLen) * 25.4

    JogSpeedMode = InStr(text, "<JogSpeedMode>")
    LPosition = InStr(1, text, "<JogSpeedMode>")
    RPosition = InStr(1, text, "</JogSpeedMode>")
    StringLen = RPosition - LPosition - 14
    JogSpeedMode = Mid(text, JogSpeedMode + 14, StringLen) * 25.4

    SeekXYSpeed = InStr(text, "<SeekXYSpeed>")
    LPosition = InStr(1, text, "<SeekXYSpeed>")
    RPosition = InStr(1, text, "</SeekXYSpeed>")
    StringLen = RPosition - LPosition - 13
    SeekXYSpeed = Mid(text, SeekXYSpeed + 13, StringLen) * 25.4

    SeekZSpeed = InStr(text, "<SeekZSpeed>")
    LPosition = InStr(1, text, "<SeekZSpeed>")
    RPosition = InStr(1, text, "</SeekZSpeed>")
    StringLen = RPosition - LPosition - 12
    SeekZSpeed = Mid(text, SeekZSpeed + 12, StringLen) * 25.4

    JerkGrate = InStr(text, "<JerkGrate>")
    LPosition = InStr(1, text, "<JerkGrate>")
    RPosition = InStr(1, text, "</JerkGrate>")
    StringLen = RPosition - LPosition - 11
    JerkGrate = Mid(text, JerkGrate + 11, StringLen) * 25.4

    AccelerationG = InStr(text, "<AccelerationG>")
    LPosition = InStr(1, text, "<AccelerationG>")
    RPosition = InStr(1, text, "</AccelerationG>")
    StringLen = RPosition - LPosition - 15
    AccelerationG = Mid(text, AccelerationG + 15, StringLen)

    AccelMAX = InStr(text, "<AccelMAX>")
    LPosition = InStr(1, text, "<AccelMAX>")
    RPosition = InStr(1, text, "</AccelMAX>")
    StringLen = RPosition - LPosition - 10
    AccelMAX = Mid(text, AccelMAX + 10, StringLen)

    JerkMAX = InStr(text, "<JerkMAX>")
    LPosition = InStr(1, text, "<JerkMAX>")
    RPosition = InStr(1, text, "</JerkMAX>")
    StringLen = RPosition - LPosition - 9
    JerkMAX = Mid(text, JerkMAX + 9, StringLen)

    CentripetalG = InStr(text, "<CentripetalG>")
    LPosition = InStr(1, text, "<CentripetalG>")
    RPosition = InStr(1, text, "</CentripetalG>")
    StringLen = RPosition - LPosition - 14
    CentripetalG = Mid(text, CentripetalG + 14, StringLen)

    BrakeG = InStr(text, "<BrakeG>")
    LPosition = InStr(1, text, "<BrakeG>")
    RPosition = InStr(1, text, "</BrakeG>")
    StringLen = RPosition - LPosition - 8
    BrakeG = Mid(text, BrakeG + 8, StringLen)

    ArcError = InStr(text, "<ArcError>")
    LPosition = InStr(1, text, "<ArcError>")
    RPosition = InStr(1, text, "</ArcError>")
    StringLen = RPosition - LPosition - 10
    ArcError = Mid(text, ArcError + 10, StringLen)

    MinLength = InStr(text, "<MinLength>")
    LPosition = InStr(1, text, "<MinLength>")
    RPosition = InStr(1, text, "</MinLength>")
    StringLen = RPosition - LPosition - 11
    MinLength = Mid(text, MinLength + 11, StringLen)

    F25SensorYOffset = InStr(text, "<F25SensorYOffset>")
    LPosition = InStr(1, text, "<F25SensorYOffset>")
    RPosition = InStr(1, text, "</F25SensorYOffset>")
    StringLen = RPosition - LPosition - 18
    F25SensorYOffset = Mid(text, F25SensorYOffset + 18, StringLen)

    F25SensorXOffset = InStr(text, "<F25SensorXOffset>")
    LPosition = InStr(1, text, "<F25SensorXOffset>")
    RPosition = InStr(1, text, "</F25SensorXOffset>")
    StringLen = RPosition - LPosition - 18
    F25SensorXOffset = Mid(text, F25SensorXOffset + 18, StringLen)

    SizeX = InStr(text, "<x>")
    LPosition = InStr(1, text, "<x>")
    RPosition = InStr(1, text, "</x>")
    StringLen = RPosition - LPosition - 3
    SizeX = Mid(text, SizeX + 3, StringLen) * 25.4

    SizeY = InStr(text, "<y>")
    LPosition = InStr(1, text, "<y>")
    RPosition = InStr(1, text, "</y>")
    StringLen = RPosition - LPosition - 3
    SizeY = Mid(text, SizeY + 3, StringLen) * 25.4

    SizeZ = InStr(text, "<z>")
    LPosition = InStr(1, text, "<z>")
    RPosition = InStr(1, text, "</z>")
    StringLen = RPosition - LPosition - 3
    SizeZ = Mid(text, SizeZ + 3, StringLen) * 25.4


    ' Sub TestXMLValuesSQL()

    strTag = "OriginList"
    ' strRet = XMLValues(CurrentProject.path & "\XMLMachineAdjustableParams.xml", strTag)
    strRet = XMLValues(strFilePath, strTag)
    varNodes = Split(strRet, vbCrLf)

    ' seed the starting value for the origins G54 is first valid origin
    strTab = 53
    For n = LBound(varNodes) To UBound(varNodes)
    strSec = Split(strRet, vbCrLf)(n)
    strNode = Trim(Mid(strSec, 1, InStr(1, strSec, " ")))
    strSec = Trim(Mid(strSec, Len(strNode) + 1))
    varPairs = Split(strSec, ";")

    strTab = strTab + 1
    If Len(strNode) Then
    ' Debug.Print "G" & strTab & " " & strNode
    ' strTab = vbTab & strTab
    End If

    For i = LBound(varPairs) To UBound(varPairs)

    FieldValue = FieldValue & ", G" & strTab & Split(varPairs(i), ":")(0)
    SaveValue = SaveValue & "', '" & LTrim((Split(varPairs(i), ":")(1)) * 25.4)

    Next i
    Next n

    strSQL = "INSERT INTO [XMLMachineSettings] (" _
    & Mid(FieldValue, 3) & ", [MachineName], [MachineNo], [A2MCNo], [FeedRateMAX], [PlungeRateMAX], [TravelRateMAX], [TravelRate],[PlungeRate],[FeedRate],[JogSpeedMode],[SeekXYSpeed],[SeekZSpeed],[JerkGrate],[AccelerationG],[AccelMAX],[JerkMAX],[CentripetalG],[BrakeG],[ArcError],[MinLength],[F25SensorYOffset],[F25SensorXOffset],[SizeX],[SizeY],[SizeZ],[ImportDate])" & _
    "VALUES(" & Mid(SaveValue, 4) & "', '" & MachineName & "', '" & MachineNo & "', '" & A2MCNumber & "', '" & FeedRateMAX & "', '" & PlungeRateMAX & "', '" & TravelRateMAX & "','" & TravelRate & "','" & PlungeRate & "', '" & FeedRate & "', '" & JogSpeedMode & "', '" & SeekXYSpeed & "','" & SeekZSpeed & "','" & JerkGrate & "','" & AccelerationG & "','" & AccelMAX & "','" & JerkMAX & "','" & CentripetalG & "','" & BrakeG & "','" & ArcError & "','" & MinLength & "','" & F25SensorYOffset & "','" & F25SensorXOffset & "','" & SizeX & "','" & SizeY & "','" & SizeZ & "','" & Now() & "');"

    ' Debug.Print strNode & strTab & vbTab & strSQL
    CurrentDb.Execute strSQL, dbFailOnError

    ' MyErrorHandler:
    ' MsgBox "Invalid File" & Chr(13) & "Check the XML file and make sure it has the correct structure", vbExclamation, "Import File Error"
    ' Resume Exit_MyErrorHandler:

    End SubXML Files.zipAttachment 42192XML Files.zip
    Last edited by sheusz; 06-16-2020 at 12:28 AM.

  13. #13
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi again!

    OK, maybe a library like MSXML2, in this case, looks like a anti-aircraft weapon for waterfowl hunting. For the moment, some low-level string procedures are enough to read some substrings in a well structured text with known tags. The power of the MSXML2 will appear in a biger and normalized schema and, especially, if you will need to get the info via http links (you said: "they will be emailed from sources all around the world").

    Your code is very good and seems like you are know what you are doing but, when you see code repeating, it's time to create a new procedure (and when you see info repeating in a database table, it's time to create a new table )

    Today, I use the "DAO style" for my suggestion because of its comfortable and because it simplify the code enough. With a recordset, I don't have to worry for the type of values or NULLs and I don't have to know the names of the fields.
    If we would haven't to do metric conversions, we whould use one loop for all fields that the names of them exists as tag names in the XML file. That would eliminate many lines of code.
    On the other hand, I think that the database executions are more efficient as more direct.
    In addition, I left the MSXML2 for the future.

    I return your sample database with my additions/suggestions.
    You will find a new module (modXML) with some procedures and a new form (frmMachineInfo) that using those procedures.

    I hope helps and I have to say that is my pleasure to helps people that appreciate it, because, from this procedure, we all learn and we all win.

    Cheers,
    John
    Attached Files Attached Files

  14. #14
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi John

    Thank you for the lengthy and detailed reply. Thanks also for the modified DB. I will closely look at what you have done and try to learn from it. Some of it seems beyond me, I have to admit.

    I'm re-thinking the conversion to metric. The "native" settings for the machines that use these XML files is imperial (inches), so in some countries we have to convert these values to metric via the user interface. I am thinking that I will adopt the same procedure in this DB. That is, store the values in their original format, and do conversions on the fly on forms, in queries and reports as required. I could also look at trying to capture regional setting off the computer to determine if this is even needed.

    The only problem with this approach is that the work gets more tedious elsewhere in the DB. So maybe having extra code at the import stage to do the conversion is better than the on the fly conversion approach. The values do not have to be re-converted in to imperial values at any time, so storing them as imperial values is of no real benefit.

    Some background for you. The XML file is a snapshot of a machines' settings at the time the XML is exported. Whenever the XML file for the same machine is created, some, all or none of the settings could be different. (except for MachineName & MachineSerialNo). That is why all the data is stored in a single table. Over time we will be able to see the rate of change for any machine, based on the number of XML files we have for that machine. I'm even thinking that I will be able to graph these changes over time. But that is for another time.

    I will use the MachineSerialNo field in the XMLMachineSettings table to link the XML file to a Customer table in the DB.

    You have given me a lot to consider, so I will spend some time going through it and see what works best.

    I will keep you updated.

    Thanks again.

  15. #15
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Stephen!

    I hope you understand my posts so good as you do with my code, because English is not my native language, so, I prefer to speak in... VBA.

    That is, store the values in their original format, and do conversions on the fly on forms, in queries and reports as required.
    That is the way that I prefer and that I suggest to you. Keep the data in database at their pure format and get the information that you want at any time via the user interface is the advice that you will get from anyone that deals with databases. The conversion before the storage eliminates the data and keeps just the info, that breaks the basic database rule about calculated values, even if in your case this is not a big deal.

    Are the units of machine settings always in imperial system? What about the node <Units>mm/min</Units> in the XML files? What about the "MM" in the machine's name that you subtract?

    I suggest to keep the data from the XML file as they are in the [XMLMachineSettings] table, and then, you can do anything with them via the appropriate expressions. So, you keep the importing processes simple and the flexibility of the application at the higher level.
    For example, you can store the <MachineName> value as it is and subtract the "MM" in a calculated field of a query. There is no need to store the modified value.
    I did follow this path for my today sample that I attach. I removed the field [MachineNo] from the table [XMLMachineSettings], I renamed the field [A2MCNo] to [Code] and, in place of them, I created the query below for the machine identification:
    Code:
    SELECT DISTINCT 
    XMLMachineSettings.MachineName, 
    XMLMachineSettings.Code, 
    Replace([MachineName],"MM","") AS MachineNo, 
    XMLMachineSettings.Code AS A2MCNo 
    FROM XMLMachineSettings;
    In addition, you will find some new procedures in the module "modXML" both for the import and for the conversion/manipulation of the data. Some of them creates a new querydef (qryMachineValuesMetrics) that converts the values, based on the fields of "qryMachineValuesToConvert", and an other (qryMachineSettingsMM) that holds the converted values but pretend the non converted[!..] (qryMachineSettings).
    The form "frmMachineInfo" uses the both last as RecordSource according to chose of units (mm/in).

    I hope helps and I hope my suggestions inspires you.

    Cheers,
    John
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Correct way to import into Access
    By templeowls in forum Access
    Replies: 1
    Last Post: 04-03-2019, 06:22 AM
  2. Replies: 2
    Last Post: 10-29-2018, 12:30 PM
  3. Replies: 11
    Last Post: 12-04-2017, 07:18 AM
  4. File Picker To Only Allow Single File Section
    By jo15765 in forum Programming
    Replies: 2
    Last Post: 05-29-2017, 04:25 PM
  5. Replies: 2
    Last Post: 01-13-2015, 07:16 AM

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