Solved

Import XML file to ACCESS

Posted on 2007-04-08
20
722 Views
Last Modified: 2011-04-14
In MS Access(2003) I want to import data from an XML file. Using the Access Get External Data, Import function the XML file is stored as multiple tables. When I opened the same XML file in Excel the file is "flattened" into one worksheet. That is the format I would like to end up with in Access. There is no schema file provided with the XML file I receive.

Here is part of the XML file provided

<?xml version='1.0' encoding='ISO-8859-5'?>
<event>
<track>Dairyland</track>
<date>4/8/2007</date>
<perf>Afternoon</perf>
<races>
<race>
<race_number> 1</race_number>
<race_grade>C</race_grade>
<race_distance>C</race_distance>
<runners>
<runner>
<post> 1</post>
<dog>A Bar Trey</dog>
<starts> 16</starts>
<first> 2</first>
<second> 3</second>
<third> 0</third>
<fourth> 1</fourth>
<lines>
<line>
<tid>DP</tid>
<dat>4/4/2007</dat>
<sig>a</sig>
<rac>7</rac>
<dst>550</dst>
<trk>F</trk>
<wtm>31.07</wtm>
<twt>73</twt>
<pst>7</pst>
<brk>7</brk>
<ca1>5</ca1>
<by1></by1>
<ca2>7</ca2>
<by2></by2>
<fin>8</fin>
<fby>8</fby>
<atm>31.63</atm>
<fav></fav>
<odd>9.50</odd>
<grd>C</grd>
<com>Blkd Far Turn Rl</com>
<arn>8</arn>
</line>
<line>
<tid>DP</tid>
<dat>3/29/2007</dat>
<sig>S</sig>
<rac>4</rac>
<dst>550</dst>
<trk>F</trk>
<wtm>30.77</wtm>
<twt>72</twt>
<pst>8</pst>
<brk>4</brk>
<ca1>2</ca1>
<by1></by1>
<ca2>2</ca2>
<by2></by2>
<fin>2</fin>
<fby>5</fby>
<atm>31.1</atm>
<fav></fav>
<odd>-----</odd>
<grd>Scl</grd>
<com>Next Best Inside</com>
<arn>7</arn>
</line>
<line>
<tid>DP</tid>
<dat>3/25/2007</dat>
<sig>S</sig>
<rac>3</rac>
<dst>550</dst>
<trk>F</trk>
<wtm>31.13</wtm>
<twt>73½</twt>
<pst>4</pst>
<brk>4</brk>
<ca1>3</ca1>
<by1></by1>
<ca2>6</ca2>
<by2></by2>
<fin>6</fin>
<fby>20</fby>
<atm>32.54</atm>
<fav></fav>
<odd>-----</odd>
<grd>Scl</grd>
<com>Clipped 1st Turn Rail</com>
<arn>7</arn>
</line>
<line>
<tid>DP</tid>
<dat>3/18/2007</dat>
<sig>a</sig>
<rac>18</rac>
<dst>550</dst>
<trk>F</trk>
<wtm>31.16</wtm>
<twt>73</twt>
<pst>7</pst>
<brk>7</brk>
<ca1>8</ca1>
<by1></by1>
<ca2>8</ca2>
<by2></by2>
<fin>8</fin>
<fby>10</fby>
<atm>31.9</atm>
<fav></fav>
<odd>5.30</odd>
<grd>C</grd>
<com>Crowded 1st Turn Rail</com>
<arn>8</arn>
</line>
0
Comment
Question by:glh128
  • 7
  • 7
  • 3
  • +1
20 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18873161
0
 

Author Comment

by:glh128
ID: 18873212
I'm not sure I understand that document. It looks to me that I need a transform file in place first? How do I build that tranform file????

thanks!
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18873259
Yea, that was my problem too. Sometimes MS documentation isn't as helpful as it should be. My problem is I can't test with you because I'm using A2000. I do have a question though....if you CAN import it using excel the way you want it, you can then import from excel? Sounds like a workaround, but if it works...I'm sure there are other Experts here that have more experience than I do directly importing XML...hopefully they'll join the thread.
J
0
 

Author Comment

by:glh128
ID: 18873270
Yeah if I can't figure out any other way I can always open the XML in excel and then import to Access. But I want to automate the whole process and would prefer to go straight to Access. Looks to me like the TransformXML method is what I want. I just need to understand how to build the datatransform (XSL) file to use.

Thanks for your help!! Maybe somebody else will show us both how to do it.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18874754
Well it's a holiday - so I might as well pop in.
Access is a relational database application development environment.
It's doing you the favour of importing your data as separate entities.  IMHO you should let it.
If, for whatever reason, you decide to then batch it all together in one table (I don't see the value in doing so) then you can run an action query on the resulting tables to do so.
Excel "flattens" the data because it has little choice.  (It *could* use separate sheets - but wouldn't be especially helpful).

You could, of course, parse the XML yourself.  Which is much more work - but you'd gain control.
However I really can't see much point in doing all that - only to dump it all into a single table.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 18874964
Use code - like this simple function:

Public Function ImportXML()
  Application.ImportXML "d:\path\sample.xml", acStructureAndData
End Function

That will create four tables:

  event
  race
  runner
  line

This is runner from your sample data:
post      dog      starts      first      second      third      fourth
 1      A Bar Trey       16       2       3       0       1

/gustav
0
 

Author Comment

by:glh128
ID: 18875109
LPurvis - I would have no problem letting Access do it's thing. The problem is when Access does that there are no fields common in the tables that I can later JOIN on. I get tables for event,race,runner, and lines but without something to tie the tables together they are useless.
0
 

Author Comment

by:glh128
ID: 18875128
cactus_data --
That will create four tables:

  event
  race
  runner
  line

This is runner from your sample data:
post      dog      starts      first      second      third      fourth
 1      A Bar Trey       16       2       3       0       1

Yes I get 4 tables when I do a standard import of the XML into Access. But those 4 tables need to be JOINED in order to use the data. And there is no common field between them.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18875426
Re: "no fields common in the tables"

Yeah - I'd wondered if that might be the problem actually.
You're going to have to parse the XML yourself manually.
Can you provide a more full example of your XML?
Perhaps as an attachment file?
www.ee-stuff.com
0
 

Author Comment

by:glh128
ID: 18875466
LPurvis --- An example file is only about 700k, but I don't see where I can add attachments???
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18875485
The above link - you log in with your EE UN and PW.
Then post the attachment at the URL of this question
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22498730.html
0
 

Author Comment

by:glh128
ID: 18875516
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 18875810
I believe this is a limitation of the XML import function of Access:

<quote>
Access 2003 (or 2002 for that matter) does not create relationships between
tables when importing from XML. As Joe suggested, when importing arbitrary
XML, Access makes a best guess as to how many tables should be created from
the XML. When importing Access created XML, you should get back the exact
same table structure that was used to export to XML, but the relationships
will be lost.

Regards,
Mike Wachal
Microsoft Corporation
</quote>

Source:
http://groups.google.com/group/microsoft.public.access.externaldata/browse_thread/thread/f42d1eeacabc85a6/f7d4c92759799877%23f7d4c92759799877

There are many ways and tools to read XML data. One that might be of value for you is the free XML component from Chilkat:
http://www.chilkatsoft.com/xml-activex.asp

/gustav
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18887392
First chance to look at this for a while (though to be fair I'm not here at all at the mo ;-)
If you add the tables to your db and then the appropriate PK's and FK's then you could go with code like the following.  (It could be split off into separate functions for greater readability and efficiency).

Sub ParseXML()

    Dim xmlDOM              As DOMDocument
    Dim objEvents           As IXMLDOMNodeList
    Dim objEvent            As IXMLDOMNode
    Dim objRaces            As IXMLDOMNodeList
    Dim objRace             As IXMLDOMNode
    Dim objRunners          As IXMLDOMNodeList
    Dim objRunner           As IXMLDOMNode
    Dim objLines            As IXMLDOMNodeList
    Dim objLine             As IXMLDOMNode
    Dim objEventDetail      As IXMLDOMNode
    Dim objRaceDetail       As IXMLDOMNode
    Dim objRunnerDetail     As IXMLDOMNode
    Dim objLineDetail       As IXMLDOMNode
    Dim db                  As DAO.Database
    Dim rstEvent            As DAO.Recordset
    Dim rstRace             As DAO.Recordset
    Dim rstRunner           As DAO.Recordset
    Dim rstLine             As DAO.Recordset
    Dim lngEvent            As Long
    Dim lngRace             As Long
    Dim lngRunner           As Long
   
    Set xmlDOM = New DOMDocument
    xmlDOM.Load CurrentProject.Path & "\Example XML.xml"
   
    Set objEvent = xmlDOM.documentElement 'Get event node
   
    Set db = CurrentDb
    Set rstEvent = db.OpenRecordset("event", dbOpenDynaset)
    Set rstRace = db.OpenRecordset("race", dbOpenDynaset)
    Set rstRunner = db.OpenRecordset("runner", dbOpenDynaset)
    Set rstLine = db.OpenRecordset("line", dbOpenDynaset)
   
    rstEvent.AddNew
        For Each objEventDetail In objEvent.childNodes
            If objEventDetail.nodeName <> "races" Then
                rstEvent.Fields(objEventDetail.nodeName) = objEventDetail.Text
            Else
                lngEvent = rstEvent.Fields("eventID")
                Set objRaces = objEventDetail.childNodes
                For Each objRace In objRaces
                    rstRace.AddNew
                        rstRace.Fields("eventID") = lngEvent
                        For Each objRaceDetail In objRace.childNodes
                            If objRaceDetail.nodeName <> "runners" Then
                                rstRace.Fields(objRaceDetail.nodeName) = objRaceDetail.Text
                            Else
                                lngRace = rstRace.Fields("raceID")
                                Set objRunners = objRaceDetail.childNodes
                                For Each objRunner In objRunners
                                    rstRunner.AddNew
                                        rstRunner.Fields("raceID") = lngRace
                                        For Each objRunnerDetail In objRunner.childNodes
                                            If objRunnerDetail.nodeName <> "lines" Then
                                                rstRunner.Fields(objRunnerDetail.nodeName) = objRunnerDetail.Text
                                            Else
                                                lngRunner = rstRunner.Fields("runnerID")
                                                Set objLines = objRunnerDetail.childNodes
                                                For Each objLine In objLines
                                                    rstLine.AddNew
                                                        rstLine.Fields("runnerID") = lngRunner
                                                        For Each objLineDetail In objLine.childNodes
                                                            rstLine.Fields(objLineDetail.nodeName) = objLineDetail.Text
                                                        Next
                                                    rstLine.Update
                                                Next
                                            End If
                                        Next
                                    rstRunner.Update
                                Next
                            End If
                        Next
                    rstRace.Update
                Next
            End If
        Next
    rstEvent.Update

   
exitHere:
    rstLine.Close
    rstRunner.Close
    rstRace.Close
    rstEvent.Close
   
End Sub
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18887403
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 18887914
But this requires that new fields for IDs have been appended to existing tables, and still no relations are created.

My understanding is, that the questioneer is looking for an automatic method that will read the XML file as is, create the tables, and establish relations as expressed by the tree structure of the XML file.

/gustav

0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 250 total points
ID: 18888276
<shrugs/>
And I thought I was going a bit OTT doing that much without leaving *something* for the OP to complete... :-S
(As we're not rent-a-coder-for-free after all :-)

OK then.
So we're assuming what?  That we can't create the tables in advance to receive the data?
Should we at least assume the entities?
Or do we walk the tree structure and create everything from scratch? (Would be more work again but still doable naturally.)

Let's keep it that stage simpler for now - as it seems reasonable glh128 needs to at least know the entities that are being imported (as they must surely be relelvant to the application).
(After all - what use would have been a pre-prepared query to join them back together if we couldn't even be sure of the entities?).

So we can save ourselves some work for now - use Access' import to create the tables first then just add the PK's and Foreign keys.  (Relationships aren't utterly vital - but no problem if need be, we don't want Ref Integ though - due to the way we're adding records).

So in this example - you'd just run  SetUpTables  before  ParseXML (or as part of it).
https://filedb.experts-exchange.com/incoming/ee-stuff/3144-XMLImport.zip
(Still not meant to be a be all method - depends on the requirements still).
0
 

Author Comment

by:glh128
ID: 18888818
LPurvis -- You got it !!!! Yes I could have created the tables myself before hand, but your code does everything for me!! I need to study it more so I understand how it works. With the relationships already created I will just create a query that joins the tables together in the form I need and append the data to my "historical" table.

Thanks again!!!
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18892924
Welcome.  Glad you're sorted.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18893008
Leigh,
All that for free!! Wow...you'll get a reputation!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now