DetailPage-MSS-KB

Base de Dados de Conhecimento

ID do artigo: 321686 - Última revisão: sexta-feira, 29 de novembro de 2013 - Revisão: 9.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:
  • DTS (Serviços de Transformação de Dados) do SQL Server
  • Microsoft SQL Server 2005 Integration Services (SSIS)
  • Servidores vinculados do SQL Server
  • Consultas de SQL Server distribuído
  • ActiveX Data Objects (ADO) e o provedor Microsoft OLE DB para SQL Server
  • ADO e o provedor Microsoft OLE DB para o Jet 4.0

Requisitos

A lista a seguir descreve as recomendações de hardware, software, infraestrutura de rede e service packs:
  • 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 usam Visual Basic
Partes deste artigo pressupõem 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

Importar vs. Acrescentar

As instruções SQL de amostra que são usadas neste artigo demonstram consultas Criar Tabela que importam dados do Excel para uma nova tabela do SQL Server usando a sintaxe SELECT...INTO...FROM. Você pode converter essas instruções em consultas Acrescentar usando a sintaxe INSERT INTO...SELECT...FROM enquanto continua a fazer referência os objetos de origem e destino, conforme indicado estas amostras de código.

Use DTS ou SSIS

É possível usar o Assistente para Importação do DTS (Serviços de Transformação de Dados) do SQL Server ou o Assistente para Importação e Exportação do SQL Server para importar dados do Excel em tabelas do SQL Server. Enquanto percorre o assistente e seleciona as tabelas de origem do Excel, lembre-se de que nomes de objetos do Excel acrescentados com um sinal de cifrão ($) representam planilhas (por exemplo, Planilha1$) e nomes de objetos simples sem o sinal de cifrão representam intervalos nomeados do Excel.

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 mais informações, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento Microsoft:
306397  (http://support.microsoft.com/kb/306397/pt-br/ ) TUTORIAL: Usar o Excel com servidores vinculados do SQL Server e consultas distribuídas
O código a seguir importa os dados da planilha clientes em "EXCELLINK" do servidor vinculado do Excel em um SQL Server nova tabela denominada XLImport1:
Selecionar * em XLImport1 de EXCELLINK...[Os clientes$]
				
Você também pode executar a consulta em relação à origem de uma maneira de passagem usando OPENQUERY da seguinte maneira:
Selecionar * em XLImport2 de OPENQUERY (EXCELLINK,
    Selecione * FROM [clientes$]')
				

Usar consultas distribuídas

Se não quiser configurar uma conexão persistente com a pasta de trabalho do Excel como um servidor vinculado, você poderá importar dados para uma finalidade específica usando a função OPENDATASOURCE ou 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:
Selecionar * em XLImport3 de OPENDATASOURCE ('. OleDb. ',
' Dados Source=C:\test\xltest.xls;Propriedades estendidas = Excel 8.0')...[Os clientes$]

Selecionar * em XLImport4 de OPENROWSET ('. OleDb. ',
'Excel 8.0;Database=C:\test\xltest.xls', [clientes$])

Selecionar * em XLImport5 de OPENROWSET ('. OleDb. ',
'Excel 8.0;Database=C:\test\xltest.xls', ' selecione * FROM [clientes$]')
				

Usar ADO e SQLOLEDB

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

A seguinte amostra de código do Visual Basic 6.0 requer que você adicione uma referência de projeto ao ADO (ActiveX Data Objects). Esta amostra de código também demonstra como utilizar OPENDATASOURCE e OPENROWSET através de uma conexão 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 o ADO com Provedor SQLOLEDB para se conectar ao destino da sua importação do Excel para o SQL. Você também pode usar o provedor OLE DB para o 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;Cadeia de Conexão ISAM].[Nome da Tabela]
  • [ODBC;Cadeia 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>]
				
The following Visual Basic 6.0 code sample requires that you add a project reference to ADO. This code sample demonstrates how to import Excel data to SQL Server over an ADO connection by using the Jet 4.0 Provider.
    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
				
Também é possível pode usar essa sintaxe, compatível com o Provedor Jet, para importar dados do Excel para outros bancos de dados do Microsoft Access, bancos de dados ISAM ("desktop") ou bancos de dados ODBC.

Solução de problemas

  • Lembre-se de que nomes de objetos do Excel acrescentados com um sinal de cifrão ($) representam planilhas (por exemplo, Planilha1$) e nomes de objetos 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 Jet, clique no número de artigo abaixo para ler o artigo correspondente na Base de Dados de Conhecimento Microsoft:
    299484  (http://support.microsoft.com/kb/299484/pt-br/ ) PRB: Colunas são classificadas em ordem alfabética ao usar o ADOX para recuperar colunas de uma 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 "majoritário" e retorna valores não correspondentes como valores NULL.Para obter informações adicionais sobre como resolver esse problema, clique no número de artigo abaixo para ler o artigo correspondente na Base de Dados de Conhecimento Microsoft:
    194124  (http://support.microsoft.com/kb/194124/pt-br/ ) PRB: Valores do Excel retornados como NULL usando OpenRecordset em DAO

Referências

Para obter informações adicionais sobre como usar o Excel como fonte de dados, clique no número de artigo abaixo para ler o artigo correspondente na Base de Dados de Conhecimento Microsoft:
257819  (http://support.microsoft.com/kb/257819/pt-br/ ) TUTORIAL: Usar o ADO com dados do Excel no Visual Basic ou VBA
Para obter informações adicionais sobre como transferir dados para o Excel, clique nos números de artigo abaixo para ler os artigos correspondentes na Base de Dados de Conhecimento Microsoft:
295646  (http://support.microsoft.com/kb/295646/pt-br/ ) TUTORIAL: Transferir dados de uma fonte de dados ADO ao Excel com o ADO
247412  (http://support.microsoft.com/kb/247412/pt-br/ ) INFORMAÇÕES: Métodos para transferir dados ao Excel a partir do Visual Basic
246335  (http://support.microsoft.com/kb/246335/pt-br/ ) TUTORIAL: Transferir dados de um conjunto de registros ADO ao Excel com automação
319951  (http://support.microsoft.com/kb/319951/pt-br/ ) TUTORIAL: Transferir dados ao Excel usando os Serviços de Transformação de Dados do SQL Server
306125  (http://support.microsoft.com/kb/306125/pt-br/ ) TUTORIAL: 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 Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Palavras-chave: 
kbhowtomaster kbjet KB321686
Compartilhar
Opções de suporte adicionais
Fóruns de Suporte do Microsoft Community
Contate-nos diretamente
Localize um parceiro certificado da Microsoft
Microsoft Store