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



  • 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



  • Check your connection string. This

    Driver=Testserver
    

    should be

    DSN=Testserver
    

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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2