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
T-SQL Import and Export Excel

T-SQL Import and Export Excel

Syntax
OPENROWSET('provider_name', 'provider_string', object | 'query')
T-SQL Import and Export Excel

T-SQL Import and Export Excel

Provider Name

Microsoft.ACE.OLEDB.12.0

Provider String

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

Object

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

T-SQL Import and Export Excel

T-SQL Import and Export Excel

SELECT * FROM OPENROWSET(
	'Microsoft.ACE.OLEDB.12.0', 
	'Excel 12.0; Database=PathExcelFile.xlsx; HDR=YES', 
	[SheetName$]);
T-SQL Import and Export Excel

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

T-SQL Import and Export Excel

Enabled Provider Name Microsoft.ACE.OLEDB.12.0

USE master
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO
T-SQL Import and Export Excel

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;
T-SQL Import and Export Excel

Format String Datetime

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