Results 1 to 5 of 5
  1. #1
    ampd85 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2014
    Posts
    2

    Importing XML

    Hi Everyone,



    I am trying to import XML into access however have noticed that Access 2013 is not importing fields/parameters found within tags. For example:

    <locations>
    <locationelement description="Master Location" dns="tester.test.com" key="M0" lastupdated="Never Updated" pdport="5555" port="5551">
    <monitoringperiodtimezone>EST5EDT</monitoringperiodtimezone>

    <monitoringperiod days="Sunday" hours="28-95" />

    <monitoringperiod days="Monday,Tuesday,Wednesday,Thursday,Friday,Saturday" hours="all" />
    </locationelement>



    </locations>


    When importing, Access creates a table locationelement with fields for monitoringperiodtimezone (containing EST5EDT) and monitoringperiod (containing nothing). I was wondering if there is a way to access fields such as description, dns, key etc or days, hours, ie fields that are within a tag.

    Any help will be greatly appreciated.




  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    can you upload an example of a real xml file or is the text you posted indicative of what it would really look like?

    There have been a number of threads on XML files not importing correctly but basically an XML file is a text file that you can parse because it's got a loop structure.

    You can parse text files easily using filesystemobject functions and append records to a table rather than facing an import wizard that may not function the way you think/want.

    This link shows you how to open a file and cycle through it one line at a time:

    http://msdn.microsoft.com/en-us/libr...=vs.84%29.aspx

    From there it's pretty easy to parse individual items from each of your lines. You can key on the elements rather than the text markers (") for instance instr(sline, "description=") would give you the starting postion of the word 'description in the locationelement line then you'd add 13 places to get you to the start of the actual text value you want MASTER LOCATION and so on. Once you extract the values you want you can then append them to your table with a db.execute (<SQL Statement>) command.

  3. #3
    ampd85 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2014
    Posts
    2
    Yeah, the above XML is actually a real world example of something I was trying to have parsed however there are plenty more that I would need to work with. Just didn't know if there was some transform function to have Access recognize those tags from the get-go, or if I was just doing something wrong...

    Thanks for the information, I'll start giving that a try but if anyone knows of a quicker approach that would be awesome

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Yeah I just tried importing this and the import wizard does not give you the ability to parse elements of a given line so the text parsing is likely your best bet.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I am not an XML expert but...

    I would not consider that XML to be well formed. It seems data is there that is not within an element. For instance, there are not any element tags for <monitoringperiod days="Sunday" hours="28-95" />

    I would usually suggest parsing XML by first creating an InternetExplorer object to interact with the XML file. Neither the wizard or internet explorer is going to know what to do with the text that does not have tags.

    All you have there is literal text.

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

Similar Threads

  1. Importing CSV
    By mike02 in forum Access
    Replies: 5
    Last Post: 08-01-2012, 05:21 PM
  2. Importing a PDF
    By JHighley in forum Access
    Replies: 6
    Last Post: 07-24-2012, 08:39 AM
  3. Need help importing to SQL
    By winterh in forum SQL Server
    Replies: 14
    Last Post: 03-28-2012, 12:36 PM
  4. Help with Importing
    By Souperbc in forum Programming
    Replies: 2
    Last Post: 04-13-2011, 12:45 PM
  5. Example Importing routine
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-24-2010, 12:35 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