Get Data from Web page with login / credentials

[Miro H]

Hello,

I have a problem getting data from company web page that requires login. Data is in the form of the table. I would like to Get the data and to be able to refresh the Excel table data with "Refresh all" when I need it.

The main hyperlink is in this form:

https://linktowebpage:8443/ords/f?p=101:1248

When I click on it the link changes as follows and I am presented with login page:

https://linktowebpage:8443/ords/f?p=101:LOGIN::::::

When I successfully login link changes and that is the page where table is located:

https://inktowebpage:8443/ords/f?p=101:1248:2148084489072:::::

(llast part is session number which changes with every login. Session lasts 30 min)

1. If I use Get Data - From other sources - From Web
enter hyperlink https://linktowebpage:8443/ords/f?p=101:1248
I am able only to reach login page. How to tell Excel to use credentials to login to page?

2. If I use Get Data - Legacy wizzard - From Web (Legacy)
enter hyperlink https://linktowebpage:8443/ords/f?p=101:1248
I manually enter password on Web wizzard
I am able to reach the table and import it in Excel.
Problem is that full link is saved in Excel connection including session part of the link. As session lasts only 30min, after that new login is required and session number changes.
Here, again, I don't know how to input username and password into Excel to login automatically and not be bound with fixed session number.

Does anyone have some advice?

 

[jdellasala]

I've encountered the same problem. No matter how many times you change the authentication to a Domain, the connection never works. My solution was to print the page to a PDF and then import that.
It's worth noting that even though I have a full license for Acrobat, the PDF it produced was unusable. Luckily, I'm using a Chrome browser (Brave), and that handled the page a LOT better, but I still had to do a lot of tweaking to get it to work.
For starters, the number of Tables and their Name varied. I surmounted that problem by adding a column that had the number of rows for each table:

Table.AddColumn(FilteredTablesOnly, "Rows", each Table.RowCount([Data]), type number)

The one that I needed always had more than 15 rows, so I was then able to filter down to only one table by filtering that column for values greater than 15.
Next, the headers were inconsistent, sometimes being in 3 or 2 or just 1 row. I overcame that by replacing all line feeds #(lf) with a space, and then transposing the entire table and then merging the column based on the value in the first row of the "next" column

= if TransposedTable0{0}[Column5] = "AAP" or TransposedTable0{0}[Column4] = "AES" then
        Table.CombineColumns(TransposedTable0,{"Column1", "Column2", "Column3", "Column4"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
    else if TransposedTable0{0}[Column4] = "AAP" or TransposedTable0{0}[Column4] = "AES" then
        Table.CombineColumns(TransposedTable0,{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
    else if TransposedTable0{0}[Column3] = "AAP" or TransposedTable0{0}[Column4] = "AES" then
        Table.CombineColumns(TransposedTable0,{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
    else Table.RenameColumns(TransposedTable0,{{"Column1", "Merged"}})

By working backwards from the worst situation (merging 4 columns) to the best (no actual merge needed, but a rename of the column was), that hurdle was jumped.
After some more cleanup on columns that were to become row headers and transposing the table back to its original orientation, and tweaking that, I was able to produce the table I needed.
It took time to work all of that out because of the inconsistency of the PDF, but after a week or two of tweaking - mostly for that Merge step, it's been working perfectly for months. Another CSV file is merged in, and all queries are set to Load on opening.
That said, there is one other option that does not work with Excel. Power BI's Web connector is much more robust, however I don't think it will get around an Authentication problem, or a web page just being nasty and not loading. Ironically, yesterday I tried to load the Microsoft Support page About the Stocks financial data sources because I wanted to pull in the table of Exchanges
, and I was unable to get Excel to connect to it no matter how I tried.

Hope that helps.

 

[Miro H]

I've encountered the same problem. No matter how many times you change the authentication to a Domain, the connection never works. My solution was to print the page to a PDF and then import that.

If I understand correctly you have to manually print the page to a PDF? Is this correct?
I have the option to download the report, so I don't have to print it to PDF. But my problem is that I have to manually open the web page, login, download the report and import it to Excel. It is huge waste of time for me and I would like to be able to just Refresh the link.
I don't know if Getting data from Web with credentials has some general problem or it is something with internal security and server settings...

 

[jdellasala]

If I understand correctly you have to manually print the page to a PDF? Is this correct?
I have the option to download the report, so I don't have to print it to PDF. But my problem is that I have to manually open the web page, login, download the report and import it to Excel. It is huge waste of time for me and I would like to be able to just Refresh the link.
I don't know if Getting data from Web with credentials has some general problem or it is something with internal security and server settings...

If what you're clicking on is a direct link to the file and the filename never changes or can be constructed as in a filename that uses date information, you can use that link as the source of a Web query. Once the Web Query sees it's a PDF, it will automatically use that function to bring the file in.
That said, I have another similar situation and have been unable to automate it. You'd need something like Power Automate or some similar way to get the click done!
BTW, update your profile to show what version of Excel and what OS you're using, otherwise you might get an unusable answer or one that doesn't use newer functions.