Results 1 to 6 of 6
  1. #1
    sheusz is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    61

    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 online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,229
    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 Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    61
    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 online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,229
    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 Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    61
    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 online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,229
    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.

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 - Senior Forums