XML Data import

[Shaun]

Hi All


I am trying to import data from an XML webpage/file but I cannot seem to get the code quite right to import the desired information. I wonder if anyone would care to take a look please?


The webpage address is

http://tatts.com/pagedata/racing/2013/4/5/RaceDay.xml


The data I am trying to extract is:

- MeetingCode

- HiRaceNo

- SortOrder

- VenueName

- Abandoned

- MeetingType


The line in the xml is:


[code]+<Meeting MeetingCode="BR" HiRaceNo="7" SortOrder="0" VenueName="Doomben" Abandoned="N" MeetingType="R">

My issue is that I can only get the first line not the remaining 23 contained in the document. The code I have at this stage is:

[pre]Sub LoadRaceDay()

Dim xmldoc As MSXML2.DOMDocument

Set xmldoc = New MSXML2.DOMDocument

xmldoc.async = False
xmldoc.Load ("http://tatts.com/pagedata/racing/2013/4/5/RaceDay.xml&#34

If (xmldoc.parseError.ErrorCode <> 0) Then
MsgBox ("An error has occurred: " & xmldoc.parseError.reason)
Else
Set RaceDay = xmldoc.SelectNodes("//Meeting&#34

Sheet1.Cells.Clear

For i = 0 To (RaceDay.Length - 1)

Set RaceDay = RaceDay.Item(i)

Set MeetingCode = RaceDay.Attributes.getNamedItem("MeetingCode&#34
Set VenueName = RaceDay.Attributes.getNamedItem("VenueName&#34
Set HiRaceNo = RaceDay.Attributes.getNamedItem("HiRaceNo&#34
Set MeetingType = RaceDay.Attributes.getNamedItem("MeetingType&#34
Set Abandoned = RaceDay.Attributes.getNamedItem("Abandoned&#34

If Not MeetingCode Is Nothing Then
Sheet1.Cells(i + 1, 1) = MeetingCode.Text
End If

If Not VenueName Is Nothing Then
Sheet1.Cells(i + 1, 2) = VenueName.Text
End If

If Not HiRaceNo Is Nothing Then
Sheet1.Cells(i + 1, 3) = HiRaceNo.Text
End If

If Not MeetingType Is Nothing Then
Sheet1.Cells(i + 1, 4) = MeetingType.Text
End If

If Not Abandoned Is Nothing Then
Sheet1.Cells(i + 1, 5) = Abandoned.Text
End If

Next
End If

End Sub[/code][/pre]
Any assistance would be greatly appreciated.


Cheers


Shaun

 

[NARAYANK991]

Hi Shaun ,


The error is in the following statement , and the ones which refer to the erroneous assignment.


The statement :


For i = 0 To (RaceDay.Length - 1)


is executed again and again , which means that :


RaceDay.Length


needs to be preserved ; when you use a statement within the loop , such as :


Set RaceDay = RaceDay.Item(i)


which modifies RaceDay , everything goes haywire !


Either use a different variable in this statement :


Set Raceday = xmldoc.SelectNodes("//Meeting")


and use that variable in the For statement upper limit :


For i = 0 To (RaceDay.Length - 1)


or


use a different variable in this statement :


Set RaceDay = RaceDay.Item(i)


in which case , you have to use this variable in all of these statements :




Set MeetingCode = RaceDay.Attributes.getNamedItem("MeetingCode")

Set VenueName = RaceDay.Attributes.getNamedItem("VenueName")

Set HiRaceNo = RaceDay.Attributes.getNamedItem("HiRaceNo")

Set MeetingType = RaceDay.Attributes.getNamedItem("MeetingType")

Set Abandoned = RaceDay.Attributes.getNamedItem("Abandoned")




Narayan

 

[imgonna]

Hi Shaun


Pulling data from XML is something I've done a fair bit - and the way you are going about it was the way I used to do it until 2 things:


1) Excel 2013 - WEBSERVICE function - pulls in the XML feed directly and then you can extract from the XML tree.


BUT that wont work for you as the XML contains too many characers for Excel to parse.


When that happens I turn to: http://nielsbosma.se/projects/seotools/


SEO TOOLs, despite its name does many things - I this case it scrapes XML and adds a new function to Excel.


So, to read in your data:


A2 =XPathOnUrl("http://tatts.com/pagedata/racing/2013/4/5/RaceDay.xml","raceday/meeting[1]",VenueName) - returns the data held in meeting[1] and its VenueName -- in this case Ipswich.


So, without any coding, you can access the entire XML file.


Download the AddIn and follow the onsite instructions.


I've put an Excel sheet with this in: http://dl.dropbox.com/u/13025982/XML_Parse.xlsx (but you'll need the AddIn for it to work).


A real time saver for XML data.


Glen

 

[Shaun]

Hi Narayan and Glen


Thank you both for taking a look at my problem, I very much appreciate it.


Narayan:


Thank you, it now works perfectly.


Glen:


That looks to be a really useful add-in. I am just learning about XML stuff and getting that list is one small part of a larger xml grabbing exercise.


The list generated tells me how race meets, where, type and number of races at each venue. From there I can remove race types which are not relevant (type T and G).


In the address above there are 77 races spread accross 12 venues. From there I am able to construct the address to collect the ramaining information using the address:

http://tatts.com/pagedata/racing/2013/4/5/BR7


where I will be able to set [code]2013, [code]4, [code]5

, BR[/code], and 7[/code] as variables and collect the data for the 77 races on that date and do that to collect prior history and upcoming meets.


http://tatts.com/pagedata/racing/year/month/day/venue & race number.xml[/code]


I have not yet had the chance to play with the add-in suggested, but I don't know whether the add-in will allow me to automate this, or let me export that data in to Access. My skills in relation to all of this are at best - poor, so I am sure there will be most certainly better ways to accomplish this outcome, but I am working by trial and error on very limited knowledge.


Thank you both again, I really appreciate your help.


Cheers


Shaun