I get error when read excel file permissionerror: [errno 13] permission denied when read excel file from sql server query ?

ahmed salah 3,216 Reputation points
2022-05-07T21:37:53.127+00:00

i get error

when run the line below

FullFilePath='//192.168.7.7/ExportExcel/ComplianceSample.xlsx'

excel_header = list(pd.read_excel(FullFilePath).columns)

error details

Error in execution. Check the output for more information.
Traceback (most recent call last):
File "<string>", line 5, in <module>
File "D:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\6D0E822A-D7E1-4DCE-BAB5-449D89FEAD53\sqlindb_0.py", line 49, in transform
excel_header = list(pd.read_excel(FullFilePath).columns)
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\util_decorators.py", line 178, in wrapper
return func(*args, **kwargs)
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\util_decorators.py", line 178, in wrapper
return func(*args, **kwargs)

Msg 39019, Level 16, State 2, Line 1
An external script error occurred:
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\excel.py", line 307, in read_excel
io = ExcelFile(io, engine=engine)
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\excel.py", line 394, in init
self.book = xlrd.open_workbook(self._io)
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\xlrd__init__.py", line 111, in open_workbook
with open(filename, "rb") as f:
PermissionError: [Errno 13] Permission denied: '//192.168.7.7/ExportExcel/ComplianceSample.xlsx'

when fullpath=\192.168.7.7\ExportExcel\ComplianceSample.xlsx

issue display
but if
fullpath=D:\ExportExcel\ComplianceSample.xlsx

issue not display

so how to solve this issue please

why i can read from local path

and by ip can't read file

I need mandatory shared path because i can't access shared path from another pc

What I have tried:

I give access to folder ExportExcel to have everyone permission

and all_packages and give full control and read and write

also i can open this path from server 7.7

\192.168.7.7\ExportExcel\ComplianceSample.xlsx

sql server 2017 and python and files all exist on server 7.7

when make run from start menu and write path \192.168.7.7\ExportExcel\ComplianceSample.xlsx i can open path

also i can read write on path above .

also path folder ExportExcel give it every one permission and admins permission

so How to solve issue please

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,298 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 106.3K Reputation points MVP
    2022-05-07T21:51:09.433+00:00

    First of all, slashes should be backslashes, and not forward slashes. That is, the path should be \192.168.7.7\ExportExcel\ComplianceSample.xlsx.

    But even with that fixed, I think you are in for an uphill battle. The Launchpad service runs under a local account, and you cannot really expect it to be able access files on a file share. You need to copy the Excel file to your local computer.

    Or consider a different solution altogether,


  2. Seeya Xi-MSFT 16,461 Reputation points
    2022-05-09T08:23:22.373+00:00

    Hi @ahmed salah ,

    Welcome to Microsoft Q&A!
    As Erland said, > The main purpose for the Pyhton support is to run algorithms for machine learning. Not importing Excel files.
    There are other methods to import import data from an Excel file to a SQL Server database: https://www.sqlshack.com/import-data-excel-file-sql-server-database/
    Hope this could give you some help.

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments