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

Aplica-se a:SQL ServerBanco 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 saber mais sobre a solução que lhe interessa, siga os links fornecidos.

Lista de métodos

Existem duas maneiras de importar dados do Excel. Talvez seja necessário instalar o SSMS (SQL Server Management Studio) 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.

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 saber mais, confira:

• 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, crie um pacote do SSIS que usa a origem do Excel e o destino do SQL Server no fluxo de dados.

Para saber mais, confira:

• 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

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 saber mais, confira Opção de Configuração do Servidor de 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 (As consultas distribuídas ainda são compatíveis com o SQL Server 2019 (15.x), mas a documentação desse recurso não foi atualizada.)
• OPENROWSET
• OPENDATASOURCE

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
• OPENQUERY

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é-requisito – salvar os dados do Excel como texto

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.

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.

O Assistente Importar Arquivo Simples

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

Conforme descrito anteriormente na seçãoPré-requisito, 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é-requisito, você deve exportar os dados do Excel como texto antes que possa usar BULK INSERT para importá-lo. BULK INSERT não pode ler diretamente arquivos do 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, confira os seguintes artigos:

• Importar dados em massa usando BULK INSERT ou OPENROWSET(BULK...)
• BULK INSERT

Ferramenta BCP

O BCP é um programa executado 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é-requisito, você deve exportar os dados do Excel como texto antes que possa usar o BCP para importá-lo. 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 utilitário bcp
• Utilitário bcp
• Preparar dados para exportar ou importar 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é-requisito, 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é-requisito, 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)"

Isso indica que o Microsoft OLEDB não foi configurado corretamente. Execute o seguinte código Transact-SQL para solucionar:

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

Isso 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.

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)"

Ambos esses erros normalmente indicam 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.

Os erros completos são:

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.

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)".

Próximas etapas

• 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