Solved Scraping a table from Website (1 Viewer)

NauticalGent

NauticalGent

Ignore List Poster Boy
Local time
Today, 10:13
Joined
Apr 27, 2015
Messages
6,622
I have been able to see table information from a site that requires a password. I can inspect the table and see the columns and the data...but no matter what I try, I cannot extract the data.

Several methods I have found using Dr. Google have failed and about 4 hours of ChatGPT have gotten me a bunch of code that does not work. It even gave me code to loop through the elements to find ANY table and it returns a "No Tables Found" message - by design of course.

I KNOW there is a table but something is keeping me from getting there...

The good news is that the company has agreed to compensate me for my time - which I will pass along to whoever wants to get me over this hump. PM me if you are interested.
 
Hi John,

Your current experience with ChatGPT sounds ominously the same as many of my experiences involving miniZinc and Access VBE. Lots and lots of hallucinations and code samples that don't work, but promote methods/properties that don't exist. Nice to see I have company.;)
 
Just as an idea: would a workaround via Excel be possible?
Example: https://www.w3schools.com/html/html_tables.asp
Code: 
let
    Source = Web.BrowserContents("https://www.w3schools.com/html/html_tables.asp"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE[id='customers'] > * > TR > :nth-child(1)"}, {"Column2", "TABLE[id='customers'] > * > TR > :nth-child(2)"}, {"Column3", "TABLE[id='customers'] > * > TR > :nth-child(3)"}}, [RowSelector="TABLE[id='customers'] > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true])
in
    #"Promoted Headers"
 
I think @Edgar_ was helping another user getting data from a web page.
I cannot find the thread ATM though. :(
 
Gasman said:
I think @Edgar_ was helping another user getting data from a web page.
I cannot find the thread ATM though. :(
Click to expand...
As fate would have it, Edgar responded rather quickly so he and I will be getting after it sometime this morning. Once we (he) has cracked this nut, I will post the details here so others may benefit.
 
arnelgp said:
i think scraping is very easy as in my currency conversion?
Exchange Rate/ currency conversion | Access World Forums (access-programmers.co.uk).

scraping codes are "everywhere" on the net.
Click to expand...
Thanks Arnel, your code is what I tried first. But the issue is, all the code I have tried, including yours, fails to find a table on the site. Here is a HTML snippet from the page:
HTML: 
<div class="ignite-data-table-wrapper has-content">
            <table>
              <thead>
                
  <!---->

  <!---->

  <tr>
 
        <ukg-th aria-sort="none" sortable="true" data-test-requests-list-column="id" data-test-requests-list-sorting="request_number" class="sc-ukg-th-h sc-ukg-th-s data-cell ukg-sort hydrated ukg-data-table-col-0" tabindex="0" scope="col" role="columnheader"><!----><div class="column-container align-left sc-ukg-th sc-ukg-th-s">
    
        ID
      
  </div></ukg-th>

        <ukg-th aria-sort="none" sortable="true" data-test-requests-list-column="name" data-test-requests-list-sorting="name" class="sc-ukg-th-h sc-ukg-th-s data-cell ukg-sort hydrated ukg-data-table-col-1" tabindex="0" scope="col" role="columnheader"><!----><div class="column-container align-left sc-ukg-th sc-ukg-th-s">
    
        Subject
      
  </div></ukg-th>

        <ukg-th data-test-requests-list-column="category" class="sc-ukg-th-h sc-ukg-th-s data-cell hydrated ukg-data-table-col-2" tabindex="-1" scope="col" role="columnheader"><!----><div class="column-container align-left sc-ukg-th sc-ukg-th-s">
    
        Category
      
  </div></ukg-th>

        <ukg-th aria-sort="none" sortable="true" data-test-requests-list-column="employee" data-test-requests-list-sorting="requested_for" class="sc-ukg-th-h sc-ukg-th-s data-cell ukg-sort hydrated ukg-data-table-col-3" tabindex="0" scope="col" role="columnheader"><!----><div class="column-container align-left sc-ukg-th sc-ukg-th-s">
    
        Requested for
      
  </div></ukg-th>

        <ukg-th data-test-requests-list-column="assignees" class="sc-ukg-th-h sc-ukg-th-s data-cell hydrated ukg-data-table-col-4" tabindex="-1" scope="col" role="columnheader"><!----><div class="column-container align-left sc-ukg-th sc-ukg-th-s">
    
        Assignees
      
  </div></ukg-th>

        <ukg-th aria-sort="none" sortable="true" data-test-requests-list-column="creationDate" data-test-requests-list-sorting="created_at" class="sc-ukg-th-h sc-ukg-th-s data-cell ukg-sort hydrated ukg-data-table-col-5" tabindex="0" scope="col" role="columnheader"><!----><div class="column-container align-left sc-ukg-th sc-ukg-th-s">
    
        Created on
      
  </div></ukg-th>

        <ukg-th aria-sort="none" sortable="true" data-test-requests-list-column="dueDate" data-test-requests-list-sorting="due_date" class="sc-ukg-th-h sc-ukg-th-s data-cell ukg-sort hydrated ukg-data-table-col-6" tabindex="0" scope="col" role="columnheader"><!----><div class="column-container align-left sc-ukg-th sc-ukg-th-s">
    
        Due date
      
  </div></ukg-th>

        <ukg-th aria-sort="none" sortable="true" data-test-requests-list-column="priority" data-test-requests-list-sorting="priority" class="sc-ukg-th-h sc-ukg-th-s data-cell ukg-sort hydrated ukg-data-table-col-7" tabindex="0" scope="col" role="columnheader"><!----><div class="column-container align-left sc-ukg-th sc-ukg-th-s">
    
        Priority
      
  </div></ukg-th>

        <ukg-th aria-sort="none" sortable="true" data-test-requests-list-column="status" data-test-requests-list-sorting="status" class="sc-ukg-th-h sc-ukg-th-s data-cell ukg-sort ukg-is-sticky--right hydrated ukg-data-table-col-8 ukg-is-last-sticky" tabindex="0" scope="col" role="columnheader" style="right: 115.422px;"><!----><div class="column-container align-left sc-ukg-th sc-ukg-th-s">
    
        Status
      
  </div></ukg-th>


      <ukg-th sticky="right" class="sc-ukg-th-h sc-ukg-th-s data-cell ukg-is-sticky--right hydrated ukg-data-table-col-9" tabindex="-1" scope="col" role="columnheader" style="right: 0px;"><!----><div class="column-container align-left sc-ukg-th sc-ukg-th-s">
    
  </div></ukg-th>

 
</tr>

  <!---->

  <!---->

  <!---->

              </thead>
              <tbody>
                  
  <!---->

  <!---->

  <!---->

  <!---->

  <!---->

 
    <tr id="ember44-row-0" data-test-ignite-paginated-table-row-index="0" data-test-ignite-paginated-table-row="21688">
 
        <td data-test-requests-list-number="" class="ukg-data-table-col-0">
  <span>
    21688
  </span>
</td>

        <td class="ukg-data-table-col-1">
  <a id="ember45" class="ember-view" data-dd-name="Request list > Request name link (efaccc88-cf7c-4b7b-9c3b-b6f7058bb869)" data-dd-privacy="mask" data-test-requests-list-name="" href="/v2/requests/efaccc88-cf7c-4b7b-9c3b-b6f7058bb869">
    Payroll - Direct Deposit
  </a>

I KNOW there is a table there, but so far everything I have tried as failed to find it...
 
as you can "obviously" see.
there are:
ID, SUBJECT, CATEGORY, REQUESTED FOR, ASSIGNEE , ETC..
on that table.
you just parse the paired < /> bracket
and you can extract the info you need.
anyway, goodluck.
 
Yes, I can see they are there, it is getting Access (or any app that uses VBA) to see it too. I know there is a way, but I sure as hell do not know it.
 
Josef P. said:
Just as an idea: would a workaround via Excel be possible?
Click to expand...
Absolutely! The company just want to get clean data so they cans use Power BI to make reports. The folks for sold them the website did not provide an API and the .csv export feature is horrible.

Josef P. said:
Example: https://www.w3schools.com/html/html_tables.asp
Click to expand...
That is the most comprehensive tutorial on HTML I have ever seen. I have used W3Schools before and they never fail to impress. Just a quick glance through the pages explained a lot of the tags and what they mean. Appreciate you sharing it.
 
Some websites actively try to defeat scraping, hopefully this isn't the case.
 
Place the html file you sent me in the same folder as this db.

Then open Immediate Window (Ctrl+G) and type ?DoIt and hit Enter.

Should give you a start.
 

Attachments

Good morning folks,

From some of the responses here, PM's and even emails, it seems I have ruffled a few feathers. I really didn't understand where the ire was coming from until I had some time to think on it.

From what I have given you, all the code I have been provided DOES work. But, like is used to tell my customers while I was on active duty (Logistics/Supply): "I gave you what you asked for, not what you need."

When I tried to plug in my site, the code provided would either fail silently or error on various lines. What was even more curious to me was that EVERY bit of code, when processing the extracted HTML would only return 25 records when there are over 19k.

Since the site is password protected and uses two-tier security - in this case a one-time pin - giving everyone access was not possible. I was able to give one person the "keys to the kingdom" and he, with is very impressive knowledge of website structure, was able to figure out what the issue(s) are.

I will try to explain it here, but when I get it wrong, understand that I am doing trouble shooting by braille here and I am only making a vain attempt to explain what has been explained to me. I know I am going to muck this up, but what the hell...

I assumed that web-scrapping covered ALL methods of getting info from the web, which is why I used that term in the title. The site I am using is more a less a front-end to another site that accesses/stores the raw-data. While I was talking to Edgar, he kept using the term API which confused me because I was assured by the company that there were in fact no API's and that the company who developed the front-end site would not make one. No idea why, but they were quite clear that it was a no-go. Simply getting a connection to their server was also a non-starter since the ability to export the data was provided. In other words, IT made the data accessible; job done. However, the export is hideous and to "clean" the data takes days due to the amount of human interaction.

He corrected me and said these were REST API's - which was clear as dog-shit mud until I did a google search after he explained it to me. This explained why I was only getting the 25 records and why web-scraping would not work. To programmatically "click" the "Next" button to load the next 25 records, process them and repeat would have been painful.

He has found a way to leverage this REST API and get up to 100 records in JSON format at a time, but is still working on how to get a better product.

I hope this clears things up, if not, ask and I will try to explain it, or even better, Edgar will. I like learning new things, but this has me feeling 'purt near stupid...
 
I suspected you might need some pagination, hence my question to you via PM!

If you can hit the REST API's directly and not have to faff about with the HTML at all life is a lot simpler (and probably much easier w.r.t. the login/2fa process too!)
 
cheekybuddha said:
I suspected you might need some pagination, hence my question to you via PM!

If you can hit the REST API's directly and not have to faff about with the HTML at all life is a lot simpler (and probably much easier w.r.t. the login/2fa process too!)
Click to expand...
He came up with a bookmarklet process to bypass the login that is just simply brilliant!
 
You must log in or register to reply here.

Similar threads

E
Relationships between tables in access
2
Replies
35
Views
452
MajP
MajP
K
Teaching AI
Replies
11
Views
375
Isaac
Isaac
asteropi
Solved How to choose data from 2 different tables?
Replies
14
Views
186
Minty
Minty
Isaac
Please Recommend a Website Builder for Dummies = (me)
Replies
9
Views
1,323
Gasman
Gasman
A
Numerical ID vs Alphanumerical ID: practical advice needed on best way forward from where I am
2
Replies
23
Views
602
Cotswold
Cotswold

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom