Error when import data from excel to SQL server using Python ?

Question

Post reply

Error when import data from excel to SQL server using Python ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

April 16, 2022 at 9:25 pm

#4017360

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 ?

STEPS WHAT I DO

datapaint

This topic was modified 2 years, 7 months ago by ahmed_elbarbary.2010.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.