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

Question

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 "", line 5, in 
  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
    functionname=function_name, params=call_parameters)
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.AHMEDSALAHSQL\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call

Msg 39019, Level 16, State 2, Line 0
An external script error occurred: 
    ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.

SqlSatelliteCall error: Error in execution.  Check the output for more information.
STDOUT message(s) from external script: 
Rows Read: 3, Total Rows Processed: 3Caught exception in file: CxAnalysis.cpp, line: 6660. ThreadID: 3332 Rethrowing.

[Microsoft][ODBC Driver Manager] Connection not open



ODBC Error in SQLDisconnect
Caught exception in file: CxAnalysis.cpp, line: 5835. ThreadID: 3332 Rethrowing.
Caught exception in file: CxAnalysis.cpp, line: 5384. ThreadID: 3332 Rethrowing.
DataStep function failed. Please see the console output for more information.
SqlSatelliteCall function failed. Please see the console output for more information.
Traceback (most recent call last):
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.AHMEDSALAHSQL\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 406, in rx_sql_satellite_call
    rx_native_call("SqlSatelliteCall", params)
  File "C:\Program Files\Microsoft SQL Server\MSSQL14.AHMEDSALAHSQL\PYTHON_SERVICES\lib\site-packages\revoscalepy\RxSerializable.py", line 291, in rx_native_call

STDOUT message(s) from external script: 
    ret = px_call(functionname, params)
RuntimeError: revoscalepy function failed.


Completion time: 2022-04-15T17:57:03.8632030+02:00

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 ?

Answer

The error message:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

That is, this is an error from the ODBC driver manager. This suggests a fairly basic problem. And, well, your connection string looks, hm, funny:

sqlConnString = "Driver=AHMEDSALAHSQL;Server=Serv;

Which driver to use depends on the data source you are to connect to. To connect to SQL Server you would use the ODBC SQL Server driver, to connect to Postgres you would use a Postgres driver etc. I have never heard of an ODBC driver AHMEDSALAHSQL. You did not write your own ODBC driver, did you?

If I understand your post correctly, you want to connect to SQL Server, and in that case you would download and install the ODBC SQL Server 18 Driver from https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15. On the same link you should also find information to specify the connection string.

Answer

Let me put it this way: If you are not willing to on your own to learn the basics of connecting to SQL Server from Python but you want to be spoonfed a solution for every step, maybe Python is not the right solution.

The prime reason of SQL Server supporting Python is machine learning. You know, AI and all that jazz. Loading Excel files from Python and connecting to SQL Server will require some extra work, because the Python environment is locked down. And in the end, your DBA or Windows administrator may not agree to making the permission changes. I would only recommend this path to someone who is already acquainted with Python and PyODBC and also understands the model for the Python services in SQL Server.

Any particular reason, you want to load Excel files this way? There are a couple of more options, of which the safes probably is to use SSIS. Or simply use the Import/Export wizard in SSMS.

Answer

Hi ahmedsalah-1628,

In addition, please also check the scripts from the links which might be helpful:
https://stackoverflow.com/questions/51268991/importing-data-from-an-excel-file-using-python-into-sql-server
https://datatofish.com/import-csv-sql-server-python/

Best Regards,
Amelia

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

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

3 answers