Editing Excel Data Source

Hello everybody,

I have a tabular instance on my local sever and one of my data sources is an Excel file. What I wanted to do was select an additional column from the spreadsheet which i had not originally selected (ticked) when i first imported it into my model.

Simple enough, go to "Properties", "Source Data", which shows you a preview of the data, tick the column you want to add and click "OK".  I then receive the following error --OLE DB or ODBC error: The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.; 3051. 

  • The excel file sits in a folder in my local C:
  • I am the Administrator of my local machine.
  • I have granted users - Administrator, Everyone and MSSQLServerOLAPService all permissions possible to the folder and file.
  • I have gone into advanced connection settings of the data source and edited the mode and locking mode.
  • I run task manager to see if the file is open and even downloaded and run Process Explorer V16.05 to see if the file is open.
  • I am the only person which has access to my project and the excel file.

No matter what I have tried, I still get the same error. I can't understand how it is a permission issue and all research I have done on the net points to permissions. Is there something I am missing?

Can somebody please help...

Thanks



   
July 29th, 2015 6:01pm

In  this case there are two possibilities

1) File is corrupted .. So try to open the file and see whether it is not corrupt

2) you have closed the file but due to some issue it has created a temp file with .. . so open file once more and save as with different name  and delete the older one .

Free Windows Admin Tool Kit Click here and download it now
July 29th, 2015 7:51pm

SSAS uses the service account to first access the file regardless of what impersonation information you use. Make sure the account running SSAS can access the file. Check your SQL config manager to find this information!

Another workaround is to add the port explicitly. So try \\server@80\\DavWWWRoot\...\ABC.xlsx (if it is on port 80)

July 30th, 2015 1:21am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics