Google Spreadsheet importxml timestamp

0
votes

I been trying for over 2 hours to import timestamp from zap2it.com link to my google spreasheet.



Here is link I am trying to importxml from.
http://affiliate.zap2it.com/tvlistings/ZCGrid.do?zipcode=78238&lineupId=DISH641:-



Here is what I am tryign to import
enter image description here

Here is what I tried so far 

=importxml("http://affiliate.zap2it.com/tvlistings/ZCGrid.do?aid=dish&pkg=8388608&fromProvider=true&zipcode=78238&x=52&y=18"&B1,"//body//div[3]/div/div/div[3]/div/div")




EDIT I was able to improve and get better results

//body//div[3]/div/div/div[1]//*

but it shows timestamp from all over the page. not exactly what I need.

1
xpathxml-parsinggoogle-docsgoogle-sheets

1 Answers

1
votes

[The first complication is that the data stream returned from dereferencing that URI is not actually XML; it has several thousand well-formedness errors (unescaped ampersands in URIs, unescaped ampersands and less-than signs in scripts, some embedded HTML, some miscellaneous errors). Since you're not reporting problems from that, however, I'll assume that somewhere between the server and your XPath expression someone is doing some tidying.]

I think you'll get better results if you use the id and class attributes that are extensively used in the document. The material you want looks like this in the source (you can use any browser-based debugging tool to find it; I used the 'Web Inspector' in Safari); I have indented to make the structure more visible, and fixed some well-formedness errors in one of the a elements (missing whitespace between attribute-value pairs).

<div class="zc-tn" id="zc-tn-top">
  <div class="zc-tn-i">
    <a href="ZCGrid.do?fromTimeInMillis=1355781600000" 
       class="zc-tn-l" 
       title="Move the grid three hours earlier"></a>
    <div class="zc-tn-c">
      <span class="zc-tn-z" 
            title="Central Standard Time">CST</span>
      <div class="zc-tn-t">7:00 PM</div>
      <div class="zc-tn-t">7:30 PM</div>
      <div class="zc-tn-t">8:00 PM</div>
      <div class="zc-tn-t">8:30 PM</div>
      <div class="zc-tn-t">9:00 PM</div>
      <div class="zc-tn-t">9:30 PM</div>
    </div>
    <a href="ZCGrid.do?fromTimeInMillis=1355803200000" 
       class="zc-tn-r" 
       title="Advance the grid three hours"></a>
  </div>
</div>

A simple search verifies that the value zc-tn-top is indeed unique as an ID value in the document. Given that, a simple XPath expression to retrieve all the elements whose display is circled in your image is (assuming xhtml is bound to the XHTML namespace):

//xhtml:div[@id='zc-tn-top']//xhtml:div[@class='zc-tn-t']

It looks from your question as if your XPath evaluator is namespace-challenged or namespace-oblivious, so you may need to write this as

//div[@id='zc-tn-top']//div[@class='zc-tn-t']