Arquivo da tag: importar

Importe arquivos muito grandes para o Excel

Dica postada originalmente pelo site PC World e de extrema serventia para algumas situações que envolvam importação de grandes quantidades de dados no Microsoft Excel 2003 e versões anteriores. A versão 2007 já conta com uma quantidade mais generosa de linhas e colunas, tornando este problema menos frequente.


Importe arquivos muito grandes para o Excel
Rodolfo de Benito – PC WORLD/Espanha
09-01-2006

Tem uma lista muito grande que não cabe em uma planilha do Excel? Saiba como criar uma macro para quebrá-la em várias planilhas

As planilhas do Excel têm um limite de 65.536 linhas. Caso você tente importar dados de uma lista com mais itens que esse valor para um arquivo do Excel, receberá um aviso de que a planilha é incapaz de realizar a operação. Uma solução para esse problema é importar a parte dos dados que couber na primeira planilha e depois retomar o processo a partir do ponto onde parou em uma outra planilha do mesmo arquivo. Para isso, basta usar o campo “Iniciar importação na linha” presente na primeira tela do assistente de importação de texto. Se o arquivo ainda não couber em duas planilhas, repita o processo em quantas for necessário.

Para automatizar essa tarefa, é possível criar uma pequena macro em Visual Basic capaz de ler o arquivo dos dados e quebrá-lo em quantas planilhas for preciso. Clique em Ferramentas / Macro / Macros. Dê um nome para sua macro, por exemplo Importar TextosGrandes, e clique em Criar. No editor do Visual Basic que se abrirá, digite o seguinte código:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Sub ImportarTextosGrandes()
    Dim ultimaFila, fila, contador As Long
    Dim linea, NomeArquivo As String
    'Calcula a última linha da planilha
    Selection.End(xlDown).Select
    ultimaFila = Selection.Row
    Selection.End(xlUp).Select
    Set oSistemaArquivo = CreateObject("Scripting.FileSystemObject")
    'Nome do arquivo a importar
    NomeArquivo = "C:\Windows\setuplog2.txt"
    Set arquivo = oSistemaArquivo.OpenTextFile(NomeArquivo, 1, False, -2)
    fila = 1
    contador = 1
    Do While arquivo.AtEndOfStream <> True
        linea = arquivo.ReadLine
        Cells(fila, "a").Value = linea
        'Atualiza barra de status
        Application.StatusBar = "Lendo linha número = " & contador
        fila = fila + 1
        contador = contador + 1
        'Cria nova planilha quando planilha atual está cheia
        If fila > ultimaFila Then
            Worksheets.Add after:=ActiveSheet
            fila = 1
        End If
    Loop
End Sub

Note que a variável NomeArquivo contém o caminho e o nome do arquivo onde estão os dados que serão importados. Criada a macro, para disparar a importação clique em Ferramentas / Macro / Macros. Clique no nome da macro criada e escolha Executar. Durante o processo, a barra de status indicará o número da linha em transferência.

Excel arquivos grandes - 400x

Link original: http://pcworld.uol.com.br/dicas/2006/01/09/idgnoticia.2006-01-09.9646172645/

Tomás Vásquez

Importando e Exportando dados do Excel para o SQL Server com VBA

Aproveitando a pergunta de um colega, deixo aqui a resposta que se mostra uma das formas de recuperar e inserir dados do Excel para o SQL Server com VBA.

Para acompanhar o tópico, viste o link: http://www.juliobattisti.com.br/forum/forum_posts.asp?TID=15402

No SQL Server 2005 Express criei uma base de dados de nome “test_excel” com uma única tabela chamada “names” com os seguintes campos:

id – int – identity
name – nvarchar(50) – allownull = false

O código abaixo serve para extrair os dados no excel na planilha de nome names:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sub RetrieveSQLServerData()
' Cria a conexão.
    Dim cntest_excel As ADODB.Connection
    Set cntest_excel = New ADODB.Connection
    ' Variável para armazenar a String de Conexão.
    Dim strConn As String
    'Informa o SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
    'Conecta à base de dados Pubs no servidor local.
    strConn = strConn &amp; "DATA SOURCE=WKS-DEVELOPER7\SQLEXPRESS;INITIAL CATALOG=test_excel;"
    'Usa autenticação integrada.
    strConn = strConn &amp; " INTEGRATED SECURITY=sspi;"
    'Abre a conexão.
    cntest_excel.Open strConn
    ' Cria o objeto Recordset.
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset
    With rsPubs
        ' Associa a conexão.
        .ActiveConnection = cntest_excel
        ' Extrai os dados.
        .Open "SELECT * FROM names"
        ' Coloca os dados na planilha.
        Worksheets("names").Range("A1").CopyFromRecordset rsPubs
        ' Fecha a transação
        .Close
    End With
    ' Fecha conexão
    cntest_excel.Close
    Set rsPubs = Nothing
    Set cntest_excel = Nothing
End Sub

Para inserir dados na tabela, digite uma lista de nomes simples na coluna A da planilha nomes, por exemplo:

Julio
Robert
Edmilson
Felipe
Com isso, o seguinte código serve para inserir estes nomes na tabela names do SQL Server:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sub InsertDataIntoSQLServer()
' Cria a conexão.
    Dim cntest_excel As ADODB.Connection
    Set cntest_excel = New ADODB.Connection
    ' Variável para controlar o percorrer das linhas da planilha.
    Dim i As Long
    ' Variável para armazenar a String de Conexão.
    Dim strConn As String
    'Informa o SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
    'Conecta à base de dados Pubs no servidor local.
    strConn = strConn & "DATA SOURCE=WKS-DEVELOPER7\SQLEXPRESS;INITIAL CATALOG=test_excel;"
    'Usa autenticação integrada.
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
    'Abre a conexão.
    cntest_excel.Open strConn
    i = 1
    Do Until IsEmpty(Worksheets("names").Cells(i, 1))
        cntest_excel.Execute "INSERT INTO names (name) values ('" & Worksheets("names").Cells(i, 1).Value & "')"
        i = i + 1
    Loop
    ' Fecha conexão
    cntest_excel.Close
    Set rsPubs = Nothing
    Set cntest_excel = Nothing
End Sub

O código funciona para o SQL Server com minhas configurações. Verifique parâmetros como usuário e senha para fazer funcionar na sua máquina corretamente. Não esqueça também de adcionar a referência ao Microsoft Active Data Objects no projeto VBA.

Abraços

Tomás Vásquez

Importando dados do SQL Server para o Excel com VBA e ADO

Essa é uma pergunta que aparece quase sempre nos fóruns de Excel que frequento. O bom é que a própria Microsoft dá a resposta:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
Sub RetrieveSQLServerData()
' Cria a conexão.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection    ' Variável para armazenar a String de Conexão.
    Dim strConn As String
 
    'Informa o SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
 
    'Conecta à base de dados Pubs no servidor local.
    strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=pubs;"
 
    'Usa autenticação integrada.
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
 
    'Abre a conexão.
    cnPubs.Open strConn
 
    ' Cria o objeto Recordset.
    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset
 
    With rsPubs
        ' Associa a conexão.
        .ActiveConnection = cnPubs
        ' Extrai os dados.
        .Open "SELECT * FROM Authors"
        ' Coloca os dados na planilha.
        Plan1.Range("A1").CopyFromRecordset rsPubs
 
        ' Fecha a transação
        .Close
    End With
 
    ' Fecha conexão
    cnPubs.Close
    Set rsPubs = Nothing
    Set cnPubs = Nothing
 
End Sub

Como importar dados de Microsoft SQL Server em Microsoft Excel

Apenas para constar, segue o código fonte pronto para usar:Bom proveito!

Tomás Vásquez

Parametrizando a importação de dados no Excel

Fazendo referência aos treinamentos em vídeo sobre união de planilhas usando a importação de dados no Excel, uma das dúvidas que surgiram entre os telespectadores foi sobre a parametrização dos dados nas consultas em SQL.

Pelo que entendi, foi uma tentativa de fazer analogia às consultas criadas no Access, em que costumeiramente menciona-se parâmetros na cláusula WHERE através do caractere chave colchete “[]”.

A criar uma consulta no Access como esta:

1
SELECT *FROM Clientes WHERE ID = [Digite o Id do Cliente];

Ao ser executada, surge uma caixa de texto com a mensagem colocada entre os colchetes da cláusula WHERE, para que o valor do parâmetro seja informado por digitação. O mesmo não acontece com o recurso de importação de dados do Excel. Pelo menos em meus testes, não consegui uma alternativa automática para fazer a importação de dados. Então, o jeito é fazer por código mesmo. Para tentar ajudar nesta tarefa, efetuarei o mesmo procedimento de importação de dados no vídeo de treinamentos com o gravador de macros ativado. Após isso, o seguinte código VBA é gerado:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Sub ImportarDados()
'
' ImportarDados Macro
' Macro gravada em 22/9/2006 por Tomás Vásquez
'
 
'
    With ActiveSheet.QueryTables.Add(Connection:=Array( _
                                                 "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\temp\Clientes.xls;Mode=Share Deny Write;Extended Prope" _
       , _
                                     "rties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35" _
       , _
                                     ";Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Databa" _
       , _
                                     "se Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=Fa" _
       , "lse;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), _
                                     Destination:=Range("A1"))
        .CommandType = xlCmdSql
        .CommandText = Array( _
                       "SELECT * FROM [Clientes$] WHERE CódigoDoCliente = ""ALFKI""")
        .Name = "Clientes"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "C:\temp\Clientes.xls"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Percebam que todos os dados informados foram gravados, inclusive o nome de planilha e o comando SQL. Agora, usando um pouco de código VBA adicionai, é perfeitamente possível parametrizar nossa importação de dados. Um exemplo extremamente simples é adicionar as seguintes linhas códigos e alterar a linha onde está o comando SQL:

1
2
3
4
5
Dim Codigo As String, SQL As String
Codigo = InputBox("Digite o Código do Cliente:", "Parâmetros da consulta", "Codigo")
SQL = "SELECT * FROM [Clientes$] WHERE CódigoDoCliente = """ &amp; Codigo &amp; """"
'nova linha do comando SQL
.CommandText = Array(SQL)

Agora é só executar e informar o valor do parâmetro na InputBox. Agora é usar a imaginação para construir consultas com parâmetros mais aprimorados, validação e tudo mais. É lógico que agora você pode extrair os valor do parâmetro de qualquer lugar da planilha, como o valor de uma célula, um UserForm. Abaixo segue o código completo:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Sub ImportarDados()
'
' ImportarDados Macro
' Macro gravada em 22/9/2006 por Tomás Vásquez
'
 
'
    Dim Codigo As String, SQL As String
    Codigo = InputBox("Digite o Código do Cliente:", "Parâmetros da consulta", "Codigo")
    SQL = "SELECT * FROM [Clientes$] WHERE CódigoDoCliente = """ & Codigo & """"
    With ActiveSheet.QueryTables.Add(Connection:=Array( _
                                                 "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\temp\Clientes.xls;Mode=Share Deny Write;Extended Prope" _
       , _
                                     "rties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35" _
       , _
                                     ";Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Databa" _
       , _
                                     "se Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=Fa" _
       , "lse;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), _
                                     Destination:=Range("A1"))
        .CommandType = xlCmdSql
        .CommandText = Array(SQL)
        .Name = "Clientes"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "C:\temp\Clientes.xls"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Agora é com vocês. Boa sorte a todos.

Tomás Vásquez