I can't read data from excel file by python although i can do it by open rowset ?

Question

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

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  

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

Answer 1

This is an error from Python. If you want to learn how to read Excel files from Pyhton, I suggest that you consult a Python forum.

A tip is to first write a standalone Python script for the task, and not until you have that script running, you try to wrap it in T-SQL. That make it easier to deal with the difficulties of the two environments.

As for the error, don't you think the name of the Excel file needs to be a single string? Right now you have lots of tokens in the call to open. That looks funny.

Answer 2

Hi @ahmed salah ,

> 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 below;

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  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".