Importar dados do Excel para o SQL Server ou o 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 importar dados em uma única etapa diretamente de arquivos do Excel; outros métodos exigem que você exporte seus dados do Excel como texto (arquivo CSV) antes de importá-los.
Este artigo resume os métodos usados com freqüência e fornece links para informações mais detalhadas. Uma descrição completa de 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
Há várias maneiras de importar dados do Excel. Você precisa instalar SQL Server Management Studio (SSMS) para usar algumas dessas ferramentas.
Você pode usar as seguintes ferramentas para importar dados do Excel:
| Exportar para texto primeiro (SQL Server e Banco de Dados SQL do Azure) | Diretamente do Excel (somente SQL Server local) |
|---|---|
| Assistente para Importação de Arquivo Simples | Assistente de Importação e Exportação do SQL Server |
| INSERÇÃO EM MASSA instrução | SQL Server Integration Services (SSIS) |
| Ferramenta de cópia em massa (bcp) | função OPENROWSET |
| Assistente de cópia (Azure Data Factory) | |
| Azure Data Factory |
Se você quiser importar várias planilhas de uma pasta de trabalho do Excel, normalmente precisará executar qualquer uma dessas ferramentas uma vez para cada planilha.
Para obter mais informações, consulte 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ê também pode salvar as configurações como um pacote do SQL Server Integration Services (SSIS) que pode ser personalizado e reutilizado posteriormente.
No SQL Server Management Studio, conecte-se a uma instância do Mecanismo de Banco de Dados do SQL Server.
Expanda Bancos de dados.
Clique com o botão direito do mouse em um banco de dados.
Selecione Tarefas.
Opte por 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
- Introdução a este exemplo simples do Assistente de Importação e Exportação
Serviços de integração (SSIS)
Se você estiver familiarizado com o SQL Server Integration Services (SSIS) e não quiser executar o Assistente de Importação e Exportação do SQL Server, poderá criar um pacote SSIS que use a Origem do Excel e o Destino do SQL Server no fluxo de dados.
Para obter mais informações, consulte os seguintes artigos:
- Fonte do Excel
- de destino do SQL Server
Para começar a aprender como criar pacotes SSIS, consulte o tutorial Como criar um pacote ETL.
OPENROWSET e servidores vinculados
Importante
No Banco de Dados SQL do Azure, você não pode importar diretamente do Excel. Você deve primeiro exportar os dados para um arquivo de texto (CSV).
Os exemplos a seguir usam o provedor JET, porque o provedor ACE incluído no Office que se conecta a fontes de dados do Excel destina-se ao uso interativo do lado do cliente.
Consultas distribuídas
Importe dados diretamente para o SQL Server a partir de arquivos do Excel usando a função Transact-SQL OPENROWSET ou OPENDATASOURCE. Esse uso é chamado de consulta distribuída.
Importante
No Banco de Dados SQL do Azure, você não pode importar diretamente do Excel. Você deve primeiro exportar os dados para um arquivo de texto (CSV).
Antes de executar uma consulta distribuída, você precisa habilitar a opção de configuração do servidor Ad Hoc Distributed Queries, conforme mostrado no exemplo a seguir. Para obter 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 OPENROWSET para importar os dados da planilha do Excel Sheet1 para uma nova tabela de banco de dados.
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.JET.OLEDB.4.0',
'Excel 8.0; Database=C:\Temp\Data.xls', [Sheet1$]);
GO
Aqui está o mesmo exemplo com OPENDATASOURCE.
USE ImportFromExcel;
GO
SELECT * INTO Data_dq
FROM OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Data Source=C:\Temp\Data.xls;Extended Properties=Excel 8.0')...[Sheet1$];
GO
Para acrescentar os dados importados a uma tabela de 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 seguintes artigos:
1 As consultas distribuídas ainda têm suporte no SQL Server, mas a documentação desse recurso não é atualizada.
Servidores vinculados
Você também pode configurar uma conexão persistente do SQL Server para 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 a partir do SQL Server Management Studio (SSMS) ou executando o procedimento armazenado do sistema sp_addlinkedserver, conforme mostrado no 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.JET.OLEDB.4.0';
SET @datasrc = 'C:\Temp\Data.xls';
SET @provstr = 'Excel 8.0';
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server,
@srvproduct,
@provider,
@datasrc,
@location,
@provstr,
@catalog;
Para obter mais informações sobre servidores vinculados, consulte os seguintes artigos:
Para obter mais exemplos e informações sobre servidores vinculados e consultas distribuídas, consulte o seguinte artigo:
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 seus dados do Excel para um arquivo de texto.
Salvar dados do Excel como texto
No Excel, selecione Arquivo | Salvar como e, em seguida, selecione texto (delimitado por tabulações) (*.txt) ou CSV (delimitado por vírgula) (*.csv) como o tipo de arquivo de destino.
Se desejar exportar várias planilhas da pasta de trabalho, selecione cada planilha e repita este procedimento. O comando Salvar como exporta apenas a planilha ativa.
Dica
Para obter melhores resultados com as ferramentas de importação de dados, salve planilhas que contenham apenas os cabeçalhos das colunas e as linhas de dados. Se os dados salvos contiverem títulos de página, linhas em branco, anotações e assim por diante, você poderá ver resultados inesperados mais tarde quando importar os dados.
Assistente para Importação de Arquivo Plano
Importe dados salvos como arquivos de texto percorrendo as páginas do Assistente para Importação de Arquivo Simples.
Conforme descrito anteriormente na seção Pré-requisitos, você precisa exportar seus dados do Excel como texto antes de poder usar o Assistente para Importação de Arquivo Simples para importá-los.
Para obter mais informações sobre o Assistente para Importação de Arquivo Simples, consulte Assistente para Importação de Arquivo Simples para SQL.
Comando BULK INSERT
BULK INSERT é um comando Transact-SQL que você pode executar a partir do SQL Server Management Studio. O exemplo a seguir carrega os dados do arquivo delimitado por vírgulas Data.csv em uma tabela de banco de dados existente.
Conforme descrito anteriormente na seção Pré-requisitos, você precisa exportar seus dados do Excel como texto antes de poder usáBULK INSERT para importá-los.
BULK INSERT não consigo ler arquivos do Excel diretamente. Com o comando BULK INSERT, você pode importar um arquivo CSV armazenado localmente ou no armazenamento de Blob 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 para o SQL Server e o Banco de Dados SQL do Azure, consulte os seguintes artigos:
- Use BULK INSERT ou OPENROWSET(BULK...) para importar dados para o SQL Server
- INSERÇÃO EM MASSA (Transact-SQL)
A ferramenta de cópia em massa (bcp)
A ferramenta bcp é executada a partir do prompt de comando. O exemplo a seguir carrega os dados do arquivo delimitado por vírgulas Data.csv na tabela de banco de dados Data_bcp existente.
Conforme descrito anteriormente na seção Pré-requisitos, você precisa exportar seus dados do Excel como texto antes de poder usar bcp para importá-los. A ferramenta bcp não pode ler arquivos do Excel diretamente. Utilize para importar para o SQL Server ou SQL Database a partir de um arquivo de teste CSV salvo no armazenamento local.
Importante
Para um arquivo de texto (CSV) armazenado no armazenamento de Blob do Azure, use BULK INSERT ou OPENROWSET. Para obter um exemplo, consulte Usar BULK INSERT ou OPENROWSET(BULK...) para importar dados para o SQL Server.
bcp.exe ImportFromExcel..Data_bcp in "C:\Temp\data.csv" -T -c -t ,
Para mais informações sobre bcp, consulte os seguintes artigos:
- Importar e exportar dados em massa usando 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 Azure Data Factory (ADF).
Conforme descrito anteriormente na seção Pré-requisitos, você precisa exportar seus dados do Excel como texto antes de poder usar o Azure Data Factory para importá-los. O Data Factory não pode ler arquivos do Excel diretamente.
Para obter mais informações sobre o Assistente para cópia, consulte os seguintes artigos:
- Assistente de Cópia do Data Factory
- Tutorial: Criar um pipeline com a atividade de cópia usando o Assistente de cópia do Data Factory.
Azure Data Factory
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 copie 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ê precisa exportar seus dados do Excel como texto antes de poder usar o Azure Data Factory para importá-los. 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, consulte os seguintes artigos:
Para começar a aprender a copiar dados com o Azure data factory, consulte os seguintes artigos:
- Mover dados usando a Atividade de Cópia
- Tutorial: Criar um pipeline com a atividade de cópia usando o portal do Azure