How to read data from excel file by Python on SQL server 2019 ?

Question

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 26-Jun-22 5:01am

ahmed_sa

Updated 26-Jun-22 5:54am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard MacCutchan · Answer 1 · 2022-06-26T05:54:00

Solution 1

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

Posted 26-Jun-22 5:54am

Richard MacCutchan

Updated 26-Jun-22 5:55am

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.