OData and Excel - Can't access Central

1. What is the problem? Be very detailed.

I'm trying to transfer submissions via OData into Excel from Central. I am using Excel 2016. I have copied the link provided by Central and pasted into the Link field in Excel and this is the message Excel returns:
error1
followed by "Unable to obtain list of tables from the data source"

I get the same error messages if I type in my credentials under the Excel Data Connection Wizard as in:
err2

My credentials are as administrator. I'm using ODK Cloud.

I don't get any additional popup window asking for login information which from the docs or other posts seems maybe to be required?

Am I missing something obvious here?!?!

2. What app or server are you using and on what device and operating system? Include version numbers. Excel 2016 and Central 1.1.2

3. What you have you tried to fix the problem?
I tried logging out of Excel/Microsoft but no difference.

4. What steps can we take to reproduce the problem?

5. Anything else we should know or have? If you have a test form or screenshots or logs, attach below.

Sorry you are having problems! I haven't tested with Excel 2016 and I'm guessing it doesn't have great OData support.

I'd like to reproduce this issue. What specific version of Excel 2016 do you have? Find it by clicking on the File button, on the top left corner of Excel. Then click on Account, on the left-hand side of the screen, then About Excel. The version is visible in the first paragraph of the dialog box that appears.

Hi @yanokwa. Excel version is 2016 MSO 16.0.13929.20206 32-bit

1 Like

I believe Excel 2016 out of the box doesn't support OData 4. But if you install Microsoft Power Query for Excel, then you'll be able to go to the Data tab, then New Query, then From Other Sources, then use that OData feed option. Can you try that and see if it works for you?

I went to install Power Query and it tells me I've already got it installed as part of Excel 2016. ( I don't really remember if I installed it before or not for some other reason but if I did it was many moons ago).

Regardless, I am still seeing an "Unable to Connect" message.
image

Curiously the messages and dialog boxes look "newer" going the "New Query" route vs the "Get External Data ... From Other Sources.." route. It also prompted me for credentials just once but now I can't get the credentials popup window to reappear in case I miss-entered something.

Thanks,
Carl

The new dialogs mean we are getting closer!

It does sound like your credentials aren't correct. I haven't cleared credentials before in Power Query, but Microsoft's Manage data source settings and permissions (Power Query) has some instructions under Manage data source credentials that look promising. Can you try those?

I'm in! Thanks Yaw! For others and for my reference later:

In Excel 2016... menu item... Data...New Query... Data Source Settings and then the Edit Permissions button allows you to change your login credentials. I chose "Basic" and it now allows the connection to go through and I have my data.

2 Likes