Download multi page table to Excel.

**mdmackillop** · 01-31-2007, 03:51 AM

Not my area of expertise, so would someone be kind enough to write me some code to copy the table in 1 or more parts shown here and paste it into Excel. Each days report would be pasted below the previous one.
Thanks
Malcolm
http://ted.europa.eu/Exec?DataFlow=S...e=1&toconf=yes

Edit:
I've just come across a time-out problem. Maybe a bit more thought needed!

**stanl** · 02-10-2007, 03:59 AM

Originally Posted by mvidas

Still need help here?

Could you give a desired navigation? Which language to select for display; which section to scrape tables from, etc...

This way I can see if HTTP transfer and local parsing of tables is a viable solution (it would avoid any timeout issues with the site). Stan

**stanl** · 02-10-2007, 05:47 AM

You can avoid the timeout with an HTTP Post (I used the freeware xStandard http.dll since it includes Tidy to ensure well-formed html is returned. I was able to make post but the response file (attached)was not what I expected. I could navigate directly with the link below, but it comes up in French, not English [quelle merde]. However, you should be able to open the htm file I attached and navigate to the correct page. At that point you can run web queries for the tables you need, or write script code (I have a DHTML analyzer I compiled which can assist in this)

http://ted.europa.eu/Exec?Template=T...LL&StatLang=EN

**stanl** · 02-10-2007, 09:58 AM

Well, if this was an HTTPS site I would expect issues, but the design is either idiotic or brilliant. I've done something similar [web-scraping the yellow pages for address and phone info for all schools, churches, public offices by county/city in North Carolina]. The main point was handling tables with multiple pages.

I used xPath, gathered everything as a database table, then generated Excel workbooks as needed. Don't know what you expected for an answer, but I can probably help you through contracts[under business opportunities] for GB..... Stan

**stanl** · 02-10-2007, 03:36 PM

The url you used gets me nothing. Maybe I missed the point, but is the attached a table of interest; because if so it is multi-paged and I think requires building the data not just performing a web query. Are you needing the data in Excel formatted the same as the HTML? Stan

**stanl** · 02-11-2007, 05:40 AM

How about:
1. Navigate to Browse by Location (NUTS)
2. re-navigate to United Kingdom [all entries]
3. Parse multiple pages into recordset
4. Either copy recordset directly into Excel and use as basis for Pivot Table, or store/archive in Access and perform Jet 4.0 SELECT...INTO queries to create Excel sheets as needed.

Stan [sans login/pw]

**stanl** · 02-11-2007, 01:36 PM

Well (I explained more in private mail) I can get to here, viz. converting table data to csv as a memvar. So concatenating multiple pages, saving the memvar as a csv file and loading into excel is doable. Stan

P.S. meant to use | as delimiter, instaed of ,

**stanl** · 02-12-2007, 07:36 AM

Originally Posted by mdmackillop

Thanks Stan

maybe...maybe not... I have attached a | separated file which parsed all contracts for UK [324 as of last count]. I see why they call it (NUTS)

Anyway, if this is something you can work with in Excel, I think my script will work and avoid the Timeouts. I originally used HTTP transfer thinking the URL constructed a sessionid, but it seems [I tested 8 times in a row] that the IE Object will suffice.

1 caveat: I used Winbatch, since the task required parsing text, I'm not that jazzed w/the filesystem object, all the Dim's and Set's involved - however, the IE part is 99% compatible with VBA, so you can use your own parsing functions. If the csv works, I can set out the steps in 99% compatible pseudo-code. Stan

**stanl** · 02-13-2007, 07:11 AM

Good thing I ran it again (as the site moved from week 6 to week 7, and that threw a curve at final parsing). I've attached week 7's data as csv and Excel - notice what happens to some dates, which I assume is an easy fix for you.

If any of this looks doable for you, I'll go over the web-scraping 101 code, try not to bore you with things you already know... otherwise I'll fade away from this thread for fear of being annoying.

Stan

**stanl** · 02-16-2007, 07:09 AM

Originally Posted by mdmackillop

I'm just a bit short of time just now.
Malcolm

Well, like you said...low priority... the code below is the best I can do for brute force processing of your data. Since few have responded to this thread, I'm thinking there is no easy web-query or 10 lines of code. Usually, with web-scraping I like to run my scripts independent so I did my best to modify my 17 lines of script code as a sub. I'm sure it can be optimized, but I'd like to think at least I did the DHTML investigations to obtain the correct references. As in my last post, I get a file that imports to Excel [as I attached ion that post] Stan

Sub GetTed()
  'Reference MS Internet Controls
  Dim IE As InternetExplorer
  Set IE = New InternetExplorer
  'first naviagte to home page
   IE.Visible = True
   IE.Navigate "http://ted.europa.eu/"
 
  'Wait until page is loaded. not recommended, probably need to
  'exit after 30 seconds, I'd write it as a separate function
  Do Until IE.ReadyState = READYSTATE_COMPLETE: Loop
  'set up a var go which assumes failure for all navigations
  go=0
  'make sure it's in English - or choose whatever language by
  'specifying part of the innertext
  For i= 0 To IE.Document.GetElementsByTagName("A").length-1
     s= IE.Document.GetElementsByTagName("A").Item(i)
     If s.GetAttribute("innerText")<>"" AND Instr(s.InnerText,"Official Journal")  Then
    s.click()
    go=1
      Exit For
   End If
  Next
 
  If go=0 Then 
      goto end
  End If
  Do Until IE.ReadyState = READYSTATE_COMPLETE: Loop
  'Now move to NUTS
  go=0
  For i= 0 To IE.Document.GetElementsByTagName("A").length-1
     s= IE.Document.GetElementsByTagName("A").Item(i)
     If s.GetAttribute("innerText")<>"" AND Instr(s.InnerText,"(NUTS)")  Then
        s.click()
      go=1
      Exit For
     End If
  Next
  End If
  If go=0 Then 
      goto end
  End If
  Do Until IE.ReadyState = READYSTATE_COMPLETE: Loop
  go=0
  'this is the tricky part, the next page has links to tables for all countries
  'but does not include an innertext to test with, I noticed that GB suddenly becomes
  'UK - which is the last in the list and [hoprefully] always -10 from the last
  'anchor
  n = oIE.Document.GetElementsByTagName("A").length-10
  If n>0 Then 
     oIE.Document.GetElementsByTagName("A").Item(n).click
     go=1
  Else
     goto end
  End If
 'bring First Table
  Do Until IE.ReadyState = READYSTATE_COMPLETE: Loop
  go=0
  cTable = "Ref,Details,Country,Start,End" & vbcrlf
  If IE.Document.Body.GetElementsByTagName("TABLE").Item(39)
     cTable = cTable & gettable(IE.Document.Body.GetElementsByTagName("TABLE").Item(39).InnerText)
   go=1
   x=2
   'loop through subsequent tables if they exist
   While go=1
      n=oIE.Document.GetElementsByTagName("A").length-1
        For i= 0 To n
        If oIE.Document.GetElementsByTagName("A").Item(i) Then
           s= oIE.Document.GetElementsByTagName("A").Item(i)
         If s.GetAttribute("innerText")<>"" AND s.InnerText=x Then
              s.click()
        Do Until IE.ReadyState = READYSTATE_COMPLETE: Loop
        cTable = cTable & gettable(IE.Document.Body.GetElementsByTagName("TABLE").Item(39).InnerText)
      x=x+1
           Else
        go=0
           End if
        End if
   Loop
  End If
  'at this point you should have a csv memvar which you can outpout to a file or
  'position back into a worksheet... your choice
:end
Set IE = Nothing
End Sub
Public Function gettable(c AS String)
cTxt = cTxt=Mid(c,Instr(c,"Deadline",8))
cTxt=Replace(cTxt,",",";")
cTxt=Replace(cTxt,"  ",",")
cTxt=Replace(cTxt,",,",",")
cTxt=Replace(cTxt,", ",",")
Return cTxt
End Function

**stanl** · 02-18-2007, 09:21 AM

I'm sure once you get this ironed out, future web-scrapes will be a breeze. I appreciate your patience with the way I kludged my original script. I have a feeling a careful use of execscript() can short-circuit some of the page-2-page navigation and looping. I have a DHTML analyzer exe I'm in the process of upgrading. You select the objects you want interrogated for a particular page, and it breaks them down into an output text file. Stan

Thread: Download multi page table to Excel.

Thread Tools

Display

Download multi page table to Excel.

Posting Permissions