CODEWITHSUNDEEP
excelasp.net-web-apiodatafiddlerpowerquery
Venkataraman R
Venkataraman R

Reputation: 12959

Excel data pull from External Source odata feed does not work

We have got a ODATA feed web API, which is a data source for our Excel report. This Odata feed, we are able to pull successfully, when it is hosted local & physical server. When the same odata feed web API is hosted in cloud environment we are facing connection timeout issue.

Unable to Connect
We encountered an error when trying to connect
Details: "OData: Request failed: Unable to connect to the remote server"

The same odata feed in cloud is accessible when we use office data connection(odc) in the excel file. 

<html xmlns:o="urn:schemas-microsoft-com:office:office"
                            xmlns="http://www.w3.org/TR/REC-html40">

                            <head>
                            <meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
                            <meta name=ProgId content=ODC.TableCollection>
                            <meta name=SourceType content=DATAFEED>
                            <title>OurApplication</title>
                            <xml id=docprops><o:DocumentProperties
                              xmlns:o="urn:schemas-microsoft-com:office:office"
                              xmlns="http://www.w3.org/TR/REC-html40">
                              <o:Name>OurApplication</o:Name>
                             </o:DocumentProperties>
                            </xml><xml id=msodc><odc:OfficeDataConnection
                              xmlns:odc="urn:schemas-microsoft-com:office:odc"
                              xmlns="http://www.w3.org/TR/REC-html40">
                              <odc:Connection odc:Type="DATAFEED">
                               <odc:ConnectionString>
Data Source=&quot;https://OurApplication.com/odata/OurTable&quot;
</odc:ConnectionString>
                               <odc:CommandType>TableCollection</odc:CommandType>
                               <odc:CommandText>&quot;OurTable&quot;</odc:CommandText>
                              </odc:Connection>
                             </odc:OfficeDataConnection>
                            </xml>
                            <style>
                            </style> 
                            </head> 
                            </html>

But, it is not accessible when we try to do bring using Get External Data as given below:

Getting External Data in Excel

We are getting below error in fiddler.

[Fiddler] The connection to 'OurApplication.com' failed.
Error: TimedOut (0x274c).
System.Net.Sockets.SocketException A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond OurApplicationIPAddress:OurApplicationPortNumber

When I try to use M language for OData source, I am getting error like below:

= OData.Feed ("OurApplication/odata/OurTable"
    ,null, [Timeout = #duration(0,2,0,0)])

DataSource.Error: OData: Request failed: The remote server returned an error: (502) Bad Gateway. (Fiddler - Connection Failed) Details: DataSourceKind=OData DataSourcePath=http://Ourapplication:portnumber/odata/$metadata Url=http://courapplication/odata/$metadata

Please help us in resolving how to connect to ODATA feed in Powerquery in excel.

Upvotes: 0

Views: 1556

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12959

In our case, basic authentication was not working for the PaaS (we were using cloud foundary based PaaS hosting) hosted webAPI. We created one more endpoint with anonymous authentication and used that endpoint in our excel connection to get data using OData.

Note: In our case, as the data was not sensitive and moreover, being internal application, we went ahead with anonymous authentication.

Upvotes: 0

Aleksei Zhigulin
Aleksei Zhigulin

Reputation: 1634

Try to specify Timeout parameter (2 hours, for example):

= OData.Feed("http://link", null, [Timeout = #duration(0,2,0,0)])

Upvotes: 1

Related Questions