Como dividir uma enorme planilha CSV Excel em arquivos separados

outubro 31, 2020 gurinho

O Microsoft Excel é excelente em muitas tarefas do dia-a-dia. Mas de vez em quando, você encontra uma de suas deficiências: o tamanho de uma planilha. Continue lendo para descobrir como diminuir o tamanho de uma planilha do Excel ou dividir um arquivo CSV grande em vários arquivos.

Por que dividir um CSV grande em vários arquivos?

Você pode estar pensando: "Por que preciso dividir um arquivo grande do Excel em vários arquivos menores?" É uma pergunta interessante, especialmente porque o Excel tem um limite de linhas de planilha de 1.048.576.

Mais de 1 milhão de linhas parece fenomenal. No entanto, atingir o limite da linha é mais fácil do que você pensa, especialmente durante certas tarefas.

Por exemplo, se você estiver fazendo marketing por e-mail, poderá importar um arquivo CSV contendo milhões de endereços de e-mail. O único problema é: como você gerencia uma planilha com tantos endereços? Além disso, e se alguém enviar a você um CSV que já ultrapassou o limite (de outro programa)?

Se isso soa como um problema que você provavelmente enfrentará, verifique as cinco maneiras a seguir de dividir um arquivo CSV ou Excel grande em vários arquivos menores.

Não tem um arquivo CSV grande à mão, mas quer jogar em casa? Estou usando o conjunto de dados COVID-19 Open Research nos exemplos, que você também pode baixar e usar.

1. Divida arquivos CSV usando um programa

Existem vários programas divisores CSV úteis por aí. Aqui estão dois dos melhores. Um aviso justo, porém, como esses programas estão funcionando, às vezes eles têm problemas de memória, que é um problema comum para programas de divisão de CSV.

Divisor CSV grátis

O Free Huge CSV Splitter é uma ferramenta básica de divisão de CSV. Você insere o arquivo CSV que deseja dividir, a contagem de linhas que deseja usar e, a seguir, seleciona Dividir Arquivo . A contagem de linhas determina o número de arquivos de saída com os quais você termina.

CSV Splitter

CSV Splitter é a segunda ferramenta. Ele oferece praticamente a mesma funcionalidade do Free Huge CSV Splitter, embora com um design um pouco mais elegante. Ele divide seu CSV em pequenos pedaços rapidamente, permitindo que você determine a contagem de linha que deseja usar.

2. Use um arquivo de lote

Em seguida, crie um arquivo em lote programável . Você pode usar um arquivo em lote para processar o CSV em partes menores, personalizando o arquivo para entregar partes diferentes.

Abra um novo documento de texto, copie e cole o seguinte:

 @echo off
 setlocal ENABLEDELAYEDEXPANSION
 REM Edit this value to change the name of the file that needs splitting. Include the extension.
 SET BFN=HCAHPSHospital.csv
 REM Edit this value to change the number of lines per file.
 SET LPF=2500
 REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.
 SET SFN=HosptialSplitFile
 REM Do not change beyond this line.
 SET SFX=%BFN:~-3%
 SET /A LineNum=0
 SET /A FileNum=1
 For /F "delims==" %%l in (%BFN%) Do (
 SET /A LineNum+=1
 echo %%l >> %SFN%!FileNum!.%SFX%
 if !LineNum! EQU !LPF! (
 SET /A LineNum=0
 SET /A FileNum+=1
 )
 )
 endlocal
 Pause

Você precisará configurar o arquivo em lote antes de executar. Vou lhe dizer o que cada comando faz e você pode alterá-lo para se adequar ao tamanho do seu arquivo em lote, bem como a saída necessária.

"SET BFN =" deve apontar para o CSV que você precisa dividir
" SET LPF =" é o número de linhas que você gostaria de limitar ao seu novo arquivo
"SET SFN =" é o novo esquema de nomenclatura para seus arquivos divididos

Depois de inserir suas variáveis, vá para Arquivo> Salvar como . Escolha um nome de arquivo e selecione Salvar . Em seguida, selecione o arquivo de texto recém-salvo e pressione F2 para renomeá-lo. Substitua a extensão .txt por .bat e pressione OK quando o aviso aparecer. Agora, você pode dividir seu arquivo CSV grande em arquivos de saída menores.

3. Use um script do PowerShell para dividir um arquivo CSV

Você pode usar arquivos em lote para uma ampla variedade de tarefas do dia-a-dia . Mas os scripts do PowerShell são mais rápidos, especialmente para esse tipo de processamento e divisão.

O script a seguir corta rapidamente seu CSV grande em arquivos menores.

Primeiro, pressione CTRL + X para abrir o Windows Power Menu e selecione PowerShell . Se o PowerShell não for uma opção, insira o PowerShell na barra de pesquisa do Menu Iniciar e selecione a Melhor Correspondência.

Agora, copie e cole o seguinte script:

 $InputFilename = Get-Content 'C:filelocation'
 $OutputFilenamePattern = 'output_done_'
 $LineLimit = 50000
 $line = 0
 $i = 0
 $file = 0
 $start = 0
 while ($line -le $InputFilename.Length) {
 if ($i -eq $LineLimit -Or $line -eq $InputFilename.Length) {
 $file++
 $Filename = "$OutputFilenamePattern$file.csv"
 $InputFilename[$start..($line-1)] | Out-File $Filename -Force
 $start = $line;
 $i = 0
 Write-Host "$Filename"
 }
 $i++;
 $line++
 }

Substitua o local do arquivo na primeira linha pelo seu arquivo CSV e execute o script. O script gera os arquivos CSV menores no diretório do usuário. Por exemplo, meus arquivos CSV são encontrados em C: Users Gavin com o nome de arquivo output_done_1.csv . Você pode mudar o nome da saída alterando a linha $ OutputFilenamePattern = 'output_done_' .

Você pode encontrar o roteiro original em SPJeff .

4. Divida um CSV grande usando o Power Pivot

Sua penúltima solução para quebrar um arquivo CSV grande em pequenos pedaços não o divide. Em vez disso, permite que você carregue seu enorme arquivo CSV no Excel e use a ferramenta Power Pivot para abri-lo. Está certo; você pode ignorar efetivamente o limite de linha do Excel e gerenciar o arquivo dentro do programa.

Você consegue isso criando um link de dados para o arquivo CSV e, em seguida, usando o Power Pivot para gerenciar o conteúdo. Para uma explicação completa e tutorial, leia o blog de José Barreto detalhando o processo.

Resumindo, Barreto cria uma Tabela Dinâmica usando "até 8,5 milhões de linhas sem nenhum problema". A imagem acima vem de uma postagem do blog, mostrando um total de 2 milhões de linhas em uso no Excel.

Lembre-se de que esse processo não divide o CSV em pequenos pedaços. No entanto, isso significa que você pode manipular o CSV no Excel, o que é uma alternativa muito útil. Se precisar de mais dicas, descubra como usar uma Tabela Dinâmica para análise de dados .

5. Divida CSV grande online usando CSV dividido

Existem também serviços online que dividem seu grande arquivo CSV em pedaços menores. Uma dessas opções é o Split CSV , um divisor CSV online gratuito.

O CSV dividido gerenciou o conjunto de dados COVID-19 muito bem, dividindo-o em blocos úteis. Como com outras ferramentas, você define a contagem de linhas para cada arquivo e permite que ele seja dividido. No entanto, eu não tinha um arquivo CSV grande para testar e, portanto, sua experiência pode variar.

O CSV dividido também inclui opções premium. Por uma taxa de assinatura, você pode usar um delimitador personalizado , escolher uma seleção de tipos de arquivo de saída, remover certos caracteres dos arquivos de saída e excluir linhas duplicadas.

Divida seus arquivos CSV em blocos facilmente gerenciáveis

Agora você tem cinco soluções para quebrar seus arquivos CSV em pequenos pedaços, tornando-os fáceis de gerenciar. As soluções variam em velocidade e no tamanho dos arquivos CSV que podem gerenciar, portanto, talvez seja necessário fazer experiências para encontrar a solução que funciona melhor para você.

Crédito da imagem: lucadp / Depositphotos