Importar dados do Excel para o SQL Server ou Banco de Dados SQL do Azure

Aplica-se a: SQL Server Banco de Dados SQL do Azure

Há várias maneiras de importar dados de arquivos do Excel para o SQL Server ou para o Banco de Dados SQL do Azure. Alguns métodos permitem que você importe dados em uma única etapa diretamente de arquivos do Excel; outros métodos exigem que você exporte os dados do Excel como texto (arquivo CSV) antes de importá-los.

Este artigo resume os métodos usados com frequência e fornece links para informações mais detalhadas. Uma descrição completa das ferramentas e serviços complexos, como SSIS ou Azure Data Factory, está além do escopo deste artigo. Para obter mais informações sobre a solução que lhe interessa, siga os links fornecidos.

Lista de métodos

Existem duas maneiras de importar dados do Excel. Você precisa instalar o SQL Server Management Studio (SSMS) para usar algumas dessas ferramentas.

É possível usar as seguintes ferramentas para importar dados do Excel:

Se quiser importar várias planilhas de uma pasta de trabalho do Excel, normalmente você precisará executar cada uma dessas ferramentas uma vez para cada planilha.

Para mais informações, confira limitações e problemas conhecidos para carregar dados de ou para arquivos do Excel.

Assistente de Importação e Exportação

Importe dados diretamente de arquivos do Excel usando o Assistente de Importação e Exportação do SQL Server. Você pode salvar as configurações como um pacote SSIS (SQL Server Integration Services) que você pode personalizar e reutilizar mais tarde.

1. No SQL Server Management Studio, conecte-se a uma instância do SQL Server Mecanismo de Banco de Dados.

2. Expanda os Bancos de dados.

3. Clique com o botão direito do mouse em um banco de dados.

4. Selecione Tarefas.

5. Escolha Importar Dados ou Exportar Dados:

   

Isso inicia o assistente:

Para obter mais informações, consulte os seguintes artigos:

• Iniciar o Assistente de Importação e Exportação do SQL Server
• Começar com esse exemplo simples de Assistente de Importação e Exportação

Integration Services (SSIS)

Se você estiver familiarizado com o SSIS (SQL Server Integration Services) e não quiser executar o Assistente de Importação e Exportação do SQL Server, você pode criar um pacote do SSIS que usa a origem do Excel e o destino do SQL Server no fluxo de dados.

Para obter mais informações, consulte os seguintes artigos:

• Origem do Excel
• Destino do SQL Server

Para aprender a compilar pacotes do SSIS, veja o tutorial Como criar um pacote do ETL.

OPENROWSET e servidores vinculados

O provedor ACE (anteriormente provedor Jet) que se conecta a fontes de dados do Excel é destinado ao uso interativo do lado do cliente. Se você usar o provedor ACE no SQL Server, especialmente em processos automatizados ou processos em execução em paralelo, poderá ver resultados inesperados.

Consultas distribuídas

Importe dados diretamente para o SQL Server de arquivos do Excel usando a função OPENROWSET ou OPENDATASOURCE do Transact-SQL. Esse uso é chamado de consulta distribuída.

Antes de executar uma consulta distribuída, você precisa habilitar a opção de configuração do servidor Ad Hoc Distributed Queries, conforme mostra o exemplo a seguir. Para mais informações, consulte Configuração do servidor: consultas distribuídas ad hoc.

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

O exemplo de código a seguir usa o OPENROWSET para importar os dados da planilha Sheet1 do Excel para uma nova tabela do banco de dados.

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\Temp\Data.xlsx', [Sheet1$]);
GO

Este é o mesmo exemplo com OPENDATASOURCE.

USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Temp\Data.xlsx;Extended Properties=Excel 12.0')...[Sheet1$];
GO

Para acrescentar os dados importados a uma tabela existente em vez de criar uma nova tabela, use a sintaxe INSERT INTO ... SELECT ... FROM ..., em vez da sintaxe SELECT ... INTO ... FROM ... usada nos exemplos anteriores.

Para consultar os dados do Excel sem importá-los, basta usar a sintaxe SELECT ... FROM ... padrão.

Para obter mais informações sobre consultas distribuídas, consulte os artigos a seguir:

• Consultas Distribuídas ¹
• OPENROWSET (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)

¹ As consultas distribuídas ainda têm suporte no SQL Server, mas a documentação desse recurso não é atualizada.

Servidores vinculados

Configure também uma conexão persistente do SQL Server com o arquivo do Excel como um servidor vinculado. O exemplo a seguir importa os dados da planilha Data no servidor vinculado existente EXCELLINK do Excel para uma nova tabela de banco de dados do SQL Server chamada Data_ls.

USE ImportFromExcel;
GO
SELECT * INTO Data_ls FROM EXCELLINK...[Data$];
GO

Você pode criar um servidor vinculado no SQL Server Management Studio (SSMS) ou executando o procedimento armazenado do sistema sp_addlinkedserver, conforme mostra o exemplo a seguir.

DECLARE @RC INT;
DECLARE @server NVARCHAR(128);
DECLARE @srvproduct NVARCHAR(128);
DECLARE @provider NVARCHAR(128);
DECLARE @datasrc NVARCHAR(4000);
DECLARE @location NVARCHAR(4000);
DECLARE @provstr NVARCHAR(4000);
DECLARE @catalog NVARCHAR(128);

-- Set parameter values
SET @server = 'EXCELLINK';
SET @srvproduct = 'Excel';
SET @provider = 'Microsoft.ACE.OLEDB.12.0';
SET @datasrc = 'C:\Temp\Data.xlsx';
SET @provstr = 'Excel 12.0';

EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server,
    @srvproduct,
    @provider,
    @datasrc,
    @location,
    @provstr,
    @catalog;

Para obter mais informações sobre servidores vinculados, consulte os artigos a seguir:

• Criar servidores vinculados (Mecanismo de Banco de Dados do SQL Server)
• OPENQUERY (Transact-SQL)

Para obter exemplos e informações sobre servidores vinculados e consultas distribuídas, veja o artigo a seguir:

• Como usar o Excel com servidores vinculados e consultas distribuídas do SQL Server

Pré-requisitos

Para usar o restante dos métodos descritos nesta página, (a instrução BULK INSERT, a ferramenta bcp ou o Azure Data Factory), primeiro você precisa exportar os dados do Excel para um arquivo de texto.

Salvar dados do Excel como texto

No Excel, clique em Arquivo | Salvar como e Texto (Delimitado por tabulação) (*.txt) ou CSV (Delimitado por vírgula) (*.csv) como o tipo de arquivo de destino.

Se você quiser exportar várias planilhas da pasta de trabalho, selecione cada uma e repita este procedimento. O comando Salvar como exporta apenas a planilha ativa.

Assistente de Importação de Arquivo Simples

Importe dados salvos como arquivos de texto percorrendo as páginas do Assistente Importar Arquivo Simples.

Conforme descrito anteriormente na seçãoPré-requisitos, você precisa exportar os dados do Excel como texto antes de usar o Assistente de Importação de Arquivo Simples para importá-los.

Para saber mais sobre o Assistente Importar Arquivo Simples, confira Importação de arquivo simples para o Assistente do SQL.

Comando BULK INSERT

BULK INSERT é um comando do Transact-SQL que você pode executar no SQL Server Management Studio. O exemplo a seguir carrega os dados no arquivo delimitado por vírgulas Data.csv para uma tabela de banco de dados existente.

Conforme descrito anteriormente na seção Pré-requisitos, você deve exportar os dados do Excel como texto antes que possa usar o BULK INSERT para importá-lo. BULK INSERT não pode ler diretamente arquivos de Excel. Com o comando BULK INSERT, você pode importar um arquivo CSV armazenado localmente ou no Armazenamento de Blobs do Azure.

USE ImportFromExcel;
GO
BULK INSERT Data_bi FROM 'C:\Temp\data.csv'
   WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
);
GO

Para obter mais informações e exemplos do SQL Server e do Banco de Dados SQL do Azure, confira os seguintes artigos:

• Usar BULK INSERT ou OPENROWSET(BULK...) para importar dados para o SQL Server
• BULK INSERT (Transact-SQL)

A ferramenta de cópia em massa (bcp)

A ferramenta bcp é executada no prompt de comando. O exemplo a seguir carrega os dados no arquivo delimitado por vírgulas Data.csv para uma tabela de banco de dados do Data_bcp existente.

Conforme descrito anteriormente na seção Pré-requisitos, você deve exportar os dados do Excel como texto antes que possa usar o bcp para importá-lo. A ferramenta bcp não pode ler diretamente arquivos de Excel. Use-o para fazer uma importação para o SQL Server ou o Banco de Dados SQL de um arquivo de teste (CSV) salvo no armazenamento local.

bcp.exe ImportFromExcel..Data_bcp in "C:\Temp\data.csv" -T -c -t ,

Para saber mais sobre bcp, veja os seguintes artigos:

• Importar e exportar dados em massa usando o bcp (SQL Server)
• Utilitário bcp
• Preparar dados para exportação ou importação em massa

Assistente de Cópia (ADF)

Importe dados salvos como arquivos de texto percorrendo as páginas do Assistente de Cópia do ADF (Azure Data Factory).

Conforme descrito anteriormente na seção Pré-requisitos, você deve exportar os dados do Excel como texto antes que possa usar o Azure Data Factory para importá-lo. O Data Factory não pode ler arquivos do Excel diretamente.

Para obter mais informações sobre o Assistente de Cópia, veja os artigos a seguir:

• Assistente de cópia do data factory
• Tutorial: Criar um pipeline com Atividade de cópia usando o Assistente de cópia do data factory.

Fábrica de dados do Azure

Se você estiver familiarizado com o Azure Data Factory e não quiser executar o Assistente de cópia, crie um pipeline com uma atividade de cópia que copia do arquivo de texto para o SQL Server ou para o Banco de Dados SQL do Azure.

Conforme descrito anteriormente na seção Pré-requisitos, você deve exportar os dados do Excel como texto antes que possa usar o Azure Data Factory para importá-lo. O Data Factory não pode ler arquivos do Excel diretamente.

Para obter mais informações sobre como usar essas fontes e coletores do Data Factory, veja os artigos a seguir:

• Sistema de Arquivos
• SQL Server
• Banco de Dados SQL do Azure

Para aprender a copiar dados com o Azure Data Factory, veja os artigos a seguir:

• Mover dados usando a Atividade de cópia
• Tutorial: Criar um pipeline com Atividade de cópia usando o Portal do Azure

Erros comuns

O Microsoft.ACE.OLEDB.12.0" não foi registrado

Esse erro ocorre porque o provedor OLEDB não está instalado. Instale-o com os Pacotes Redistribuíveis do Mecanismo de Banco de Dados do Microsoft Access 2016. Instale a versão de 64 bits se o Windows e o SQL Server tiverem 64 bits.

O erro completo é:

Msg 7403, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

Não é possível criar uma instância do provedor OLE DB "Microsoft.ACE.OLEDB.12.0" para o servidor vinculado "(null)"

Este erro indica que o Microsoft OLEDB não foi configurado corretamente. Para solucionar esse problema, execute o seguinte código Transact-SQL:

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1;
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1;

O erro completo é:

Msg 7302, Level 16, State 1, Line 3
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

O provedor OLE DB de 32 bits "Microsoft.ACE.OLEDB.12.0" não pode ser carregado em processo em um SQL Server de 64 bits

Este erro ocorre quando uma versão de 32 bits do provedor OLE DB está instalada com um SQL Server de 64 bits. Para solucionar esse problema, desinstale a versão de 32 bits e instale a versão de 64 bits do provedor OLE DB.

O erro completo é:

Msg 7438, Level 16, State 1, Line 3
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

O provedor OLE DB "Microsoft.ACE.OLEDB.12.0" para o servidor vinculado "(null)" relatou um erro

Este erro normalmente indica um problema de permissões entre o processo do SQL Server e o arquivo. Verifique se a conta em execução no serviço SQL Server tem permissão de acesso completo ao arquivo. Não é recomendável tentar importar arquivos da área de trabalho.

O erro completo é:

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Não é possível inicializar o objeto de fonte de dados do provedor OLE DB "Microsoft.ACE.OLEDB.12.0" para o servidor vinculado "(null)"

Este erro normalmente indica um problema de permissões entre o processo do SQL Server e o arquivo. Verifique se a conta em execução no serviço SQL Server tem permissão de acesso completo ao arquivo. Não é recomendável tentar importar arquivos da área de trabalho.

O erro completo é:

Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Conteúdo relacionado

• Começar com esse exemplo simples de Assistente de Importação e Exportação
• Importar dados do Excel ou exportar dados para o Excel com o SSIS (SQL Server Integration Services)
• Utilitário bcp
• Mover dados usando a Atividade de cópia