I work on SQL server 2017 I add script python to import data from excel to SQL
python version 3.10
when run query below i get error
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
but when i run query above 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
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
my connection data
my instance name is : AHMEDSALAHSQL
my pc name DESKTOP-L558MLK
userid sa
password:321
i can read and write on this path G:\ImportExportExcel
also sql and python and path on my local system
i using windows 10
so can any one help me on solve this issue please ?
