T-SQL Import and Export Excel

T-SQL Import and Export Excel

  1. Import Excel
  2. Export Excel
  3. Format String Datetime

T-SQL Import and Export Excel

Syntax
OPENROWSET('provider_name', 'provider_string', object | 'query')

T-SQL Import and Export Excel

Provider Name

Microsoft.ACE.OLEDB.15.0

Provider String

Excel 15.0; Database=PathExcelFile.xlsx; HDR=YES

Object

[SheetName$] | 'SELECT * FROM [SheetName$]'

T-SQL Import and Export Excel

SELECT * FROM OPENROWSET(
	'Microsoft.ACE.OLEDB.15.0', 
	'Excel 15.0; Database=PathExcelFile.xlsx; HDR=YES', 
	[SheetName$]);
Selection Columns
SELECT * FROM OPENROWSET(
	'Microsoft.ACE.OLEDB.15.0', 
	'Excel 15.0; Database=PathExcelFile.xlsx; HDR=YES', 
	'SELECT Column1, Column2, ... FROM [SheetName$]');

T-SQL Import and Export Excel

Enabled Ad Hoc Distributed Queries

EXEC sp_configure  @configname = 'show advanced options', 
	@configvalue = 1;
RECONFIGURE;
GO
EXEC sp_configure @configname = 'Ad Hoc distributed Queries', 
	@configvalue = 1;
RECONFIGURE;
GO

T-SQL Import and Export Excel

Enabled Ad Hoc Distributed Queries

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc distributed Queries', 1;
RECONFIGURE;
GO

T-SQL Import and Export Excel

Enabled Provider Name Microsoft.ACE.OLEDB.15.0

USE master
GO
EXEC sp_MSset_oledb_prop @provider_name = N'Microsoft.ACE.OLEDB.15.0', 
	@property_name = N'AllowInProcess', @property_value = 1;
GO
EXEC sp_MSset_oledb_prop @provider_name = N'Microsoft.ACE.OLEDB.15.0', 
	@property_name = N'DynamicParameters', @property_value = 1;
GO

T-SQL Import and Export Excel

Enabled Provider Name Microsoft.ACE.OLEDB.15.0

USE master
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0' , 
	N'AllowInProcess' , 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0' , 
	N'DynamicParameters' , 1
GO

T-SQL Export To Excel

OPENROWSET

INSERT INTO OPENROWSET(
	'Microsoft.ACE.OLEDB.12.0', 
	'Excel 12.0 Xml;Database=FileExelPath.xlsx', 
	[SheetName$]
) SELECT * FROM TableName;

Format String Datetime

SELECT FORMAT(DATEADD(D, -1, GETDATE()), 'yyyyMMdd') + '.xlsx';
GO