SQLTeam.com | Weblogs | Forums

Issue when import data from excel to sql server by python 3.10?

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 ?

Driver=AHMEDSALAHSQL

Is incorrectly specified. DRIVER would be something like "ODBC" or "SQLOLEDB".

thank you for reply
i changed driver to Testserver
but still issue exist
and give me this error
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

i print statement before execute it give me as below

(1 row affected)

import pandas as pd
import os
import glob
from revoscalepy import RxSqlServerData, rx_data_step
sqlConnString = "Driver=Testserver;Server=Serv; Database=z2data;Uid=sa;Pwd=321"
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")

You're not grokking what has been said... the "Driver" is NOT the name of a server! It has to be the name of a "provider", otherwise known as the "driver type" and it has to be like @robert_volk said above.