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?
1条答案
按热度按时间w6lpcovy1#
我建议把panda换成openpyxl进行阅读操作。
(pd.read文件路径,引擎=“openpyxl”)