I work on sql server 2019 I can't read excel file by python 3.10
but i can read excel file by using open rowset
to read excel file by using open rowset i do as below :
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 success read from sql query
i try to read it using python from sql query as below
it give me error as below
Msg 39004, Level 16, State 20, Line 0
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 0
An external script error occurred:
Error in execution. Check the output for more information.
Traceback (most recent call last):
File "<string>", line 3, in <module>
File "D:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\DE2D6F1E-639B-40F3-BCFE-4CD46AACA4AD\sqlindb_0.py", line 31
f = open(\\192.168.7.9\\Import\6\strtinsertinput (4)-953aee07-ca14-4213-a91e-ab0b0f7f3db2.xlsx)
^
SyntaxError: unexpected character after line continuation character
SqlSatelliteCall error: Error in execution. Check the output for more information.
STDOUT message(s) from external script:
SqlSatelliteCall function failed. Please see the console output for more information.
Traceback (most recent call last):
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 605, in rx_sql_satellite_call
rx_native_call("SqlSatelliteCall", params)
File "D:\SQL Data\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 375, in rx_native_call
ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.
so How to solve issue to can able to read data by python script
What I have tried:
EXECUTE sp_execute_external_script
@language =N'Python',
@script=N'from pathlib import Path
f = open(\\192.168.7.9\\Import\6\strtinsertinput (4)-953aee07-ca14-4213-a91e-ab0b0f7f3db2.xlsx)';
GO