Importing data from a parent SharePoint site into excel

Copper Contributor

Hi,

I'm trying to get data into an Excel worksheet from a list in SharePoint Online. I'm doing so through Data tab --> New Query --> From Other Sources --> From SharePoint List

At the beginning, I tried this on a testing subsite within my SharePoint https://mysite.sharepoint.com/testsite and it worked. It returned all the available lists in that subsite and I was able to fetch them into Excel. However, then I created the actual list that I want to import in the main site, which is just https://mysite.sharepoint.com but it always gives an error:

 

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error.
Details:
DataSourceKind=SharePoint
DataSourcePath=https://mysite.sharepoint.com/_vti_bin/ListData.svc

 

(basically means it can't find any lists) I've attached a screenshot of the error

 

So does import data from SharePoint list just works for the subsites?

4 Replies

Mohammad...are there any clues in the Event Viewer or ULS log?  A 500 error is at the IIS level and not SharePoint.

Thank you Steve for your reply. We are using SharePoint Online so there's no IIS server involved

In SharePoint Online, the path to My sites is https://tenantname-my/sharepoint.com so I assume you are referring to Mysite as being your tenant.
All of the personal sites are not subsites directly so you can't get all subsites and you should be able to list any lists at that URL but it won't then traverse subsites.
If you are referring to the top level SharePoint site it will be your https://tenantname-my.sharepoint.com
Just to add to that, the my sites top level URL wouldn't have any lists as it is just the navigation point with a specific template for hosting my sites.