T-SQL: Importar dados do Excel e Access usando MDAC – ACE

O Microsoft SQL Server possue ferramentas para fazer o trabalho de importação e exportação de dados do Excel, Access entre outras fontes.

Porém, no meu caso desejo executar um script importação e/ou exportação.

Como fazer isso usando T-SQL?

Para isso é necessário ter instalado o Provider no servidor do SQL Server.

• Microsoft.Jet.OLEDB.4.0 – depreciado
• Microsoft.ACE.OLEDB.12.0
• Microsoft.ACE.OLEDB.16.0

O Jet OLEDB foi depreciado, mas existem diversos códigos antigos de exemplos com ele. Podemos aproveitar esses códigos, substituindo pelo Microsft ACE OLEDB.

Office 2010 – ACE 12 – Microsoft Access Database Engine 2010 Redistributable

• AccessDatabaseEngine.exe
• AccessDatabaseEngine_X64.exe

Office 2016 – ACE 16 – Microsoft Access Database Engine 2016 Redistributable

• AccessDatabaseEngine.exe
• AccessDatabaseEngine_X64.exe

Para conferir a instalação entre no SQL Managment Studio – Server Objects – Linked Servers – Providers

-- Ativa Linked Server

sp_configure 'show advanced options', 1

GO

RECONFIGURE WITH OverRide

GO

sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE WITH OverRide

GO

-- Ativa Permissão ao Provider versão ACE 12

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

Caso recebe algumas mensagem referente a permissão de acesso, ou erro de impossibilidade de criar linked server, leia Permissions needed to set up linked server with out-of-process provider

 

-- Ativa Permissão ao Provider versão ACE 16

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess',1

GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters',1

GO

-- Usa o Provider Jet ( Depeciado ) para acessar XLS

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\Excel\File.xls; Extended Properties=Excel 5.0')...[Sheet1$]

GO

-- Usa o Provider ACE 12 para acessar XLS / XLSX

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=C:\ExcelFiles\File2003.xls; Extended Properties=Excel 8.0')...[Sheet1$]

GO

-- Usa o Provider ACE 16 para acessar XLS / XLSX

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0','Data Source=C:\ExcelFiles\File2016.xlsx; Extended Properties=Excel 12.0')...[Sheet1$]

GO

Resultado

Importante!!!

O arquivo está na unidade “C:” local do SERVIDOR SQL Server.

Para abrir arquivo da rede, use o caminho COMPLETO, conforme abaixo:

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=\\NomeDoPC\NomePastaCompartilhada\NomeArquivo.xlsx; Extended Properties=Excel 8.0')...[NomePlanilha$]

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0','Data Source=\\NomeDoPC\NomePastaCompartilhada\NomeArquivo.xlsx; Extended Properties=Excel 12.0')...[NomePlanilha$]

fonte:

Query Excel file source through Linked Server

How to use Excel with SQL Server linked servers and distributed queries

Configuiring a Linked Microsoft Access Server on SQL 2005

Microsoft.Jet.OLEDB4.0 – 32bit and 64bit ( Deprecated )

Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null

Cannot create instance of “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” on x64 Vista with x64 SQL Server 2008 – sp_MSSet_oledb_prop

How to Import and Export SQL Server data to an Excel file

T-SQL OpenDataSource on SQL Server 2017

Import data from Excel to SQL Server or Azure SQL Database

Permissions needed to set up linked server with out-of-process provider