Abrir arquivo texto no Excel o com mais de 1 milhão de linhas

Planilha de cotação de preços Excel
Planilha de cotação de preços Excel
7 de março de 2013
Planilha de Controle de Obras Excel VBA
Planilha de Controle de Obras Excel VBA
19 de março de 2013

Abrir arquivo texto no Excel o com mais de 1 milhão de linhas

Objetivo: Demonstrar como abrir um arquivo texto com mais de 1 milhão de linhas no Excel utilizando VBA.

AbrirArquivoExcel

Esta versão do artigo http://guiadoexcel.com.br/separar-linhas-de-um-arquivo-texto-em-planilhas, neste há uma melhoria pequena que permite que seja selecionado o arquivo, evitando que tenha que digitar o caminho completo do arquivo.

Veja neste link Como abrir arquivos com mais de 1.048.576 linhas no Excel com o PowerQuery no Excel.

Sugiro que incluam esta macro na  sua pasta pessoal de macros para que possa ser usada quando necessária, pode ser de grande utilidade.

O Excel á partir de sua versão 2007 permite a criação de planilhas com 1.048.576 linhas, só que ás vezes temos que abrir arquivos texto no Excel com mais do que o limite de linhas da planilha, ou simplesmente desejamos separar o arquivo em planilhas com a quantidade igual de linhas.

Para isso criei uma macro que abre o arquivo em quantidades iguais em várias planilhas quantas forem necessárias.

Então é isso pessoal, este procedimento não separa o arquivo em colunas, porque cada arquivo texto possuem delimitadores diferentes. Em um próximo post vou demonstrar como separar em colunas um arquivo texto.

Public Sub LerArquivoTexto()
    On Error GoTo TratarErro

    Dim lsCaminho As String
    Dim llArquivo As Long
    Dim llLinha As String
    Dim lQtde As Long
    Dim llPlanilhas As Long

    'Local do Arquivo
    lsCaminho = lfSelecionarArquivo 'InputBox("Digite o caminho do arquivo: ", actName)
    
    'Qtde de Linhas a separar no arquivo
    lQtde = InputBox("A cada quantas linhas separar o arquivo: ", actName)

    'Identificar se o arquivo existe
    If Dir(lsCaminho) <> "" Then
        llArquivo = FreeFile
        
        Open lsCaminho For Input As #llArquivo
        
        lContador = 1
        llPlanilhas = 1
        
        'Ler o arquivo texto
        While Not EOF(llArquivo)
            Line Input #llArquivo, llLinha
            
            If lContador <= lQtde Then
                Range("A" & lContador).Value = llLinha
            Else
                llPlanilhas = llPlanilhas + 1
                Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = CStr(llPlanilhas)
                lContador = 1
                Range("A" & lContador).Value = llLinha
            End If
            lContador = lContador + 1
        Wend

        Close #llArquivo
    Else
        MsgBox "Arquivo não encontrado"
    End If

 
Sair:
    Exit Sub
TratarErro:
    MsgBox "Houve um erro na leitura do arquivo!"
    GoTo Sair
    Resume
End Sub

 

DIGITE O SEU EMAIL PARA FAZER O DOWNLOAD DOS ARQUIVOS:

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

35 Comentários

  1. Raphael disse:

    Marcos,
    como posso fazer com que o Excel importe os dados de arquivos TXT diretamente de um servidor FTP?
    posso citar um exemplo da CETIP, que disponibiliza os valores de média de CDI em seu servidor FTP, como dados em arquivos TXT. O ideal é um script em que o excel buscasse sempre os arquivos novos e extraísse os dados dos arquivos txt conforme as datas de liberação (que está no nome do arquivo).

  2. Raphael disse:

    Bom dia Marcos,
    obrigado por responder.
    o caminho é esse:
    ftp://ftp.cetip.com.br/MediaCDI/
    os arquivos são disponibilizados por dia e os arquivos são nomeados por data. Isto é: 20130416.TXT – 16/04/2013, e o dado de cada arquivo é a média do CDI na mesma data.

  3. Linelson disse:

    Bom dia Marcos, já tem algum post post de como separar em colunas um arquivo texto via macro?

    Att

  4. Vandeir disse:

    O curso VBA,pode ser assistido no sistema ANDROID.

  5. Sidnei disse:

    Marcos Rieper, boa tarde! Esta macro para mim vai ajudar muito, obrigado!. Entretanto, tem como inserir nesta macro a opção de separar o arquivo em colunas. Vi em outra macro que existe no internet que a pessoa determina o delimitador. No meu caso o delimitador é “|”. Se puder ajudar, eu agradeço. Já gravei uma macro no excel importando com o delimitador, mas, não sei inserir na macro Public Sub LerArquivoTexto().

    • Marcos Rieper disse:

      Boa noite Sidnei,

      É possível realizar esta alteração, mas é necessário realizar esta separação pelos delimitadores somente após a separação em planilhas.

      Para isso é necessário ter conhecimento em VBA para realizar as alterações necessárias.

      Abraço

      Marcos Rieper

  6. Adérito disse:

    Marcos

    De tudo que ja achei na rede ,este seu codigo me ajuda e muito.
    Meu caso tenho um arquivo em txt com cerca de 8.460.252 registro(tamanho do arquivo 3.5gb).

    Rodei o codigo,porem nao carregou totalmente os registro,ao selecionar a quantidade de 1.000.000 de registro ,ele executa com sucesso mas para na 3 aba carregando somente 1.500.000 registro.

    Aparece uma tela de erro é necessario selecionar pouco dados ou fechar alguns aplicativos.

    Neste caso seria algum problema no excel(usando versao 2007 .xlxs) ou algo no codigo?

    Outro coisa tenho como usar este codigo para o acess?

    Antecipamente voce esta de parabens por esta soluçao e pela ajuda

  7. Adérito disse:

    Marcos

    Acabei de formatar um micro deixando zerado.

    Rodei o codigo e apresentou o mesmo erro.
    Percebi que quando chega a cerca de 1.500.000 registros ele trava.

    Tem algum algum modo de verificarmos este processo?

    • Marcos Rieper disse:

      Bom dia Adérito,

      O problema pode ser a falta de memória do computador, pois a massa de dados é muito grande.

      Você pode dividir o arquivo em mais de um e depois aplicar o procedimento.

      Para isso utilize o software http://notepad-plus-plus.org/ que permite abrir arquivos muito grandes, recorte uma parte dos dados, crie um novo arquivo e cole os dados neste novo, depois aplique a planilha sobre os dois arquivos. Acredito que deva resolver o problema.

      Abraço

      Marcos Rieper

  8. Ronildo disse:

    Marcos, quando colei a macro, ao fazer um teste de como rodar, na linha abaixo de “Indetificar se o arquivo existe”, é mostrada a mensagem: “Erro de compilação: erro de sintaxe” e marca toda a linha. O que pode estar causando o problema? E outra pergunta: Como faço para abrir o arquivo executando a macro?

    • Marcos Rieper disse:

      Bom dia Ronildo,

      O sistema do blog converteu incorretamente os caracteres e por isso gerou o problema, desculpe a nossa falha.

      O problema já foi corrigido, e você pode baixar novamente o código.

      Abraço

      Marcos Rieper

  9. Olá, Tenho um arquivo do banco de dados do ACCESS – ACCDB com mais de 1 milhão de linhas, preciso abri-lo em excel…Porém, não consigo exportar todas as linhas…Pode me ajudar?

  10. Bruno disse:

    Como faço para deixar as colunas alinhadas ?

    • Marcos Rieper disse:

      Olá Bruno,

      Você quer dizer ajustar a largura das colunas automaticamente?

      Duas formas:

      1. Selecione a planilha toda e clique entre duas colunas com o mouse, ele reajustará automaticamente.
      2. Use o código abaixo, pode usar o procedimento ou colocar no final do código estas linhas no procedimento que irá funcionar também.

      Sub lsAutoAjustar()
      Cells.Select
      Cells.EntireColumn.AutoFit
      End Sub

      Abraço
      Marcos Rieper

  11. Alexandre disse:

    Olá Marcos ! tenho um loop que gera combinações de numeros em uma coluna !
    Gostaria de gerar varios milhoes de linhas ! dois ou tres milhoes !
    Li seu código, mas não consegui adptar para gerar as combinações em mais de uma planilha !
    Poderia me ajudar ?

  12. Alexandre disse:

    Bom dia !
    Tenho um loop que gera mais de milhao de linhas em uma planilha ! gostaria que gerasse mais que isso abrindo outra planilha quando chegasse na ultima linha ! tentei aproveitar seu código mais não consegui !
    Poderia me ajudar ?

    Att.
    Alexandre Dias

  13. Mayara Lisboa disse:

    Eu preciso com urgência saber transformar um arquivo dat em excel ou ASCII que contém mais do que o limite de linhas do excel….
    Alguém pode me ajudar pf!

  14. Ricardo disse:

    Olá Marcos,
    preciso de ajuda com programas que possam abrir arquivos txt grandes, se possível dar uma dica
    att
    Ricardo

  15. José disse:

    Formatei meu notebook e minhas planilhas que executavam a função VExtenso estão com erro #nome?, como recuperar?

  16. Nathalia Caram disse:

    Marcos, bom dia. Estou fazendo minha monografia e ela é sobre a Contabilidade Pública, estou utilizando dados enormes que a União disponibiliza. Eu achei muito útil e maravilhosa a planilha que você criou. Porém, nos meus dados mais recentes eu percebi que ela não tá lendo o arquivo por completo.. o arquivo ao todo tem mais de 1 milhão de linhas, quando eu abro, ele ta abrindo apenas 177 mil. Você conseguiria me ajudar? Obrigada.

  17. Daiana disse:

    Olá Marcos,

    Gostaria de agradecer por ter disponibilizado o macro. Ele me salvou a vida! 🙂

  18. CLAUDEMIR oliveira lima disse:

    estou tentando criar planilha de probabilidade ma o excel tem poucas linhas e poucas colunas . tem com aumentar esse limite de linas e coolunas ou tem outros programas para este fim.

    muito obrigado

  19. Karen disse:

    Marcos,

    Quando eu abro a janela da macro me dá um erro “Este comendo irá para o depurador” e ele vai para o código como se tivesse um erro nesta linha:
    If Dir(lsCaminho) “” Then

    Alguma ideia do porque isso acontece? O que eu estou fazendo de errado?

    Karen

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.