[Solved] Importing HTML Source?

Discuss the spreadsheet application

[Solved] Importing HTML Source?

Postby mokanman » Sat Feb 23, 2019 1:15 am

I have a spreadsheet with an external link to https://www.timeanddate.com/weather/@z-us-64746/ext which is once again working very well for me thanks to Villeroy. However, there is a column for wind direction that contains a graphic that always points north in the spreadsheet after downloading whereas the web page actually displays a graphic depicting the direction being forecasted. I doubt there is a way to convert that directly from that download, however, I'm wondering if there is a way to import the HTML source to parse it. I found, using Firefox, that I can view the source code which actually contains the information in degree increments (between 0 and 359). If I could somehow import that source then I could parse it based on the text layout and convert it to normal directional conventions (N,S,E,W). I found that the source contains the characters wd followed by a quote, then a colon, then the degree number followed by a comma. This should be easy to parse. I successfully copied the entire source manually from the Firefox source display into a new worksheet so it seems feasible. Does anyone know of a way to import HTML source into a spreadsheet.
Last edited by mokanman on Sun Feb 24, 2019 10:59 pm, edited 1 time in total.
Libreoffice 6.0.6.2 on Ubuntu 18.04
mokanman
 
Posts: 8
Joined: Fri Nov 16, 2018 9:41 am

Re: Importing HTML Source?

Postby Lupp » Sat Feb 23, 2019 3:17 am

The file is starting with
<!DOCTYPE html><!--
scripts and programs that download content transparent to the user are not allowed without permission
-->

Of course, I'm sure that my script (most simple Basic) is not subject to permission by anybody. It may be different, however, with the evaluation of the content. I'm not a lawyer.

My crude code did NOT try to analyse the content. It simply was
Code: Select all   Expand viewCollapse view
Sub Main
sheet = ThisComponent.Sheets(0)
rg = sheet.getCellRangeByName("A2:A10001")
da = rg.getDataArray
url = "https://www.timeanddate.com/weather/@z-us-64746/ext"
If FileExists(url) Then
  num = FreeFile
  Open url For Input As #num
  k = -1
  While NOT EoF(#num)
    k = k + 1
    Line Input #num, oneLine
    da(k)(0) = oneLine
  Wend
  rg.setDataArray(da)
End If
Close #num   
End Sub
Last edited by Lupp on Sat Feb 23, 2019 11:00 pm, edited 2 times in total.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Importing HTML Source?

Postby mokanman » Sat Feb 23, 2019 8:59 pm

I guess I need to start paying more attention to comments in HTML source code. I would have to agree, however, with your (humorous) interpretation of that vaguely and potentially mis-worded comment. Also, anyone using the external links capability of LibreOffice (and I'm sure many other available software) would never even know that comment was there unless they also happened to look at the source code. Anyway, it is good to know that your script is not subject to permission by anybody. Thanks for the reply and the helpful code!
Libreoffice 6.0.6.2 on Ubuntu 18.04
mokanman
 
Posts: 8
Joined: Fri Nov 16, 2018 9:41 am

Re: Importing HTML Source?

Postby mokanman » Sun Feb 24, 2019 10:57 pm

While I appreciate Lupp's helpful reply and will possibly use that knowledge for future endeavours I've actually switched my external link to a different web site that has all the information I need without requiring me to do any additional coding. I'm marking this issue "solved" as Lupp provided the information I requested and I have also pursued an alternative approach.
Libreoffice 6.0.6.2 on Ubuntu 18.04
mokanman
 
Posts: 8
Joined: Fri Nov 16, 2018 9:41 am


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 11 guests