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:
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:
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.
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.
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.
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.
Thanks a lot to both of you, this is very helpful!
Actually, Excel documentation is not very precise (it says Select Data > Get Data but as Yaw mentioned this is under Power Query). Indeed users should not select OData feed from the Get External Data group (at least I was unable to make it work this way, not sure in what context it could be used) but from New Query.
Here are the detailed steps that work for me with Excel (Professional) 2016
Data > New Query > From other sources > From Odata feed
In the OData feed pop up, check Basic and enter your OData URL
If you do not have the correct permissions, you will be prompted to use either anonymous, Windows, Basic, Web API or organizational account access for the feed
Select the Basic tab, enter your ODK Central credentials and the level of the URL level you want to give permission for (e.g. whole ODK Central server, only this project, only this form) and click on Connect
Select the item you want to display - to enable multiple item selection, you need to check the corresponding box and check the different items you want to display and click on Load or Load to (see next step)
If you build a connection with a single item, it is loaded immediately in a new Excel worksheet.
It you built a connection multiple items, you need to allocate them to a specific worksheet manually: right quick on the query > Load to > Table > new worksheet or Existing Worksheet
Last comment, ODK variables within a group do not seem to be displayed by default when loaded, I needed to edit the query and manually expand the groups where appropriate (there may be ways to do so automatically)
Query > Edit
Click on the expansion arrows on the right side of the group column
Check all the columns you want to display within that group and click on OK
Click on Close and load to go back to the worksheet and implement changes
Hi Florian, I think the following link may be what you have been looking for. This being said, I am also not at all an expert with Excel. There are options to schedule a refresh when opening the file, or at a given time interval, but I have not tested them yet.
