Click here to Skip to main content
15,395,877 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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
Posted
Updated 26-Jun-22 5:54am

1 solution

Look at the error message:
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)

The parameter in the open statement must be a string, i.e. surrounded by single (') or double (") quote characters.

Try
Python
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
   
v2
Comments
ahmed_sa 26-Jun-22 14:20pm
   
thank you for reply
i try answer posted above but i get error
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 5, in <module>
File "D:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\F760843E-D95E-47A2-B40E-3106F323A76E\sqlindb_0.py", line 31, in transform
f = open("\\192.168.7.9\\Import\6\strtinsertinput (4)-953aee07-ca14-4213-a91e-ab0b0f7f3db2.xlsx")
FileNotFoundError: [Errno 2] No such file or directory: '\\192.168.7.9\\Import\x06\\strtinsertinput (4)-953aee07-ca14-4213-a91e-ab0b0f7f3db2.xlsx'

SqlSatelliteCall error: Error in execution. Check the output for more information.
Richard MacCutchan 27-Jun-22 2:47am
   
What part of "FileNotFoundError" and "No such file or directory" are you having a problem with?
ahmed_sa 28-Jun-22 3:48am
   
after apply answer above i get error as

full 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 5, in <module>
File "C:\ProgramData\MSSQLSERVER\Temp-PY\Appcontainer1\B6088BCA-3151-4E8E-B865-76108461458A\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'

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 "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 605, in rx_sql_satellite_call
rx_native_call("SqlSatelliteCall", params)
File "C:\Program Files\Microsoft SQL Server\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.
Richard MacCutchan 28-Jun-22 3:53am
   
FileNotFoundError: [Errno 2] No such file or directory: '\\192.168.7.9\\Import\x06\\strtinsertinput (4)-953aee07-ca14-4213-a91e-ab0b0f7f3db2.xlsx'

No one here can find that file for you. You need to investigate why it cannot be found. at a (wild) guess, you Python code cannot access that IP address.
ahmed_sa 28-Jun-22 6:11am
   
but if not access why
open rowset get data from same ip and same path
Richard MacCutchan 28-Jun-22 6:38am
   
Try running the Python code directly, so you can see exactly what is happening.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900