Como limpar um arquivo CSV bagunçado

Por que as exportações chegam quebradas

CSV é uma família de convenções e não um formato. Dois aplicativos podem reivindicar suporte CSV e discordar sobre o delimitador, o estilo de citação, o final da linha e a codificação de caracteres.

O delimitador é a surpresa mais comum. Software vendido em países que usam vírgula como separador decimal geralmente exporta arquivos separados por ponto e vírgula, porque a vírgula já está ocupada. Esses arquivos ainda terminam em .csv, e uma ferramenta que assume vírgulas recolhe todas as linhas em uma única coluna.

A codificação é a segunda. Um arquivo UTF-8 freqüentemente começa com uma marca invisível de ordem de bytes de três bytes. Excel no Windows utiliza-o para reconhecer UTF-8; muitas outras ferramentas não esperam isso, então ele fica colado ao primeiro nome do cabeçalho e uma coluna chamada id silenciosamente se torna aquela que nenhuma pesquisa jamais corresponderá.

Diagnosticar em três números

A detecção automática analisa uma amostra com cada delimitador candidato e escolhe aquele que produz o resultado retangular mais consistente, e é por isso que não é enganada por vírgulas dentro dos campos entre aspas de um arquivo separado por ponto e vírgula. Está certo na maioria das vezes; a visualização informa quando não está.

• Contagem de colunas. Se for 1, o delimitador está errado. Tente ponto e vírgula e depois tabulação.
• Contagem de linhas. Se for muito maior do que o esperado, o arquivo provavelmente contém novas linhas entre aspas que algo do upstream já destruiu.
• Contagem de avisos. Cada aviso nomeia um número de linha, contado da mesma forma que um editor de texto, para que você possa ir e olhar.

Lendo os avisos

Uma linha irregular significa que uma linha tinha um número de células diferente do cabeçalho. Nada é descartado para arrumar a grade: linhas curtas são preenchidas com espaços em branco e linhas longas mantêm seus valores extras. Excluir silenciosamente os dados de alguém para fazer uma tabela parecer retangular é a pior coisa que uma ferramenta de limpeza pode fazer, por isso não é feita.

Uma aspa não fechada significa que uma aspa dupla foi aberta e nunca fechada, então tudo depois dela foi lido como um campo enorme. Isso quase sempre é uma corrupção genuína na fonte e vale a pena corrigir onde o arquivo foi produzido, em vez de corrigir o downstream.

A ferramenta relata isso em vez de repará-los, porque ambos os casos têm várias soluções corretas possíveis e só você sabe qual delas se aplica.

Limpe na ordem certa

A exportação segura para injeção de fórmula está ativada por padrão e é a única opção aqui com um impacto de segurança genuíno. Uma célula que começa com =, +, - ou @ é tratada como uma fórmula pelos aplicativos de planilha, portanto, um valor que chega de uma fonte não confiável pode ser executado quando alguém abrir seu arquivo. A exportação segura prefixa essas células com um apóstrofo, o que as impede de calcular – incluindo qualquer um que você pretenda calcular, o que é inerente à mitigação e não a um bug.

1. Apare os espaços em branco primeiro. Duas linhas que diferem apenas por um espaço à direita não são duplicadas até que você as corte.
2. Remova as linhas duplicadas em segundo lugar. A primeira ocorrência é mantida, portanto a ordem sobrevivente permanece inalterada.
3. Renomeie os cabeçalhos e escolha as colunas a serem exportadas.
4. Classifique se precisar. As colunas numéricas são detectadas e classificadas numericamente, portanto, 9 vem antes de 10.
5. Verifique as opções de exportação antes de baixar, principalmente a configuração de injeção de fórmula.

Escolhendo a codificação de exportação

Ative a marca de ordem de bytes UTF-8 se o arquivo for destinado ao Excel no Windows, que a utiliza para detectar a codificação; sem ele, caracteres acentuados e escritas não latinas podem aparecer como mojibake com um clique duplo.

Deixe OFF se o arquivo alimenta um script, uma importação de banco de dados ou outro programa, pois alguns analisadores rigorosos tratam a marca como dados.

Exemplo resolvido: uma exportação de 12,000 linhas que abre como uma coluna

Uma exportação de cliente de um CRM europeu abre em seu editor como 12,000 linhas e uma única coluna. A linha do cabeçalho diz Nome;E-mail;País;Data de inscrição.

1. Carregue o arquivo. Detecção automática de colunas de relatórios 4; caso contrário, defina o delimitador para ponto e vírgula manualmente.
2. Leia os avisos: diga 3 linhas irregulares em 418, 2,905 e 7,110.
3. Olhe para essas três linhas. Eles contêm um ponto e vírgula sem escape dentro do nome da empresa – as células extras são mantidas, então nada foi perdido.
4. Corte os espaços em branco e remova as duplicatas.
5. Exporte como CSV com vírgula como delimitador e marca de ordem de bytes ativada, pois o arquivo vai para um colega que usa Excel.

Resultado: Um arquivo UTF-8 separado por vírgula com uma marca de ordem de bytes, quatro colunas e sem linhas duplicadas. As três linhas problemáticas ainda estão presentes com suas células extras intactas, sinalizadas para você corrigir na origem, em vez de excluí-las para deixar a tabela organizada.

Abra a ferramenta

Limpe um CSV no seu navegador

Analisa um Web Worker no seu dispositivo, mantém todas as células que encontra e relata problemas que não consegue adivinhar.

O que isso não cobre

• O arquivo inteiro é mantido na memória do dispositivo. A tampa é 50 MB e um telefone terá problemas muito antes de um laptop.
• Somente as primeiras 100 linhas são mostradas na visualização, embora cada linha seja limpa e exportada.
• A detecção de codificação é limitada à marca de ordem de bytes UTF-8. Um arquivo salvo como Windows-1252 ou Shift-JIS é lido como UTF-8 e pode mostrar caracteres de substituição; exporte-o novamente como UTF-8 do aplicativo de origem.
• Apenas UTF-8 está escrito. Codificações legadas não são produzidas.
• Não há suporte para múltiplas planilhas, porque CSV não tem conceito de planilhas.
• Um arquivo cuja cotação já está corrompida é relatado e não reparado.
• Desfazer está limitado às últimas operações 20.