microsoft excel can not parse this odata format?

[EagleSmith]

return data:
{
"Id": "0101010019 ",
"Barcode": "00182",
"Name": "灏忚嫤鑿\ufffd",
"ItemClsId": "200201 "
}
microsoft can't parse this data ?

[EagleSmith]

result message : A missing or empty content type header was found when trying to read a message. The content type header is required

[JocaPC]

There is a bug that might cause this and it is fixed in NuGet version 5.1:
https://www.nuget.org/packages/MsSql.RestApi/0.5.1

However, I have not reproduced your issue.

• Try to use version 0.5.1 with bug-fix and try again.
• If this don't help or if you want to try it with the current version try to pass Metadata.MINIMAL parameter to OData request (depending what function you use). The bug with Content-type should not happen if you use minimal format.
• Make sure that you use the latest version of necessary connector for Excel. When I browse for this error message I see that people resolved it by installing the latest version of WCF clients - maybe the same issues is here.

If nothing helps, could you provide more details? It would be helpful if you could call the OData service directly via browser and see what properties are missing.

I'm not an expert for Excel OData but not that it might be possible that it requires metadata-full or some specific version of OData (for example v3), or maybe XML version instead of JSON, and that this is causing the issue.

[Rich-AU]

I experience similar problem and worked out Excel (and power BI desktop as well) requires proper Odata Service document and cdsl metadata to get the data structure first, which aren't provided by this library.

@JocaPC, is there any plan to enhance this library to provide Odata Service Document and metadata?

[JocaPC]

@Rich-AU @EagleSmith - if you are still interested for this here is example that you can try: https://github.com/JocaPC/sql-server-rest-api/tree/belgrade-odata-min-metadata-api

This sample exposes system views sys.objects, sys.columns, and sys.parameters as OData api and you can add more tables there.

The challenge with Excel is that it requires at least OData with min metadata. My service returns no-metadata by default because this is easiest to configure. To enable excel to read data from OData you need to do two additional things:

• Add root actions that returns OData service metadata based on the list of tables that should be exposed.
• Setup metadata that will return XML metadata document that returns metadata information about the entities.

If URL don't match excel will fail. See setup instructions here: https://github.com/JocaPC/sql-server-rest-api/blob/belgrade-odata-min-metadata-api/README.md

I would be happy to try to make setup experience easier if this works fine.

Please let me know does it works for your.

[JocaPC]

Here is example of OData controller that can provide OData content to Excel and LinqPad in main branch.

[Rich-AU]

@JocaPC , that's really great!

Currently I am working on an example that can dynamically adding controller actions (tables/views) based on configurations in the appsetting.json file - like this :
"Customer": {
"Enabled": "true",
"schema": "SalesLT",
"table": "Customer",
"Columns": "",
"relatedtable": "CustomerAddress",
"relatedschema": "SalesLT",
"relatedJoin": "SalesLT.Customer.CustomerID=SalesLT.CustomerAddress.CustomerID",
"relatedcolumns": ""
}

The only thing I still need to work on is to generate proper service document and metadata, your example above does give me a clear direction, thanks a lot!