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

Question

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

Answer 1

Check your connection string. This

Driver=Testserver

should be

DSN=Testserver

if you're using a DSN. Otherwise use a DSN-less connection string.