how to import excel file stored in sharepoint document library to powerpivot

Question

Is there any way to import excel workbook which is stored in sharepoint document library as data source to the powerpivot workbook? I know the powerpivot workbook stored in sharepoint document library can be use as external data in powerpivot, how about excel workbook?

Answer 1

Hi Karen,

Yes, you can import Excel file on the SharePoint Server into PowerPivot as external datasource. In the PowerPivot Windows, click From other datasources, then select Excel File, you should type in the Excel File's URL, such as: Http://ServerName/FolderNmae/Excel File.xlsx

Of course you can also merge the Excel file into the PowerPivot Workbook, then create a linked table on the same workbook.

If you have any other question, please feel free to let us know. 

---

Challen Fu

TechNet Community Support

Answer 2

Thanks for reply.

When I type in the Excel File's URL, an error occur

error: The file you have chosen doesn't exist. Select an existing file.

I am sure the url is correct. I paste it in the browser and I can get the excel file.

 

I clicked test connection

============================

Error Message:

============================

 

Invalid internet address.

----------------------------

Failed to connect to the server. Reason: Invalid internet address.

 

============================

Call Stack:

============================

 

   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)

   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)

   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)

   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

   at System.Data.OleDb.OleDbConnection.Open()

   at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.InitializeConnectionObject(String connectionIdentifier)

----------------------------

   at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.InitializeConnectionObject(String connectionIdentifier)

   at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.Open(String& connectionIdentifier)

   at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.Open()

   at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.TestConnection()

   at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.ClickTestConnection(Object progressControl)

 

============================

 

 

Answer 3

Hi,

Please check if the URL you entered to import is in this format

http://ServerName/SharedDocumentLibraryName/YourExcelFileName.xlsx.

I think you have pasted the URL by copying it from URL after opening your excel file in sharepoint library. If so try giving URL in mentioned format

--------------------------------------

Please mark as answer if helpful

 

Answer 4

Hi,

Yes, the URL is in correct format. 

I try to use the same URL format to connect to powerpivot workbook via analysis services. It works fine. But when I try to connect to excel file, exception throws.

my URL is like:
http://ServerName/subsite/DocumentLibrary/ExcelFile.xlsx

Answer 5

It seems that this is the issue of permission, can you access to the ExcelFile from the SharePoint Server using the current user?

---

Challen Fu

TechNet Community Support

Answer 6

Hi,

Sorry, I don't quite understand your question. I have a sharepoint server installed on windows server 2008 r2. I am using another server 2008 r2, which join the same domain with sharepoint server. On this server 2008 r2, I have powerpivot client installed and I want to import the excel file stored in the sharepoint into the powerpivot window. I am currently using the domain\administrator to logon to server. This account supposes to have permission to all sharepoint site, farm administration and content database.

Answer 7

Hi,

Could anyone provide some examples of the Connection string for connection to excel file in sharepoint to me?

Thanks.

Answer 8

Hi Karen,

The below screenshot shows you how to connect to an excel in share point from power pivot. By clicking on "get data from external sources"-> "from excel file" then you should enter the connection string as shown below(http://servername/galleryname/workbookname.xlsx). Please mark as answer if helpful.

Answer 9

Thanks for reply.

I use the same format. With no luck, the error still exists. :[

Answer 10

Hi Karen,

I am facing the same problem while adding excel file stored in sharepoint as datasource to powerpivot workbook. Have you solved it ?

For info, Already I have checked all the solutions mentioned above. still no luck.

       1. Current windows user is farm account

       2. Sharepoint site has been added into trusted sites location

Answer 11

Hi Kalaimani,

I still have not solved it. Do you have any new finding on it?

Answer 12

Hi,

Finally, I solved this. The is not a permission problem. My client and Sharepoint server are in different domain. I find that I can't use the path with URL, instead I can use UNC to access the files stored in Sharepoint. According to this post, "Desktop Experience" has to be installed in the client and also make sure that "Webclient" is started in the services to allows these UNC path to be recognized.

the filepath to sharepoint:
http://servername/documentLibrary/Excelfile.xlsx

the UNC path:
\\servername\documentLibrary\Excelfile.xlsx

Thanks for everyone.

Karen

Answer 13

Has anyone found a solution to this issue? I have been follwing this article http://office.microsoft.com/en-us/excel-help/get-data-from-analysis-services-HA102837073.aspx?CTT=5&origin=HA102836921 with no luck. 

When I enter http://server/bicenter/powerpivotGallery/filename.xlsx  in the "Server or File Name:" and click "Test Connection" I get the following error:

Call Stack:
============================

   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.InitializeConnectionObject(String connectionIdentifier)
----------------------------
   at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.InitializeConnectionObject(String connectionIdentifier)
   at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.Open(String& connectionIdentifier)
   at Microsoft.AnalysisServices.Modeler.Storage.RelationalDataSourceConnection.Open()
   at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.TestConnection()
   at Microsoft.AnalysisServices.Modeler.DataImportWizard.DataSourceBasic.ClickTestConnection(Object progressControl)

============================

I have even entered the UNC path as mentioned in this forum \\servername\bicenter\powerpivotgallery\filename.xlsx and receive the following error:

You cannot use a local PowerPivot workbookfile as a source for Analysis Services.  You must provide the URL to a published PowerPivot workbook in  a SharePoint document library. 

The workbook is published into the PowerPivot Gallery in SharePoint.  So if i select test connection with the previous error message i get this error:

Failed to connect to the server.  REason: Errors in the OLE DB Provider.  Could not connect to the redirector.  Ensure that the SQLBrower service is running on the \\server\bicenter\powerpivotgallery\ server.  Errors in the OLEDB provider.  An error occured while named instance information was being retrieved from the SQL Browswer service on the '\\server\bicenter\powerpivotgallery' server. 

Answer 14

My issue has been resolved, I placed a MS Support call and they were able to resolve this for me.  There was an additional Kerberos setting that I had not  set up for PowerPivot.  This is the article that he gave me to resolve my issue.

http://blogs.msdn.com/b/johndesch/archive/2012/04/23/using-powerpivot-workbooks-from-a-mid-tier-server-configured-for-kerberos-authentication.aspx

What this article basically says is to

1.  Navigate to the PowerPivot folder on the server %SystemDrive%\program files\common files\web service extensions\14\ISAPI\powerpivot  if it's not there then try %SystemDrive%\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\PowerPivot

2.  Make a copy of the web.config file

3.  Open the original web.config file and locate the "RedirectorBinding" heading

4.  Replace "Windows" with "Negotiate"

5. IISRESET

Hope this helps.