Results 1 to 12 of 12
  1. #1
    JoeDirte is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2018
    Posts
    5

    Import XML to Access

    Im working on a personal project where I need to import an XML(several) file to Access. I play a game called Warhammer 40k that has about 14 different armies, each army with 70+ units, each unit with different stats.

    There is an open source platform with all of the data in XML with .cat extensions. When attempting to import the data, it creates useless tables, with almost no data.

    I fell like this should be a simple import, but with very little knowledge of VBA or XML, I'm stuck. I'll include a link below for the data file. If someone is willing/able to assist, I'd be forever in your debt (or would be willing to compensate a little for your time and effort). Thank you for your time and effort regarding this!

  2. #2
    JoeDirte is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2018
    Posts
    5

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Tried to use the wizard to import and keep getting 'invalid XML character' error.

    If the wizard won't work, alternative is VBA manipulating text file object to read the XML file one line a time and parse the string.
    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.

  4. #4
    JoeDirte is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2018
    Posts
    5
    Thanks June7... now.... how do i do that?

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Dirte.zip
    See attached, this will load with errors. The errors are caused by data fields being longer than 255 characters, but there are only 6 or 8 of them.
    You could fix this by looking at the import error table to find the offending table, then change those fields (looks like it's the Description field) to MEMO or LongText, depending on your Access version.
    After doing that, If you could find the data in the XML file, copy its text into the table fields manually. Memo or Long Text can hold thousands of text characters.

  6. #6
    JoeDirte is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2018
    Posts
    5
    I've done that part already and have gotten rid of those errors. There is a significant amount of data that is being skipped. I'm not entirely sure why.

    For example(if opened in an xml editor like notepad++): Look at line 951...

    <characteristic name="Range" characteristicTypeId="6fa97fa8-ea74-4a27-a0fb-bc4e5f367464" value="12&quot;"/>

    It should be pulling:

    Range
    12"

    into a table. I'm guessing that is because Access isn't seeing things in " " as data?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I view the xml in Notepad and I see &quot; code which might be causing issue.
    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.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, seeing as how I jumped in without reading all the posts first, I might as well upload what I got by using Excel to import an xml file as a sheet. Makes little sense to me, and may be a different xml file than the one that seems to hold your error, as I can't find 6fa97fa8-ea74-4a27-a0fb-bc4e5f367464 in it. However, it is a table of sorts, but I had to remove about 6,000 rows just to get the zip small enough to upload.
    Maybe it will help.

    xlm1.zip
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    JoeDirte is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2018
    Posts
    5
    Yeah, you got it further along than I did, but for some reason it isn't picking up the right info.

    I found that access isn't "seeing" the data that is in " "

    It should be something like:
    Name BS WS ...
    Militarum Tempestus Scions 4+ 4+

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    for some reason it isn't picking up the right info.
    Could be a different file.

  11. #11
    AdrianG001 is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2018
    Posts
    9
    Hey,

    You can import XML to access referring the below guide
    addictivetips.com/microsoft-office/import-xml-file-in-access-2010/

    Adrian Gates
    Sr. Cloud Expert - CloudDesktopOnline

  12. #12
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    I haven't looked at the attached files but I have found importing XML with " " to be problematic, especially if you are working with the XML in VBA. I usually do a Find & Replace on the XML specs to change all the " to '. If you do this, make sure it isn't done in a program like Word because then the single-quote will turn into an angled quotation mark which is not the same thing.

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

Similar Threads

  1. Replies: 5
    Last Post: 12-11-2017, 01:36 PM
  2. Replies: 1
    Last Post: 07-09-2017, 11:14 PM
  3. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  4. Replies: 3
    Last Post: 10-08-2014, 11:05 AM
  5. Replies: 10
    Last Post: 06-16-2014, 08:37 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