Post reply

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

  • 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

  • April 17, 2022 at 10:10 pm

    #4017684

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

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

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

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