无法从Python访问Excel文件并出现错误No such file or directory?

6ojccjat  于 2022-12-05  发布在  Python
关注(0)|答案(1)|浏览(475)

I work with SQL Server 2019 and Python 3.10.
When I try to read an Excel file with OPENROWSET using this statement:

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=\\192.168.7.9\\Import\6\strtinsertinput (4)-953aee07-ca14-4213-a91e-ab0b0f7f3db2.xlsx;HDR=YES','select * FROM [Sheet1$]')

It reads an Excel file successfully,
But when I try to read it using Python from SQL query

EXECUTE sp_execute_external_script
      @language = N'Python',
      @script = N'import pandas as pd
  df = pd.read_excel("\\192.168.7.9\\Import\6\strtinsertinput (4)-953aee07-ca14-4213-a91e-ab0b0f7f3db2.xlsx", sheet_name = "Sheet1")';
  GO

I get this error:
Error in execution. Check the output for more information.
Traceback (most recent call last):
File "", line 5, in
File "C:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\46CB4A4F-004A-4329-A390-FEF283444F33\sqlindb_0.py", line 31, in transform
df = pd.read_excel("\192.168.7.9\Import\6\strtinsertinput (4)-953aee07-ca14-4213-a91e-ab0b0f7f3db2.xlsx", sheet_name = "Sheet1")
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\util_decorators.py", line 178, in wrapper
return func(*args, **kwargs)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\util_decorators.py", line 178, in wrapper
return func(*args, **kwargs)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\excel.py", line 307, in read_excel
io = ExcelFile(io, engine=engine)
Msg 39019, Level 16, State 2, Line 0
An external script error occurred:
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\excel.py", line 394, in init
self.book = xlrd.open_workbook(self.io)
File "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\xlrd_init
.py", line 111, in open_workbook
with open(filename, "rb") as f:
FileNotFoundError: [Errno 2] No such file or directory: '\192.168.7.9\Import\x06\strtinsertinput (4)-953aee07-ca14-4213-a91e-ab0b0f7f3db2.xlsx'
How to solve this issue?

w6lpcovy

w6lpcovy1#

我建议把panda换成openpyxl进行阅读操作。
(pd.read文件路径,引擎=“openpyxl”)

相关问题