错误[Microsoft][ODBC驱动程序管理器]数据源名称未找到，在将excel数据导入sql server时未指定默认驱动程序

Question

我在SQL server 2017上工作，我需要将数据从excel 2016导入到sql server 2017

我使用python脚本来完成这个任务。

我用名称odbc创建了Testserver和成功测试

路径G:\ImportExportExcel有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

在下面运行脚本时

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

运行查询时会出现错误。

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

，所以有人能帮我解决问题吗?

我将odbc连接添加到我的pc上，并测试它是否成功。

Answer 1

检查你的连接线。这

Driver=Testserver

应该是

DSN=Testserver

如果你用的是DSN。否则使用无DSN连接字符串。