Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I work on sql server 2017 i add script python to import data to excel
but i get error


(1 row affected)
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: 

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
my instance name is : AHMEDSALAHSQL
my pc name DESKTOP-L558MLK
userid sa
password 321
sql and path is local pc
import path G:\ImportExportExcel
i can access import path without any issue
so how to solve issue please ?

Error in execution.  Check the output for more information.
DataStep error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Traceback (most recent call last):
  File "<string>", line 5, in <module>
  File "C:\PROGRA~1\MICROS~3\MSSQL1~1.AHM\MSSQL\EXTENS~1\AHMEDSALAHSQL01\5597C745-A0D5-49D6-B67F-64CC0F06E21D\sqlindb.py", line 79, in transform
    rx_data_step(input_data = Output, output_file = sqlDS,overwrite = True)
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.AHMEDSALAHSQL\PYTHON_SERVICES\lib\site-packages\revoscalepy\etl\RxDataStep.py", line 320, in rx_data_step
    functionname=function_name, params=call_parameters)
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.AHMEDSALAHSQL\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call

Msg 39019, Level 16, State 2, Line 0
An external script error occurred: 
    ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.

SqlSatelliteCall error: Error in execution.  Check the output for more information.
STDOUT message(s) from external script: 
Rows Read: 3, Total Rows Processed: 3Caught exception in file: CxAnalysis.cpp, line: 6660. ThreadID: 3332 Rethrowing.

[Microsoft][ODBC Driver Manager] Connection not open



ODBC Error in SQLDisconnect
Caught exception in file: CxAnalysis.cpp, line: 5835. ThreadID: 3332 Rethrowing.
Caught exception in file: CxAnalysis.cpp, line: 5384. ThreadID: 3332 Rethrowing.
DataStep function failed. Please see the console output for more information.
SqlSatelliteCall function failed. Please see the console output for more information.
Traceback (most recent call last):
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.AHMEDSALAHSQL\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 406, in rx_sql_satellite_call
    rx_native_call("SqlSatelliteCall", params)
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.AHMEDSALAHSQL\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call

STDOUT message(s) from external script: 
    ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.


Completion time: 2022-04-15T17:57:03.8632030+02:00


What I have tried:

declare @ImportPath NVARCHAR(MAX)='G:\ImportExportExcel'
declare @DBConnectionString NVARCHAR(MAX) = 'Database=z2data;Uid=sa;Pwd=321'
declare @ImportAll BIT=0
declare @CombineTarget BIT=0
declare @ExcelFileName NVARCHAR(200)='dbo.studentsdata'
declare @ExcelSheetName NVARCHAR(50)='students2'
                                      

 
--BEGIN TRY
  

 
 SELECT @ImportPath = CASE WHEN RIGHT(@ImportPath,1) = '\' THEN @ImportPath ELSE CONCAT(@ImportPath,'\') END
 DECLARE @Serv NVARCHAR(200) = CONCAT(CHAR(39),CHAR(39),@@SERVERNAME,CHAR(39),CHAR(39))
 
  DECLARE @ValidPath TABLE (ValidPathCheck BIT)
 
INSERT @ValidPath
EXEC sp_execute_external_script
@language =N'Python',
@script=N'
import pandas as pd
d = os.path.isdir(ImportFilePath)
OutputDataSet = pd.DataFrame([d],columns=["Filename"])'
,@params = N'@ImportFilePath NVARCHAR(MAX)'
,@ImportFilePath = @ImportPath
 

        
     
DECLARE @PythonScript NVARCHAR(MAX) =CONCAT('
import pandas as pd
import os
import glob
from revoscalepy import RxSqlServerData, rx_data_step
sqlConnString = "Driver=AHMEDSALAHSQL;Server=Serv; ',@DBConnectionString,'"
Filefolderepath = ImportFilePath+"*.xlsx"
if ImportAll ==0:
   Filename =ImportFilePath+ExcelFileName+".xlsx"
   exists = os.path.isfile(Filename)
   if exists and ExcelSheetName in pd.ExcelFile(Filename).sheet_names:
         Output = pd.read_excel(Filename, sheetname=ExcelSheetName, na_filter=False).astype(str)
         if not Output.empty:
             sqlDS = RxSqlServerData(connection_string = sqlConnString,table = "".join(fl for fl in ExcelFileName if fl.isalnum())+"_"+"".join(sh for sh in ExcelSheetName if sh.isalnum()))
             rx_data_step(input_data = Output, output_file = sqlDS,overwrite = True)
   else:
      print("Invalid Excel file or sheet name")')
  
 --- print @PythonScript
EXEC   sp_execute_external_script
      @language = N'Python'
     ,@script = @PythonScript
     ,@params = N'@ImportFilePath NVARCHAR(MAX),@ImportAll BIT,@CombineTarget BIT,@ExcelFileName NVARCHAR(200),@ExcelSheetName NVARCHAR(50),@Serv NVARCHAR(200)'
     ,@ImportFilePath = @ImportPath
     ,@ImportAll = @ImportAll
     ,@CombineTarget = @CombineTarget
     ,@ExcelFileName = @ExcelFileName
     ,@ExcelSheetName = @ExcelSheetName
     ,@Serv = @Serv
Posted
Updated 15-Apr-22 7:33am
v2

1 solution

I think you're doing it wrong. If you want to to get data from Excel/MS SQL Server and then to write it into MS SQL Server/MS Excel file, use Python. See:
How to Connect to SQL Server Databases from a Python Program - SQLNetHub[^]
Using Python Pandas dataframe to read and insert data to Microsoft SQL Server | TomazTsql[^]

I'd suggest to use pyodbc + pandas data frame.
#1) Import data from MS Sql Server database and write to MS Excel file
Python
import pyodbc 
import pandas as pd
#connect to ms sql server
cnxn = pyodbc.connect("your_connection_string_here")
#read ms sql server data
df = pd.read_sql_query('select * from table', cnxn)
#write data to ms excel file
df.to_excel('saved_file.xlsx', index = False)
#close connection
cnxn.close()


#2) Import from Excel and write to MS Sql Server database
Python
df = pd.read_excel('FullFileNameHere.xlsx')
cnxn = pyodbc.connect("your_connection_string_here")
cursor = cnxn.cursor()

for index,row in df.iterrows():
    cursor.execute("INSERT INTO TableName(<list_of_columns_here>) 
                         values (?, ?,?)", row['ColumnName1'], 
                                           row['ColumnName2'], 
                                           row['ColumnNameN']) 
    cnxn.commit()
cursor.close()
cnxn.close()


Another way is described here: pandas.DataFrame.to_sql — pandas 1.4.2 documentation[^]

Good luck!
 
Share this answer
 
v4
Comments
ahmed_sa 15-Apr-22 13:37pm    
im not read from excel
i need to import data from excel to sql server 2017
ahmed_sa 15-Apr-22 13:37pm    
i need to insert data from excel to sql server 2017
Maciej Los 15-Apr-22 13:49pm    
Check out updated answer.

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