CODEWITHSUNDEEP
xmlgoogle-sheetsweb-scrapingformattinggoogle-sheets-formula
VixinG
VixinG

Reputation: 1407

ImportXML function format data in spreadsheet

I'm trying to import some live match data from XML feed using the ImportXML function and that's how it looks when it runs:

importXML data in cells

Basically 2 rows = 1 match (Spain is playing with Italy, Everton with Liverpool, etc.). Teams listing is the first ImportXML function in A1. The score is another ImportXML function located in B1. I can't manage to move the blue fields to the orange spot, so both teams are in one row (1 match per row). Do I have to create separate ImportXML for every match or something? I have no idea.

https://docs.google.com/spreadsheet/ccc?key=0AiYrkv5aSrfmdHQ1b19ORm1TODdBcUFpVk1MQjlSMHc&usp=sharing

Thanks in advance

Upvotes: 1

Views: 3288

Answers (1)

Harold
Harold

Reputation: 3337

I propose you to do it with 4 formulas (one in each column). here the demo. and bellow the explainations:
You need to look for the first or the second value of the node you are interested. For that, I just modified a little the XML. Request for the first country it's: 

=ImportXML("http://api.espn.com/v1/sports/soccer/events/top?_accept=text/xml&apikey=9nrqbfsah2m6sprwtntth3kv";"//competitors/competitorsItem[1]/team/name")

As you can see I added [1] just after competitorsItem. So I limit the results to only the first occurence. Then on the fourth column I do the same but I changed [1] for [2] to get the opponent country. I found the documentation here: http://www.w3schools.com/xpath/xpath_syntax.asp

I hope it will be something usefull for you.

Harold

EDIT

you'll find here a variant of the previous demo where you get all the results at once.
the formula is: 

=ImportXML("http://api.espn.com/v1/sports/soccer/events/top?_accept=text/xml&apikey=9nrqbfsah2m6sprwtntth3kv","//competitors/competitorsItem[2]/score|//competitors/competitorsItem[2]/team/name|//competitors/competitorsItem[1]/score|//competitors/competitorsItem[1]/team/name")

But of course you are then forced to remix them. For that I used an array formula:

={index(A:A;(row()-1)*4+2),index(A:A;(row()-1)*4+1),index(A:A;(row()-1)*4+3),index(A:A;(row()-1)*4+4)}

Upvotes: 1

Related Questions