Excel question - importing data from a web app

[JenniC]

Excel question - importing data from a web app

To import a table from a web page and put it into a spreadsheet file, there is an excellent script posted at http://www.biterscripting.com/SS_WebPageToCSV.html . It imports n'th table from a web page (a web page may have more than one table) and puts it in a .csv file (Comma separated values). The good thing is that it strips off html and other tags, formatting, etc. along the way. Also, it will handle nested tables. So you don't have to do anything extra.

I use it all the time. To use, you can download biterscripting free at http://www.biterscripting.com . Install all the sample scripts including the WebPageToCSV script with the following command.

script "http://www.biterscripting.com/Download/SS_AllSamples.txt"

Should complete pretty fast. Call the WebPageToCSV script as below.

script "SS_WebPageToCSV.txt" page("http://www.xxx.yyy/mmm.nnn") number(5)

The above will import 5th table from page http://www.xxx.yyy/mmm.nnn .

One thing, if you add more functionality to their sample scripts, please post your improved scripts for the rest of us. Thanks in advance.

Jenni

[NickFitz]

This looks useful for my purposes so may have a bash. Just need to separate entire file from above into lines. Only prob is not all website source is formatted with line breaks and some tags can have minor content so could be messy.

Bored, if you want to mail content or give me url and tell me which bits you want to extract I can see if works with yours.

If the page is well-formed XML (which includes XHTML) then the responseXML property of XHR will contain the document parsed into a DOM tree.

If the response is not well-formed, it won't. In that case, you may be better off reverting to the webbrowser control, which will allow you to traverse the HTML DOM via the document property.

[bored]

Update, I decided not to bother and redid the spreadsheet in Mathematica as I would have to do that eventually anyway.

[xoggoth]

This looks useful for my purposes so may have a bash. Just need to separate entire file from above into lines. Only prob is not all website source is formatted with line breaks and some tags can have minor content so could be messy.

Bored, if you want to mail content or give me url and tell me which bits you want to extract I can see if works with yours.

[Spacecadet]

Not sure if this is helpful, but in IE you can interact with Excel from a web page using Javascript... I assume thrugh an ActiveX object.

I've done that before when building web apps and it's not too difficult, you can even do some cell formatting.

Better option though is to provide a - "open this in excel link" which opens the data as an excel file using the meta tags to specify the application

Still, neither of these is the solution that bored is looking for

IMO - a far better solution is to grab the data from the website on a regular basis, store it in a database application or a formatted plain text file then access the database or text file from Excel.
Sounds like more work, but it will be a lot easier to manage in the future and avoids having to use VBA

[d000hg]

Not sure if this is helpful, but in IE you can interact with Excel from a web page using Javascript... I assume thrugh an ActiveX object.

[NickFitz]

The data in question is a 1MB CSV file and growing, downloading it via XmlHttpRequest and then parsing it is just not worth it I guess since the wget solution will work fine. I think might just not bother with it at all and redo the spreadsheet using something more appropriate than Excel.

You might be able to work something out using XHR and then using ADO to treat the CSV as an ODBC data source.

Actually doing that is left as an exercise for the reader

[bored]

Thanks.

I've just tried to use Get External Data from Text, Get External Data from Web and some other options but they all seem flawed. The first one prompts me for the filename every time I hit Refresh All, even though the filename is saved in the properties of the connection. The second one can only read HTML files and not XML files, how dumb is that. No option to get CSV files from the web either. Microsoft , guess the whole web services thing just slipped by them.

The data in question is a 1MB CSV file and growing, downloading it via XmlHttpRequest and then parsing it is just not worth it I guess since the wget solution will work fine. I think might just not bother with it at all and redo the spreadsheet using something more appropriate than Excel.

[xoggoth]

That's neat! Never 'eard of it but I'm a dinosaur. That code didn't quite work but there's a ref here.

http://scriptorium.serve-it.nl/view.php?sid=40

Cut it down a bit and it works!

Private Sub CommandButton1_Click()
Dim sURL As String, sHTML As String
Dim oHttp As Object
sURL = "http://www.bondriver.co.uk"
On Error Resume Next
Set oHttp = CreateObject("MSXML2.XMLHTTP")
If Err.Number <> 0 Then
Set oHttp = CreateObject("MSXML.XMLHTTPRequest")
MsgBox "Error 0 has occured while creating a MSXML.XMLHTTPRequest object"
End If
If oHttp Is Nothing Then
MsgBox "For some reason I wasn't able to make a MSXML2.XMLHTTP object"
Exit Sub
End If
oHttp.Open "GET", sURL, False
oHttp.Send
sHTML = oHttp.responseText
Range("C10").Value = sHTML
End Sub

[NickFitz]

Rather than using the webbrowser control, which is basically firing up a whole instance of Internet Explorer to retrieve a plain text file, it would make more sense to use MSXML.XmlHttp, usually known as XmlHttpRequest.

You can probably get away with using it synchronously, if you don't mind locking up if the web site is down or otherwise unreachable:

Dim xhr As Object
Dim url As String, csvText As String

url = "http://example.com/data.csv"
Set xhr = MSXML2.XmlHttp
xhr.Open "GET", url, False
xhr.Send ""
' should really check that response status=200 here
csvText = xhr.responseText

It's years since I've used VBScript/VBA, so don't be overly surprised if there are syntax errors in there

[xoggoth]

Another macro answer, but this is really easy, took about 10 secs to check it out in outline, stick a webbrowser on sheet and copy contents to the sheet.

Macro is:

Private Sub Worksheet_Activate()
WebBrowser2.Navigate "http://www.bondriver.co.uk"
a = WebBrowser2.Document.Body.innerHTML
Range("G8") = a
End Sub

When you go to the sheet from another, G8 now contains all the contents of the body part of the file. I expect you can extract it from that.works in 2000 anyway. You can use innerText to get just the visible text without all the HTML tags.

I think you can get individual table contents in similar way if you know the ID. You won't find any documentation for browser in vba, look on net for VB6 webbrowser, is same object.

[bored]

Thanks, that's a nice idea, I tried importing data directly via a URL with various options but it never worked well. I can get the web app to generate a .csv file, download it using wget (same thing as url2file) from a macro and then use an ODBC datasource to import the data. Seems a bit too complicated though, there must be a better way.

[xoggoth]

Not using macros, dunno but if nobody has a non macro solutio I would suggest a freeware file ripper thing URL2FILE.EXE that can be called from a macro. More details if you need it. Works well.

[bored]

A question for Excel experts:

What is the best way to dynamically import external data (1-2 tables) from a web app into Excel 2007? If possible I'd like to avoid macros (i.e. just use External Data/Refresh All).

Also, what is a good way to do that if the table is generated by a command-line app? Currently I write the table into a .CSV file then import it via an ODBC .csv datasource. It takes a while to set it up though, is there any better way?