DetailPage-MSS-KB

Base de Dados de Conhecimento

Artigo: 321686 - Última revisão: quinta-feira, 21 de Março de 2013 - Revisão: 5.0

 

Nesta página

Sumário

Este artigo passo a passo demonstra como importar dados de planilhas do Microsoft Excel para bancos de dados do Microsoft SQL Server usando uma variedade de métodos.

Descrição da técnica

Os exemplos neste artigo importar dados do Excel usando:
  • SQL Server Data Transformation Services (DTS)
  • Microsoft SQL Server 2005 Integration Services (SSIS)
  • Servidores SQL Server vinculada
  • Consultas do SQL Server distribuído
  • ActiveX Data Objects (ADO) e o Microsoft OLE DB Provider para SQL Server
  • ADO e o Microsoft OLE DB Provider para Jet 4.0

Requisitos

A lista a seguir descreve o hardware recomendado, software, infra-estrutura de rede e service packs necessários:
  • Instância disponível do Microsoft SQL Server 7.0 ou Microsoft SQL Server 2000 ou Microsoft SQL Server 2005
  • Microsoft Visual Basic 6.0 para os exemplos de ADO que usa o Visual Basic
Partes deste artigo presumem que você esteja familiarizado com os seguintes tópicos:
  • Serviços de transformação de dados
  • Servidores vinculados e consultas distribuídas
  • Desenvolvimento do ADO no Visual Basic

Amostras

Importação versus acréscimo

As instruções SQL de exemplo que são usadas neste artigo demonstram consultas Criar Tabela que importam dados do Excel em uma nova tabela do SQL Server usando a sintaxe SELECT...INTOFROM. Você pode converter essas instruções em Consultas Acréscimo, usando a sintaxe INSERT INTO...SELECT...FROM, e continuar consultando os objetos de origem e destino conforme monstram estas amostras de código.

Use DTS ou SSIS

Você pode usar o Assistente de importação do SQL Server Data Transformation Services (DTS) ou o SQL Server importar e exportar assistente para importar dados do Excel para tabelas do SQL Server. Quando você estiver percorrendo o assistente e selecionar as tabelas de origem do Excel, lembre-se de que os nomes de objeto do Excel que são acrescentados com um sinal de cifrão ($) representam planilhas (por exemplo, Sheet1$) e nomes de objeto simples sem o símbolo de cifrão representam Excel intervalos nomeados.

Usar um servidor vinculado

Para simplificar as consultas, você pode configurar uma pasta de trabalho do Excel como um servidor vinculado no SQL Server.Para obter informações adicionais, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
306397  (http://support.microsoft.com/kb/306397/EN-US/ ) HOWTO: Como usar o Excel com os Servidores Vinculados do SQL Server e Consultas Distribuídas
O código a seguir importa os dados da planilha de clientes no servidor vinculado do Excel "EXCELLINK" em uma nova tabela do SQL Server chamado XLImport1:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
				
Você também pode executar a consulta em relação à origem de uma maneira de passagem usando OPENQUERY da seguinte maneira:
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')
				

Usar consultas distribuídas

Se não desejar configurar uma conexão persistente para a pasta de trabalho do Excel como um servidor vinculado, você pode importar dados para uma finalidade específica, usando o OPENDATASOURCE ou a função OPENROWSET. Os exemplos de código a seguir também importam os dados da planilha Customer (Clientes) do Excel para novas tabelas do SQL Server:
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
				

Usar ADO e SQLOLEDB

Quando você está conectado ao SQL Server em um aplicativo ADO usando o Microsoft OLE DB para SQL Server (SQLOLEDB), você pode usar a mesma sintaxe "consulta distribuída" das Usando consultas distribuídas seção importar dados do Excel para o SQL Server.

O seguinte exemplo de código do Visual Basic 6.0 requer que você adicione uma referência de projeto para o ActiveX Data Objects (ADO). Este exemplo de código também demonstra como usar OPENDATASOURCE e OPENROWSET através de uma conexão de SQLOLEDB.
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
        "Initial Catalog=<database>;User ID=<user>;Password=<password>"

    'Import by using OPENDATASOURCE.
    strSQL = "SELECT * INTO XLImport6 FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=C:\test\xltest.xls;" & _
        "Extended Properties=Excel 8.0')...[Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and object name.
    strSQL = "SELECT * INTO XLImport7 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "[Customers$])"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and SELECT query.
    strSQL = "SELECT * INTO XLImport8 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "'SELECT * FROM [Customers$]')"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing
				

Usar o ADO e o provedor Jet

O exemplo na seção anterior usa ADO com o provedor SQLOLEDB para se conectar ao destino de importação do Excel para SQL. Você também pode usar o OLE DB Provider for Jet 4.0 para se conectar à fonte de Excel.

O mecanismo de banco de dados Jet pode fazer referência a bancos de dados externos em instruções SQL usando uma sintaxe especial que tem três formatos diferentes:
  • [Caminho completo para o banco de dados do Microsoft Access].[Nome da tabela]
  • [Nome do ISAM;Seqüência de conexão do ISAM].[Nome da tabela]
  • [ODBC;Seqüência de conexão ODBC].[Nome da tabela]
Esta seção usa o terceiro formato para fazer uma conexão ODBC com o banco de dados do SQL Server de destino. Você pode usar um nome de fonte de dados ODBC (DSN) ou uma seqüência de caracteres de conexão sem DSN:
DSN:
    [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

DSN-less:
   [odbc;Driver={SQL Server};Server=<server>;Database=<database>;
       UID=<user>;PWD=<password>]
				
O seguinte exemplo de código do Visual Basic 6.0 requer que você adicione uma referência ao ADO. Este exemplo de código demonstra como importar dados do Excel para o SQL Server através de uma conexão ADO usando o provedor do Jet 4.0.
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\xltestt.xls;" & _
        "Extended Properties=Excel 8.0"
    
    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].XLImport9 " & _
        "FROM [Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
        
    cn.Close
    Set cn = Nothing
				
Você também pode usar essa sintaxe, aceita pelo provedor Jet, para importar dados do Excel para outros bancos de dados do Microsoft Access, bancos de dados do Método de Acesso Seqüencial Indexado (ISAM) ("desktop") ou os bancos de dados ODBC.

Solução de problemas:

  • Lembre-se de que os nomes de objeto do Excel que são acrescentados de um sinal de cifrão ($) representam planilhas (por exemplo, Sheet1$) e nomes de objeto simples representam intervalos nomeados do Excel.
  • Em algumas circunstâncias, especialmente quando você designa os dados de origem do Excel usando o nome da tabela em vez de uma consulta SELECT, as colunas na tabela do SQL Server de destino são reorganizadas em ordem alfabética.Para obter informações adicionais sobre esse problema com o provedor do Jet, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
    299484  (http://support.microsoft.com/kb/299484/EN-US/ ) PRB: Colunas são classificadas em ordem alfabética ao usar ADOX para recuperar colunas da tabela do Access
  • Quando o provedor Jet determina que uma coluna do Excel contém texto misto e dados numéricos, o provedor Jet seleciona o tipo de dados "maioria" e retorna valores nulos não correspondentes.Para obter informações adicionais sobre como solucionar esse problema, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
    194124  (http://support.microsoft.com/kb/194124/EN-US/ ) PRB: Valores do Excel retornados como NULL usando OpenRecordset do DAO

Referências

Para obter informações adicionais sobre como usar o Excel como uma fonte de dados, clique no número abaixo para ler o artigo na Base de dados de Conhecimento da Microsoft:
257819  (http://support.microsoft.com/kb/257819/EN-US/ ) COMO: Usar o ADO com dados do Excel do Visual Basic ou VBA
Para obter informações adicionais sobre como transferir dados para o Excel, clique nos números abaixo para ler os artigos na Base de dados de Conhecimento da Microsoft:
295646  (http://support.microsoft.com/kb/295646/EN-US/ ) COMO: Transferir dados da fonte de dados ADO para o Excel com o ADO
247412  (http://support.microsoft.com/kb/247412/EN-US/ ) INFO: Métodos para transferir dados do Visual Basic para Excel
246335  (http://support.microsoft.com/kb/246335/EN-US/ ) COMO: Transferir dados de um conjunto de registros ADO para o Excel com a automação
319951  (http://support.microsoft.com/kb/319951/EN-US/ ) COMO: Transferir dados para o Excel usando o SQL Server Data Transformation Services
306125  (http://support.microsoft.com/kb/306125/EN-US/ ) COMO: Importar dados do SQL Server para o Microsoft Excel

A informação contida neste artigo aplica-se a:
  • Microsoft Excel 2000 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL 2005 Server Enterprise
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL 2005 Server Workgroup
Palavras-chave: 
kbhowtomaster kbjet kbmt KB321686 KbMtpt
Tradução automáticaTradução automática
IMPORTANTE: Este artigo foi traduzido por um sistema de tradução automática (também designado por Machine translation ou MT), não tendo sido portanto revisto ou traduzido por humanos. A Microsoft tem artigos traduzidos por aplicações (MT) e artigos traduzidos por tradutores profissionais. O objectivo é simples: oferecer em Português a totalidade dos artigos existentes na base de dados do suporte. Sabemos no entanto que a tradução automática não é sempre perfeita. Esta pode conter erros de vocabulário, sintaxe ou gramática… erros semelhantes aos que um estrangeiro realiza ao falar em Português. A Microsoft não é responsável por incoerências, erros ou estragos realizados na sequência da utilização dos artigos MT por parte dos nossos clientes. A Microsoft realiza actualizações frequentes ao software de tradução automática (MT). Obrigado.
Clique aqui para ver a versão em Inglês deste artigo: 321686  (http://support.microsoft.com/kb/321686/en-us/ )
Partilhar
Opções de suporte adicionais
Fóruns de Suporte da Comunidade Microsoft
Contacte-nos directamente
Encontre um parceiro certificado Microsoft
Loja Microsoft