Sample Header Ad - 728x90

error [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified when import excel data to sql server

0 votes
1 answer
1935 views
I work on SQL server 2017 I need to import data from excel 2016 to sql server 2017 I using python script to do that I create odbc and success test with name Testserver path G:\ImportExportExcel have allpackage and every one full control permissions my instance name is : AHMEDSALAHSQL my pc name DESKTOP-L558MLK named pipe enabled true and instance allow remote when run script below declare @ImportPath NVARCHAR(MAX)='G:\ImportExportExcel' declare @DBConnectionString NVARCHAR(MAX) = 'dsn=Testserver;Uid=sa;Pwd=321' declare @ImportAll BIT=0 declare @CombineTarget BIT=0 declare @ExcelFileName NVARCHAR(200)='dbo.studentsdata' declare @ExcelSheetName NVARCHAR(50)='students2' 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=Testserver;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")') 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 I get error when run query 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 **so can any one help me to solve issue ?** I add odbc connection to my pc and test it success Test success connection
Asked by user3223372 (1 rep)
Apr 16, 2022, 02:56 AM
Last activity: Apr 16, 2025, 09:02 PM