Reading excel sheet data from SQL query in stored procedure in Azure SQL

T S Nandakumar 0
Viewed 62

I am trying to read data from excel file from SQL query and insert it in temp table but not able to read the data from Excel file. Getting Below Errors:

  1. 'OPENDATASOURCE' rowset provider not supported in this version of SQL Server.
  2. 'OPENROWSET' rowset provider not supported in this version of SQL Server.
  3. Linked servers are not supported in this version of SQL Server.

Below Solutions Tried:

SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 8.0;HDR=NO;Database=T:\temp\Test.xlsx',
    'select * from [sheet1$]')

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\Users\Downloads\Excel Logic File.xlsx;Extended Properties=EXCEL 12.0')..[Sheet1$];

Also Tried Executing below before running the query:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

Getting the error( Linked servers are not supported in this version of SQL Server.) EXEC ('select * from table') at linkedserver

azure-sql-server sql-server excel
1 Answers
  • I have tried to Import the data to Azure SQL from On-Premises Excel, got the same error. enter image description here

'OPENROWSET' rowset provider not supported in this version of SQL Server.

As mentioned in MSDoc OPENDATASOURCE and OPENROWSET are supported in Azure SQL Database but the data file must be in Azure Blob Storage to use the BULK option.

Create external data source and upload sample excel file in Azure Blob Storage. Add the required file formats(Please check the following sample)

CREATE  EXTERNAL  DATA  SOURCE mydata
WITH (
TYPE  = BLOB_STORAGE,
LOCATION =  'https://blb1209.blob.core.windows.net'
);
 
SELECT  *  FROM  OPENROWSET(
BULK  'data/sample3.csv',
DATA_SOURCE =  'mydata',
SINGLE_CLOB) AS DataFile;

enter image description here

From On-Premise

Able to Import the Data from Excel using Tasks => Import Data

Check the below workaround.

We can Import Excel Data to Azur SQL using many ways.

Right click on your DB => Tasks => Import Data Select the DataSource as Excel = > Browse your Excel file

enter image description here

Select Destination as Microsoft OLEDB Provider for SQL Server => Enter credentials for SQL Server Authentication

enter image description here

Output in On-prem: enter image description here

Output in Azure SQL enter image description here

userlrrk10
Related