As we intending to move soon to a version of E10 that supports REST I thought I would trial it with Excel.
As far as I know it is working as I have done some simple tests using Postman and I am able to retrieve data.
However when I try and add a OData service in Excel I get the error “Microsoft Excel can’t access the data feed server because the URL the feed uses isn’t valid”
As per the REST manual I have added the tag
and for testing I have also added
What’s the URL you are using?
Do you have a valid SSL Certificate?
Oh dear. the xml tags got chopped out of my message
anyway - in web.config i have set
EnforceApiKeyForRestApiV2 = false
and for testing
AllowInsecureRestApi = true
URLs work fine in Postman
http:////api/v2/odata//BaqSvc/COM-CustContacts
switch to V1 URL and try again. V2 requires keys and doesn’t jive well with Excel
Not sure if those web config flags will apply even in Excel.
I have tried v1 URLs as well, but get the same error response from Excel. Again they work fine in Postman.
AllowInsecureRestApi = true definitely is honoured. Without it I get a certificate error.
Next step will be to trying some tracing…
I thought that Excel 365 no longer worked with v1 and you must use the Legacy Plugin in Excel. Thats where v2 and ODATA 4 came in they worked with the new connector.
v2 is for 10.2.400 - Since you mentioned it on top I assumed you were on 10.2.400.
v1 does not work with the New Adapter to my understanding or atleast doesnt for me anymore since a Office 365 Update I get a 404 - but Legacy is fine.
I had to use v2 and also in addition add my server to the Trusted Root Certificates.
Epicor States:
The format of Business Activity Queries (BAQs) data requests has changed to include /Data in the request URL. IMPORTANT: This is critical for enabling REST API compatibility with Microsoft® Excel® 2016/365. Refer to the BAQ Feeds topic of this guide for detailed information and examples (Display Data in OData Applications Display in Excel Table).
From MS I had to use Legacy for old v1
3 The new OData connector available in Power BI and Excel 2016 reads the service metadata and caches it regardless of the HTTP header specifying it should not be cached. I have a case where new entity sets are added dynamically and metadata does change. The user has to clear the cache after each data retrieval otherwise only the entity sets available during the first call are available.
The workaround is to use the legacy data importer which I hope MS will continue to support in future Excel releases.
Tag: @Olga
By default, Postman will ignore SSL verification. Check your settings to see if that is enabled.
We don’t have O365 - Our Office 2016 is on premise, volume licensed.
I have disabled the requirement for SSL to remove that issue. The requests are http://
Time to break out wireshark i think…
Ok just know they said that both 2016/365 are affected, atleast mine has been for about 6 months now ever since an Office Update made it on-prem 
But if your certain its not that, I agree #Wireshark or… maybe .NET Fiddler
If you have access to IIS logs, you can check if query reaches Epicor at all. And if yes, what exactl URL is and what HTTP error response you get.
Also you can switch on REST Api trace in the log and see REST call in Epicor log.
Install PowerPivot_for_Excel_amd64 for Office 64 bit and PowerPivot_for_Excel_x86 for office 32 bit
Hi @richardh
we have just upgraded to ERP10.2.400.8 installed on an (On Prem server), -still under testing-, i had similar issue making a REST v1 call from client 2016 Excel, export Epicor Certificate from the new server to the client PC solved it, still have not solved the Rest V2 issue especially with BAQ, both REST versions are working fine when making the call using web browsers (internal domain).
Another morning and a clear(er) head.
First off I realised I had been trying the “Import” OData feed rather than “Query” OData feed option in Excel.
So I tried this instead - hmmmm - different error - still doesnt work.
As per Olga’s suggestion above I have a little look in the IIS log. And can see a 302 redirection to a login page, but I am not receiving a dialogue in Excel to enter credentials.
A little digging around in Excel and I found “Data Source Settings” in the “New Query” menu on the “Data” ribbon.
Within there were some of the URIs I had been trying and sure enough the cache credentials were set to ‘Anonymous’. Changing them to known epicor logons and eureka! data is returned for both v1 and v2 REST calls.
is it possible to share some print screens for v2 data source settings
disable https -
< add key=“AllowInsecureRestApi” value=“True” />
disable app key -
< add key=“EnforceApiKeyForRestApiV2” value=“false” />
By natively supporting OData, Epicor ERP web services allow for easy consumption from tools that leverage the
protocol. You can have a refreshable spreadsheet against a live data source. Build pivot tables, charts against the
16 Epicor ERP | 10.2.400
Display Data in OData Applications Epicor REST Services v.2
feed for quick reports. Do the following to use REST services to pull Epicor data within an Excel spreadsheet. You
can then format this live data display as you need.
Note OData v4 is supported by MS Excel® 2016 or higher.
Important To be able to get data from a Service/BAQ feed into Excel using REST API v.2, you must:
• exclude the api-key parameter from your requests, and
• disable the default API Key requirement for REST requests by setting the value of the
EnforceApiKeyForRestApiV2 tag in the appSettings section of the system configuration file to false.
tried that mate, v2 still give me errors, if you could apply it in your environment with some screenshots, i will be very appreciated
On the Epicor side i changed added the 2 tags in web.config
EnforceApiKeyForRestApiV2 = false
AllowInsecureRestApi = true
On the Excel side, for the query I edited the credentials after the initial request threw an error.
For example I have tried the sample from the REST setup guide with my own site settings substituted in.
http://epicorapp01/E102400LIVE/api/v2/odata/NCPLTD/BaqSvc/COM-CustContacts/Data
