Partilhar via

Importar dados do Excel ou exportar dados para o Excel com o SQL Server Integration Services (SSIS)

Aplica-se a:SQL Server SSIS Integration Runtime em Azure Data Factory

Este artigo descreve as informações de conexão que você precisa fornecer e as configurações que você precisa definir para importar dados do Excel ou exportar dados para o Excel com o SQL Server Integration Services (SSIS).

As seções a seguir contêm as informações necessárias para usar o Excel com êxito com o SSIS e para entender e solucionar problemas comuns:

Ferramentas que pode utilizar

Você pode importar dados do Excel ou exportar dados para o Excel com o SSIS usando uma das seguintes ferramentas:

Obtenha os ficheiros de que necessita para ligar ao Excel

Antes de poder importar dados do Excel ou exportar dados para o Excel com o SSIS, talvez seja necessário baixar os componentes de conectividade do Excel se eles ainda não estiverem instalados. Os componentes de conectividade do Excel não são instalados por padrão.

Use a tabela em Não é possível usar as interfaces ODBC, OLEDB ou DAO do Access fora do Office Clique para Executar para entender se componentes adicionais são necessários para seu ambiente.

Observação: Os Drivers do Office System são suportados apenas em determinados cenários, consulte Considerações para automação do lado do servidor do Office para obter orientações específicas.

Especifique o Excel como sua fonte de dados

O primeiro passo é indicar que você deseja se conectar ao Excel.

No processo SSIS

No SSIS, crie um Gerenciador de Conexões do Excel para se conectar ao arquivo de origem ou destino do Excel. Há várias maneiras de criar o gerenciador de conexões:

  • Na área Gerenciadores de Conexões , clique com o botão direito do mouse e selecione Nova conexão. Na caixa de diálogo Adicionar Gerenciador de Conexões SSIS , selecione EXCEL e Adicionar.

  • No menu SSIS , selecione Nova conexão. Na caixa de diálogo Adicionar Gerenciador de Conexões SSIS , selecione EXCEL e Adicionar.

  • Crie o gerenciador de conexões ao mesmo tempo em que configura a Origem do Excel ou o Destino do Excel na página Gerenciador de conexões do Editor de Código-fonte do Excel ou do Editor de Destino do Excel.

No Assistente de Importação e Exportação do SQL Server

No Assistente de Importação e Exportação, na página Escolha uma Fonte de Dados ou Escolha um Destino , selecione Microsoft Excel na lista Fonte de dados .

Se não vir o Excel na lista de origens de dados, certifique-se de que está a executar o assistente de 32 bits. Os componentes de conectividade do Excel são normalmente ficheiros de 32 bits e não são visíveis no assistente de 64 bits.

Arquivo do Excel e caminho do arquivo

A primeira informação a fornecer é o caminho e o nome do ficheiro Excel. Você fornece essas informações no Editor do Gerenciador de Conexões do Excel em um pacote SSIS ou na página Escolha uma Fonte de Dados ou Escolha um Destino do Assistente de Importação e Exportação.

Insira o caminho e o nome do arquivo no seguinte formato:

  • Para um ficheiro no computador local, C:\TestData.xlsx.

  • Para um arquivo em um compartilhamento de rede, \\Sales\Data\TestData.xlsx.

Ou selecione Procurar para localizar a planilha usando a caixa de diálogo Abrir.

Importante

Não é possível ligar a um ficheiro Excel protegido por palavra-passe.

Versão Excel

A segunda informação a fornecer é a versão do ficheiro Excel. Você fornece essas informações no Editor do Gerenciador de Conexões do Excel em um pacote SSIS ou na página Escolha uma Fonte de Dados ou Escolha um Destino do Assistente de Importação e Exportação.

Selecione a versão do Microsoft Excel que foi usada para criar o arquivo ou outra versão compatível. Por exemplo, se você teve problemas para instalar os componentes de conectividade 2016, você pode instalar os componentes 2010 e selecionar Microsoft Excel 2007-2010 nesta lista.

Talvez não seja possível selecionar versões mais recentes do Excel na lista se tiver apenas versões mais antigas dos componentes de conectividade instalados. A lista de versões do Excel inclui todas as versões do Excel suportadas pelo SSIS. A presença de itens nesta lista não indica que os componentes de conectividade necessários estão instalados. Por exemplo, o Microsoft Excel 2016 aparece na lista mesmo que você não tenha instalado os componentes de conectividade 2016.

Observação

A partir do SQL Server Management Studio 21 e do SQL Server 2025, o Assistente de Importação e Exportação oferece suporte apenas a um ambiente de 64 bits. Microsoft.JET.OLEDB.4.0 só funciona em ambientes de 32 bits.

Para usar o Assistente de Importação e Exportação para arquivos do Excel em um ambiente de 64 bits, baixe o Microsoft Access Database Engine 2016 Redistributable para instalar o provedor Microsoft.ACE.OLEDB.16.0. Em seguida, selecione Microsoft Excel 2016 como a versão do Excel no Assistente de Importação e Exportação do SQL Server, como a seguinte captura de tela:

Captura de ecrã do Assistente para Importar e Exportar Excel no SSMS com o Microsoft Excel 2016 selecionado.

O provedor Microsoft.ACE.OLEDB.16.0 oferece suporte a arquivos do Excel criados pelo Excel 97-2003 (.xsl) e Excel 2007-2010, 2016 (.xlsx).

A primeira linha tem nomes de colunas

Se você estiver importando dados do Excel, a próxima etapa é indicar se a primeira linha dos dados contém nomes de coluna. Você fornece essas informações no Editor do Gerenciador de Conexões do Excel em um pacote SSIS ou na página Escolha uma Fonte de Dados do Assistente de Importação e Exportação.

  • Se você desabilitar essa opção porque os dados de origem não contêm nomes de coluna, o assistente usará F1, F2 e assim por diante, como cabeçalhos de coluna.
  • Se os dados contiverem nomes de coluna, mas você desabilitar essa opção, o assistente importará os nomes de coluna como a primeira linha de dados.
  • Se os dados não contiverem nomes de coluna, mas você habilitar essa opção, o assistente usará a primeira linha de dados de origem como os nomes das colunas. Nesse caso, a primeira linha de dados de origem não é mais incluída nos próprios dados.

Se você estiver exportando dados do Excel e habilitar essa opção, a primeira linha de dados exportados incluirá os nomes das colunas.

Planilhas e intervalos

Há três tipos de objetos do Excel que você pode usar como origem ou destino para seus dados: uma planilha, um intervalo nomeado ou um intervalo sem nome de células que você especifica com seu endereço.

  • Planilha. Para especificar uma planilha, acrescente o $ caractere ao final do nome da planilha e adicione delimitadores ao redor da cadeia de caracteres - por exemplo, [Sheet1$]. Ou procure um nome que termine com o $ caractere na lista de tabelas e modos de exibição existentes.

  • Intervalo nomeado. Para especificar um intervalo nomeado, forneça o nome do intervalo - por exemplo, MyDataRange. Ou procure um nome que não termine com o $ caractere na lista de tabelas e exibições existentes.

  • Intervalo sem nome. Para especificar um intervalo de células que você não nomeou, acrescente o caractere $ ao final do nome da planilha, adicione a especificação do intervalo e adicione delimitadores ao redor da cadeia de caracteres - por exemplo, [Sheet1$A1:B4].

Para selecionar ou especificar o tipo de objeto do Excel que você deseja usar como origem ou destino para seus dados, siga um destes procedimentos:

No processo SSIS

No SSIS, na página Gerenciador de conexões do Editor de código-fonte do Excel ou do Editor de destino do Excel, siga um destes procedimentos:

  • Para usar uma planilha ou um intervalo nomeado, selecione Tabela ou exibição como o modo de acesso a dados. Em seguida, na lista Nome da planilha do Excel , selecione a planilha ou o intervalo nomeado.

  • Para usar um intervalo sem nome especificado com seu endereço, selecione o comando SQL como o modo de acesso a dados. Em seguida, no campo de texto do comando SQL , insira uma consulta como o exemplo a seguir:

    SELECT * FROM [Sheet1$A1:B5]

No Assistente de Importação e Exportação do SQL Server

No Assistente de Importação e Exportação, siga um destes procedimentos:

  • Ao importar do Excel, siga um destes procedimentos:

    • Para usar uma planilha ou um intervalo nomeado, na página Especificar cópia ou consulta de tabela , selecione Copiar dados de uma ou mais tabelas ou exibições. Em seguida, na página Selecionar Tabelas e Exibições de Origem , na coluna Origem , selecione as planilhas de origem e os intervalos nomeados.

    • Para usar um intervalo sem nome especificado com seu endereço, na página Especificar cópia de tabela ou consulta , selecione Gravar uma consulta para especificar os dados a serem transferidos. Em seguida, na página Fornecer uma consulta de origem , forneça uma consulta semelhante ao exemplo a seguir:

      SELECT * FROM [Sheet1$A1:B5]

  • Ao exportar para o Excel, siga um destes procedimentos:

    • Para usar uma planilha ou um intervalo nomeado, na página Selecionar Tabelas e Modos de Exibição de Origem , na coluna Destino , selecione as planilhas de destino e os intervalos nomeados.

    • Para usar um intervalo sem nome especificado com seu endereço, na página Selecionar Tabelas e Exibições de Origem , na coluna Destino , insira o intervalo no seguinte formato sem delimitadores: Sheet1$A1:B5. O assistente adiciona os delimitadores.

Depois de selecionar ou inserir os objetos do Excel a serem importados ou exportados, você também pode fazer o seguinte na página Selecionar Tabelas e Exibições de Origem do assistente:

  • Revise os mapeamentos de coluna entre origem e destino selecionando Editar mapeamentos.

  • Visualize dados de exemplo para ter certeza de que é o que você espera selecionando Visualizar.

Problemas com tipos de dados

Tipos de dados

O driver do Excel reconhece apenas um conjunto limitado de tipos de dados. Por exemplo, todas as colunas numéricas são interpretadas como duplas (DT_R8) e todas as colunas de cadeia de caracteres (exceto colunas de memorando) são interpretadas como cadeias de caracteres Unicode de 255 caracteres (DT_WSTR). O SSIS mapeia os tipos de dados do Excel da seguinte maneira:

  • Numérico - flutuador de precisão dupla (DT_R8)

  • Moeda - moeda (DT_CY)

  • Booleano - Booleano (DT_BOOL)

  • Data/hora - datetime (DT_DATE)

  • String - Unicode string, comprimento 255 (DT_WSTR)

  • Memorando - fluxo de texto Unicode (DT_NTEXT)

Conversões de tipo de dados e comprimento

O SSIS não converte implicitamente tipos de dados. Como resultado, talvez seja necessário usar transformações de Coluna Derivada ou Conversão de Dados para converter dados do Excel explicitamente antes de carregá-los em um destino diferente do Excel ou converter dados de uma fonte diferente do Excel antes de carregá-los em um destino do Excel.

Aqui estão alguns exemplos das conversões que podem ser necessárias:

  • Conversão entre colunas de cadeia de caracteres Unicode Excel e colunas de cadeia de caracteres não-Unicode com página de código específica.

  • Conversão entre colunas de cadeia de caracteres do Excel de 255 caracteres e colunas de cadeia de caracteres de comprimentos diferentes.

  • Conversão entre colunas numéricas do Excel de precisão dupla e colunas numéricas de outros tipos.

Sugestão

Se você estiver usando o Assistente de Importação e Exportação e seus dados exigirem algumas dessas conversões, o assistente configurará as conversões necessárias para você. Como resultado, mesmo quando você deseja usar um pacote SSIS, pode ser útil criar o pacote inicial usando o Assistente de Importação e Exportação. Deixe o assistente criar e configurar gerenciadores de conexões, fontes, transformações e destinos para você.

Problemas com a importação

Linhas vazias

Quando você especifica uma planilha ou um intervalo nomeado como origem, o driver lê o bloco contíguo de células começando com a primeira célula não vazia no canto superior esquerdo da planilha ou intervalo. Como resultado, seus dados não precisam começar na linha 1, mas você não pode ter linhas vazias nos dados de origem. Por exemplo, não pode ter uma linha vazia entre os cabeçalhos das colunas e as linhas de dados ou um título seguido de linhas vazias na parte superior da folha de cálculo.

Se houver linhas vazias acima dos dados, não será possível consultar os dados como uma planilha. No Excel, tem de selecionar o intervalo de dados e atribuir um nome ao intervalo e, em seguida, consultar o intervalo nomeado em vez da folha de cálculo.

Valores em falta

O driver do Excel lê um determinado número de linhas (por padrão, oito linhas) na fonte especificada para adivinhar o tipo de dados de cada coluna. Quando uma coluna parece conter tipos de dados mistos, especialmente dados numéricos misturados com dados de texto, o driver decide a favor do tipo de dados majoritário e retorna valores nulos para células que contêm dados do outro tipo. (Em caso de empate, vence o tipo numérico.) A maioria das opções de formatação de célula na planilha do Excel não parece afetar essa determinação de tipo de dados.

Você pode modificar esse comportamento do driver do Excel especificando Modo de importação para importar todos os valores como texto. Para especificar o Modo de Importação, adicione IMEX=1 ao valor de Propriedades Estendidas na cadeia de conexão do gerenciador de conexões do Excel na janela Propriedades.

Texto truncado

Quando o driver determina que uma coluna do Excel contém dados de texto, o driver seleciona o tipo de dados (cadeia de caracteres ou memorando) com base no valor mais longo que ele amostra. Se o driver não descobrir valores maiores que 255 caracteres nas linhas que ele amostra, ele tratará a coluna como uma coluna de cadeia de caracteres de 255 caracteres em vez de uma coluna de memorando. Portanto, valores maiores que 255 caracteres podem ser truncados.

Para importar dados de uma coluna de memorando sem truncamento, você tem duas opções:

  • Verifique se a coluna de memorando em pelo menos uma das linhas de amostra contém um valor maior que 255 caracteres

  • Aumente o número de linhas amostradas pelo driver para incluir essa linha. Você pode aumentar o número de linhas amostradas aumentando o valor de TypeGuessRows na seguinte chave do Registro:

Versão dos componentes redistribuíveis Chave de registo
Excel 2016 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
Excel 2010 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

Problemas com a exportação

Criar um novo arquivo de destino

No processo SSIS

Crie um Gerenciador de Conexões do Excel com o caminho e o nome do arquivo do novo arquivo do Excel que você deseja criar. Em seguida, no Editor de Destino do Excel, para Nome da planilha do Excel, selecione Novo para criar a planilha de destino. Neste ponto, o SSIS cria o novo arquivo do Excel com a planilha especificada.

No Assistente de Importação e Exportação do SQL Server

Na página Escolha um Destino , selecione Procurar. Na caixa de diálogo Abrir , navegue até a pasta onde deseja que o novo arquivo do Excel seja criado, forneça um nome para o novo arquivo e selecione Abrir.

Exportar para um intervalo suficientemente grande

Quando você especifica um intervalo como destino, ocorre um erro se o intervalo tiver menos colunas do que os dados de origem. No entanto, se o intervalo especificado tiver menos linhas do que os dados de origem, o assistente continuará gravando linhas sem erro e estenderá a definição de intervalo para corresponder ao novo número de linhas.

Exportar valores de texto longo

Antes de salvar com êxito cadeias de caracteres com mais de 255 caracteres em uma coluna do Excel, o driver deve reconhecer o tipo de dados da coluna de destino como memorando e não como cadeia de caracteres.

  • Se uma tabela de destino existente já contiver linhas de dados, as primeiras linhas amostradas pelo driver deverão conter pelo menos uma instância de um valor maior que 255 caracteres na coluna do memorando.

Conteúdo relacionado

  • Last updated on