SQL Server Import/Export to Excel by executing Python script within T-SQL

In this post, let us see how to import excel into SQL Server and export SQL server data to excel by executing Python script within T-SQL.   There are some existing methods to do this using BCP, Bulk Insert, Import & Export wizard from SSMS, SSIS, Azure data factory, Linked serv

80 times
Add to favorites
E-mail Twitter del.icio.us Digg Facebook
  • Using usecols in pd.read_excel
    1 Posts | Last post October 24, 2019
    • Hi: 
      Firstly, I found the article interesting and helpful. Thanks!! 
      I'm trying to enhance the code so that usecols can be used. Idea is to pass a string, ie: A:BE . The string is saved in a SQL Server database column Range varchar(25) = A:BE
      In ML Services external script I set ---> range = str("\"")+str(row["Range"])+str("\"")
      so that I get the double quotes wrapping the content.
      When I print(range) I get "A:BE" which is the expected result.
      When I run pd.read_excel(InFolder+ExcelFileName, sheetname=sheetname, skiprows=skiprow, header=headerrow, usecols=range)
      I get error message:
      Msg 39019, Level 16, State 2, Line 16
      An external script error occurred: 
        File "D:\MSSQL\DEFAULT\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 929, in _make_engine
          self._engine = klass(self.f, **self.options)
        File "D:\MSSQL\DEFAULT\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 1820, in __init__
          self.columns, self.num_original_columns = self._infer_columns()
        File "D:\MSSQL\DEFAULT\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 2183, in _infer_columns
          columns = self._handle_usecols(columns, columns[0])
        File "D:\MSSQL\DEFAULT\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\pandas\io\parsers.py", line 2236, in _handle_usecols
      ValueError: 'A' is not in list
      SqlSatelliteCall error: Error in execution.  Check the output for more information.
      Any ideas as of why is it failing?
      Kindest Regards,