Export Table from website and calculate distance from workplace

This topic contains 6 replies, has 3 voices, and was last updated by Profile photo of Tech Gismo Tech Gismo 1 year, 7 months ago.

  • Author
    Posts
  • #23279
    Profile photo of Tech Gismo
    Tech Gismo
    Participant

    I would very much like some help to get a table from a website into an XML document. I don't know how to approach this. but I have found some code from another PowerShell.org Q/A thread.

    1. The site is in Danish, but the code behind is in English, which is great. here is the website: http://basislaege.dk/Simple.asp?SortBy=timelinefull.TimelineNumber ASC

    2. The code I found is this:

    $webClient = new-object System.Net.WebClient
    $webClient.Headers.Add["user-agent", "PowerShell Script"]
    $output = $webClient.DownloadString["http://basislaege.dk/Simple.asp?SortBy=timelinefull.TimelineNumber ASC"]

    3. My end goal is this: One the headlines says "Uddannelsessted" = "Place of Education" roughly translated.

    I want to be able to see how far there is between my home address or Workplace to one the place mentioned under "Uddannelsessted".

    4. Here is some of the html information:

    I would very much like some help to get a table from a website into an XML document. I don't know how to approach this. but I have found some code from another PowerShell.org Q/A thread.

    1. The site is in Danish, but the code behind is in English, which is great. here is the website: http://basislaege.dk/Simple.asp?SortBy=timelinefull.TimelineNumber ASC

    2. The code I found is this:

    $webClient = new-object System.Net.WebClient
    $webClient.Headers.Add["user-agent", "PowerShell Script"]
    $output = $webClient.DownloadString["http://basislaege.dk/Simple.asp?SortBy=timelinefull.TimelineNumber ASC"]

    3. My end goal is this: One the headlines says "Uddannelsessted" = "Place of Education" roughly translated.

    I want to be able to see how far there is between my home address or Workplace to one the places mentioned under "Uddannelsessted". There are about 400 different options, so it would be really great if I with your help could get this to work.

    4. Here is some of the html information:

    
    

    Thanks in advance for the help 🙂

    Best regards

    TechGismo

  • #23280
    Profile photo of Don Jones
    Don Jones
    Keymaster

    You can use Invoke-WebRequest a bit more easily. What you'll get back is "sorta" XML, and depending on the website might in fact be XML (e.g., XHTML). You just need to parse through the document object model (DOM) to find your table. On client computers, Invoke-WebRequest returns a parsed object; check out its properties and see if any of that helps you get just the table you want.

    I've no idea how to go about calculating distance between places, though.

  • #23300
    Profile photo of Tech Gismo
    Tech Gismo
    Participant

    My thought on the distance calculation, was if you in some way could use Google maps in some way? But if adresses or Google maps could be seen as an object wouldn't that hepl? Without knowing how to do it, I have learned that if powershell can be forced to see something as an object, then you can get somewhere.

    Am i correct in this?

  • #23303
    Profile photo of Tech Gismo
    Tech Gismo
    Participant

    Hello Mr. Jones

    I would like to know if i'm on the right track here? I've got this so fare:

    $r = Invoke-WebRequest http://basislaege.dk/Simple.asp
    $r.Forms[0].
    Invoke-RestMethod http://basislaege.dk/Simple.asp -Body $r.Forms[0] | Out-File C:\PSscript\basis.txt

    I would like some to analyse what i am doing wrong? here is the powershell error msg:

    PS C:\Windows\system32> $r = Invoke-WebRequest http://basislaege.dk/Simple.asp
    $r.Forms[0].
    Invoke-RestMethod http://basislaege.dk/Simple.asp -Body $r.Forms[0] | Out-File C:\PSscript\basis.txt
    At line:3 char:7
    + Invoke-RestMethod http://basislaege.dk/Simple.asp -Body $r.Forms[0] | Out-File C ...
    +       ~~~~~~~~~~~
    Unexpected token '-RestMethod' in expression or statement.
    At line:3 char:19
    + Invoke-RestMethod http://basislaege.dk/Simple.asp -Body $r.Forms[0] | Out-File C ...
    +                   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Unexpected token 'http://basislaege.dk/Simple.asp' in expression or statement.
        + CategoryInfo          : ParserError: [:] [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : UnexpectedToken

    I've also tried:

    $r = Invoke-WebRequest http://basislaege.dk/Simple.asp
    $r.Forms[0].Name = "title1"
    $r.Forms[0].Name = "title2"
    Invoke-RestMethod http://basislaege.dk/Simple.asp -Body $r.Forms[0] | Out-File C:\PSscript\basis.txt

    But I just got the following errors:

    The property 'Name' cannot be found on this object. Verify that the property exists and can be set.
    At line:2 char:1
    + $r.Forms[0].Name = "title1"
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: [:] [], RuntimeException
        + FullyQualifiedErrorId : PropertyAssignmentException
     
    The property 'Name' cannot be found on this object. Verify that the property exists and can be set.
    At line:3 char:1
    + $r.Forms[0].Name = "title2"
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: [:] [], RuntimeException
        + FullyQualifiedErrorId : PropertyAssignmentException
     
    Invoke-RestMethod : A content-body with this verb-type can not be sent.

    (Sorry if this line doesn't make sence, I had to translate that from Danish to English.)

    At line:4 char:1
    + Invoke-RestMethod http://basislaege.dk/Simple.asp -Body $r.Forms[0] | Out-File C ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: [:] [Invoke-RestMethod], ProtocolViolationException
        + FullyQualifiedErrorId : System.Net.ProtocolViolationException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

    Thanks for all the help

  • #23394
    Profile photo of Tech Gismo
    Tech Gismo
    Participant

    I have puzzled around a bit with the code and now I finally found out to parse it to a csv file.

    $r = Invoke-WebRequest http://basislaege.dk/Simple.asp?show=all | Out-File C:\PSscript\teeest.csv

    This only gives me the format information used on the website, how do I get the actual content of the website to a csv file?

  • #23412
    Profile photo of Daniël
    Daniël
    Participant

    I don't think you actually want to move the contents of the website to a CSV file, your goal is to extract the 'Uddannelsessted' out of that page right?

    Looking at the HTML code, all the table rows containing the entries you're interested in look like this:

    [blockquote]
    (/tr)(TR id="6412" class="Line1")
    (TD align="center" class="ocp" style="border-top:#999999 1px solid;cursor:default;")354(/TD)
    (TD class="Line10")Nord.(/TD)
    (TD class="Line10")1. mar 15(/TD)
    (TD class="Line10" style="color:#000000;")Aalborg Universitetshospital(/TD)
    (TD class="Line10")Klinik Medicin, Nyremedicin(/TD)
    (TD class="Line10" style="background-color:#AB56D8;color:#ffffff;")Nefrologi(/TD)
    (/tr)(TR class="Line11")
    [/blockquote]

    So basically, you're looking for rows with class 'Line10'. Fortunately, we can filter on those with PowerShell:

    $Request = Invoke-WebRequest -Uri 'http://basislaege.dk/Simple.asp?show=all'
    $Line10 = ($Request.ParsedHtml).getElementsByTagName('TD') | Where-object{$_.ClassName -eq 'Line10'}
    

    Next, convert the items in the $Line10 variable to PowerShell objects. There are 5 rows in the table, so loop for each 5 items in $Line10.

    $i = 0
    
    Do
    {
        $Hash = @{
        Region = $Line10[$i].InnerText
        StartDato = $Line10[$i+1].InnerText
        Uddannelsessted = $Line10[$i+2].InnerText
        Afdeling = $Line10[$i+3].InnerText
        Speciale = $Line10[$i+4].InnerText
        }
    
        New-Object -TypeName PSObject -Property $Hash
    
        $i = $i + 5
    }
    Until($Line10.count -eq $i)
    

    Now you've got your items as PowerShell objects.

  • #24324
    Profile photo of Tech Gismo
    Tech Gismo
    Participant

    Thanks for the help Daniël I will use this.

You must be logged in to reply to this topic.