Excel Power Query talking to NAR odata/atom feed; a first experiment


Now that we can issue basic http requests to the NAR webapi server, a set of node.js packages and setup, we configure excel power query to talk to the same server, modified slightly to allow a proxy to intercept the communications between excel and webapi server.

First, we host the server on the me, domain (rather than localhost), amending host file.  Then we alter excel so it uses the reverse proxy, listening on a particular port.

image

 

The latter step is achieved by adding a Microsoft.Mashup.Container.exe.config  fgile in C:\Program Files (x86)\Microsoft Power Query for Excel\bin

 

<?xml version=”1.0″ encoding=”utf-8″ ?>
configuration>
<system.net>
<defaultProxy>
  <proxy autoDetect=”false” bypassonlocal=”false” proxyaddress=”http://127.0.0.1:8888″ usesystemdefault=”false” />
</defaultProxy>
</system.net>
</configuration>

 

Of course, the windows system hosts file exposes me, for lookback adaptor

 

::1             localhost me

 

the webapi server itself is instructed next to no longer listen on localhost but on me:

 

image

listener, changed to host = me

 

The webapi server does not seem to have the logic to issue authorization challenges, should there by missing authorization headers. So, we pre-configure excel with basic credentials:

(admin/admin).

image

 

When we have excel talk to = OData.Feed(http://me:42999/DataSystem.svc/?DataSystem), we get

image

The fiddler proxy, spying on the communications now, has two sessions:

image

excel issues two http requests, with different accept headers:

Accept: application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.8,text/plain;q=0.8 REQUEST
Accept: application/json;odata=minimalmetadata;q=1.0,application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.8,text/plain;q=0.8 REQUEST

 

We list the messages here:

#1 : Accept: application/json;odata=minimalmetadata;q=1.0,application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.8,text/plain;q=0.8 REQUEST

GET http://me:42999/DataSystem.svc/?DataSystem HTTP/1.1
User-Agent: Microsoft.Data.Mashup (http://go.microsoft.com/fwlink/?LinkID=304225)
MaxDataServiceVersion: 3.0
Accept: application/json;odata=minimalmetadata;q=1.0,application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.8,text/plain;q=0.8
Authorization: Basic YWRtaW46YWRtaW4=
Accept-Encoding: gzip, deflate
Host: me:42999
Connection: Keep-Alive

WITH RESPONSE

HTTP/1.1 200 OK
content-type: application/xml;charset=UTF-8
Vary: Accept-Encoding
Date: Wed, 03 Sep 2014 00:45:49 GMT
Connection: keep-alive
Content-Length: 7970

<?xml version=”1.0″ encoding=”utf-8″?>
<feed xml:base=”http://me:42999/DataSystem.svc/” xmlns=”http://www.w3.org/2005/Atom” xmlns:d=”http://schemas.microsoft.com/ado/2007/08/dataservices” xmlns:m=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata” xmlns:georss=”http://www.georss.org/georss” xmlns:gml=”http://www.opengis.net/gml”>
<id>http://me:42999/DataSystem.svc</id>
<title type=”text”>DataSystem</title>
<updated>2014-09-01T02:08:32.000Z</updated>
<link rel=”self” title=”DataSystem” href=”DataSystem” />
<entry>
  <id>http://me:42999/DataSystem.svc/DataSystem(‘RESO API Server’)</id>
  <category term=”RESO.OData.Transport.DataSystem” scheme=”http://schemas.microsoft.com/ado/2007/08/dataservices/scheme” />
  <link rel=”edit” title=”DataSystem” href=”DataSystem(‘RESO API Server’)” />
  <title>Data Services for RESO API Server</title>
  <updated>2014-09-01T02:08:32.000Z</updated>
  <author>
   <name>Center for REALTOR Technology</name>
  </author>
  <content type=”application/xml”>
   <m:properties>
    <d:Name>RESO API Server</d:Name>
    <d:ServiceURI>http://me:42999/DataSystem.svc</d:ServiceURI>
    <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:45:47.726Z</d:DateTimeStamp>
    <d:TransportVersion>0.9</d:TransportVersion>
    <d:Resources m:type=”Collection(RESO.OData.Transport.Resource)”>
    <d:element>
     <d:Name>Property</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Property Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>Member</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Member Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>Office</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Office Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>Contact</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Contact Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>Media</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Media Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>History</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard History Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>SavedSearch</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard SavedSearch Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>OpenHouse</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard OpenHouse Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>Green</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Green Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>Room</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Room Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>UnitType</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard UnitType Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>OtherPhone</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard OtherPhone Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>SocialMedia</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard SocialMedia Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>OfficeUrlType</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard OfficeUrlType Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>UserDefinedField</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard UserDefinedField Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    </d:Resources>
    <d:ID>RESO API Server</d:ID>
   </m:properties>
  </content>
</entry>
</feed>

 

# Accept: application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.8,text/plain;q=0.8 REQUEST

 

GET http://me:42999/DataSystem.svc/?DataSystem HTTP/1.1
User-Agent: Microsoft.Data.Mashup (http://go.microsoft.com/fwlink/?LinkID=304225)
MaxDataServiceVersion: 3.0
Accept: application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.8,text/plain;q=0.8
Authorization: Basic YWRtaW46YWRtaW4=
Accept-Encoding: gzip, deflate
Host: me:42999

 

WITH RESPONSE

 

HTTP/1.1 200 OK
content-type: application/xml;charset=UTF-8
Vary: Accept-Encoding
Date: Wed, 03 Sep 2014 00:45:49 GMT
Connection: keep-alive
Content-Length: 7970

<?xml version=”1.0″ encoding=”utf-8″?>
<feed xml:base=”http://me:42999/DataSystem.svc/” xmlns=”http://www.w3.org/2005/Atom” xmlns:d=”http://schemas.microsoft.com/ado/2007/08/dataservices” xmlns:m=”http://schemas.microsoft.com/ado/2007/08/dataservices/metadata” xmlns:georss=”http://www.georss.org/georss” xmlns:gml=”http://www.opengis.net/gml”>
<id>http://me:42999/DataSystem.svc</id>
<title type=”text”>DataSystem</title>
<updated>2014-09-01T02:08:32.000Z</updated>
<link rel=”self” title=”DataSystem” href=”DataSystem” />
<entry>
  <id>http://me:42999/DataSystem.svc/DataSystem(‘RESO API Server’)</id>
  <category term=”RESO.OData.Transport.DataSystem” scheme=”http://schemas.microsoft.com/ado/2007/08/dataservices/scheme” />
  <link rel=”edit” title=”DataSystem” href=”DataSystem(‘RESO API Server’)” />
  <title>Data Services for RESO API Server</title>
  <updated>2014-09-01T02:08:32.000Z</updated>
  <author>
   <name>Center for REALTOR Technology</name>
  </author>
  <content type=”application/xml”>
   <m:properties>
    <d:Name>RESO API Server</d:Name>
    <d:ServiceURI>http://me:42999/DataSystem.svc</d:ServiceURI>
    <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:45:52.836Z</d:DateTimeStamp>
    <d:TransportVersion>0.9</d:TransportVersion>
    <d:Resources m:type=”Collection(RESO.OData.Transport.Resource)”>
    <d:element>
     <d:Name>Property</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Property Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>Member</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Member Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>Office</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Office Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>Contact</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Contact Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>Media</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Media Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>History</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard History Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>SavedSearch</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard SavedSearch Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>OpenHouse</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard OpenHouse Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>Green</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Green Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>Room</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard Room Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>UnitType</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard UnitType Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>OtherPhone</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard OtherPhone Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>SocialMedia</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard SocialMedia Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>OfficeUrlType</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard OfficeUrlType Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    <d:element>
     <d:Name>UserDefinedField</d:Name>
     <d:ServiceURI>http://me:42999/listing.svc</d:ServiceURI>
     <d:Description>RESO Standard UserDefinedField Resource</d:Description>
     <d:DateTimeStamp m:type=”Edm.DateTime”>2014-09-03T00:39:36.467Z</d:DateTimeStamp>
     <d:TimeZoneOffset m:type=”Edm.Int32″>-7</d:TimeZoneOffset>
     <d:Localizations m:type=”Collection(RESO.OData.Transport.Localization)” />
    </d:element>
    </d:Resources>
    <d:ID>RESO API Server</d:ID>
   </m:properties>
  </content>
</entry>
</feed>

If we talk to the listing.svc, we get:

image

fiddler spying on the communications shows that excel expects the listing.svc to be supported by a $metadata endpoint:

image

Advertisements

About home_pw@msn.com

Computer Programmer who often does network administration with focus on security servers. Very strong in Microsoft Azure cloud!
This entry was posted in odata. Bookmark the permalink.