Importing Data using legacy Web Connector in Excel

[Guy Hunkin]

Hi all,

I have a question related to usage scenarios of the legacy Import from Web connector in Excel.

 

Today the legacy Import from Web connector supports the following features, which I’d like to get your perspective on:

1. Importing the entire page. You have an option to select and import the entire page using a dedicated arrow.

2. Importing data from preformatted text (PRE). For example, if you try importing this dummy data, legacy Web Connector enables you to do this.

My question is how important these feature are? Do you find them useful? Are you familiar with someone who uses them?

Thanks in advance for your help!

Guy.

- Excel Team

[#2]

That's only my own experience - for many years used this feature quite seldom. These days Power Query connector to Web works fine for my purposes, even forgot about above legacy connector.

[#3]

Hi Guy,

Even though you only had one response up till now, I'd like to stress that that doesn't mean this feature isn't used much. I have a couple of customers that rely heavily on it. Thanks for asking though :)

[#4]

Thanks, Sergei.

[#5]

Got it, Jan Karel. Thanks for the feedback.

[#6]

My exerience lately has been trying to help someone with the legacy connector because the target web page changed. After a couple rounds of click-here, try-this, I ask if they are on 2016 or have Power Query on their machine and if yes, we try a new query and it almost always works better/faster/easier.

[#7]

I am a developer for an application which relies upon this for key reporting functionality.

It's only now, when trying to find out why it's broken, that I find out that this had been asked.

It's broken because the new wizard is no longer recognising that the http-basic authentication has succeeded and thinks there was a significant connection issue, causing it to simply error rather than import any data.

The discussion here: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/19310818... relates to this, but refers simply to the breaking of the tab separation recognition in the <pre> block. Today I've got the new wizard and Excel is not recognising that it has successfully authenticated with the web service.

 I can see from the web access logs and the application audit that the data was produced and supplied to the requesting client.

I would appreciate some help in diagnosing this problem. Ideally before we try to demonstrate it to customers at 9am EDT today, but ...

Is there someone that can help me?

Thanks

[#8]

Hi there,

Just to clarify - are you using the legacy Web wizard or Power Query one?

Guy

- Excel Team

[#9]

Guy,

Sorry it's taken a while to reply - I don't recall an email notifying me that you had queried and stumbled back to the community by other means.

We can use the legacy one for the http-basic authentication as that still works (when we happen to get it proferred by Excel, but that seems random, unpredictable and reducing in frequency).

I cannot get the new power query connector to recognise that it has successfully authenticated, I posted this early on the Excel User Voice discussion:

"Just updated to Version 1707 (Build 8326.2076 Click-to-Run)

In-house diagnosis shows that when accessed via a web browser, the service responds with and http/401 and then (after entering username/password) an http/200 and the data is shown in the browser.

When attempted in Excel, the access log on the web service shows nothing. The only evidence that an attempt has been made to connect to the web service is an update to some temporary files within the application used to manage the compilation and execution of the Java classes.

When I debug the application and present Excel with a URL it has not previously known, I see that Excel requests the page initially and correctly interprets the http/401 response, asking for credentials. 
On entering credentials, it then sends a request with a correct authorization header* three times. It then reports incorrectly that the credentials are invalid and whatever you change (username or password) will never return to the web service to request the page again until you restart Excel and try again.

I really don't know where to go from here. As far as I can tell from a web service provision point of view, the web service is behaving correctly. Why is Excel not recognising this?

* correct: identical in content to that passed by a browser which then receives the results"

You may also wish to look at the cumminity post at: https://techcommunity.microsoft.com/t5/Get-and-Transform-Data/Unable-to-Connect-to-Web-Source-With-C...

Thanks,

Tim

[#10]

Very important, the new 2016 method breaks a lot of web connections for me.  For example, I have a page that I cannot get to without entering a login.  Old way the page opens up I enter login and then check the box to grab the data I want.  If needed clean and transform is used to get rid of unused items.  I was looking for an answer to this issue which brought me here.  Please bring it back.

FYI- I have tried the new method by but no luck getting past login page.  It would be nice if I could get that feature to work but no luck yet.

[#11]

Hi there,

If you still want to use on of the legacy data import wizards, you can bring them back as described here.

Hope this helps.

Guy

- Excel Team

[#12]

Guy,

Thanks for the note on the legacy wizards. This appears to address my issues, but I will need to do some further testing.

Are there plans to remove the legacy wizard capability from a future update?

I was struggling with the comment regarding the legacy wizards being less secure. Could you elaborate, please?

Why can we not have Web.Page() capable of authenticating?

ref: user-voice

Thanks,

Tim

[#13]

Hi Tim,

There are no plans to remove the legacy wizards from Excel. They are just hidden from the ribbon, but you can bring them back if required.

Regarding the "secure" comment, then Power Query supportד various modern ways of authentication - Basic auth, OAuth, Azure Active Directory authentication and more. Some of those are not supported in legacy wizards.

Regarding Web.Page() authentication, then I assume you are talking about Form Authentication, right? Well, if this is the case then the best way to influence will be to vote for this idea on Excel or PowerBI User Voice. This helps וד to prioritize our resources.

Guy

- Excel Team

[#14]

re: Web.Page()
I mean http basic authentication - as that is what is used to do and it worked very nicely with the http-basic auth I implemented in our application.

I have some votes on the suggestion at: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/31900633...

Thanks,

Tim

[#15]

Power Query supports Basic authentication out-of-the-box. Am I missing something?

[#16]

Basic authentication is needed with web.page() to allow massaging of the URL programmatically in the xlsx file.

[#17]

This is important functionality, we leverage this across hundreds of customers.   The deprecation is especially frustrating for Mac users, is there a workaround for Mac.

[#18]

Guy,

In the post at: https://social.technet.microsoft.com/Forums/ie/en-US/f3099495-eba9-4600-85c5-131257564c82/get-data-f...

your Microsoft colleague (Jeff Reed) posts:

"The newer experience uses Power Query for retrieving the web page.  Specifically the From Web experience is using the M function Web.Page.  Web.Page uses IE to read the webpage, and IE does not give the ability to compartmentalize cookies, which could present a security risk of leaked cookies.  To prevent the security risk Basic Auth and OAuth are prohibited with Web.Page.

The team knows that this is painful and is actively working on a solution to provide a better experience."

To be able to continue to use the basic authentication and to be able to modify the URL programmatically in the .xlsx file, we will continue to use the legacy wizard.

Thanks,

Tim