Porque é que o seu CSV abre mal no Excel

O Excel não olha para dentro de um ficheiro .csv para perceber como está separado. Quando faz duplo clique num deles, usa o separador de lista das definições regionais do seu sistema operativo e, se esse separador não corresponder ao ficheiro, cada linha vai parar a uma única coluna.

No Reino Unido e nos Estados Unidos o separador de lista é a vírgula. Na maior parte da Europa continental e em boa parte da América Latina, onde a casa decimal se escreve com vírgula, o separador de lista é o ponto e vírgula. Assim, um ficheiro válido separado por vírgulas enviado de Londres abre como uma única coluna esmagada em Amesterdão, Berlim ou Madrid, e um ficheiro com ponto e vírgula faz o mesmo no sentido inverso. Nada está corrompido; o ficheiro está a ser lido com o pressuposto errado.

Três formas de o abrir corretamente

1. Importe em vez de abrir. No Excel, Dados, depois Obter Dados ou De Texto/CSV, e escolha o delimitador e a codificação na caixa de diálogo. É o único caminho que lhe dá controlo total e o único que sobrevive ao envio para outro país.
2. Mude o nome do ficheiro para .txt. O Excel recusa-se a adivinhar num ficheiro .txt e mostra o assistente de importação, que lhe pergunta qual é o separador.
3. Converta o ficheiro para o separador que o destinatário espera antes de o enviar. Se sabe que quem o recebe usa uma localidade de ponto e vírgula, envie-lhe um ficheiro com ponto e vírgula.

A linha sep=

Tanto o Excel como o LibreOffice compreendem uma primeira linha especial que anula a definição da localidade:

sep=;
name;email;country
Ada;ada@example.com;UK

Funciona, e é uma solução frequente para o "o cliente não consegue abrir a minha exportação". Tenha presente que é uma convenção das folhas de cálculo e não faz parte de nenhuma especificação de CSV. Passe o mesmo ficheiro a um script ou a uma importação de base de dados e sep=; é lido como uma primeira linha vulgar, transformando-se em geral num cabeçalho de coluna falso. Use-a em ficheiros destinados a uma pessoa com uma folha de cálculo, não para troca entre máquinas.

Aspas e escapes

A referência informal para o CSV é o RFC 4180, que descreve a prática corrente em vez de a impor. As suas regras são curtas:

• Um campo tem de ser envolvido em aspas duplas se contiver o separador, uma aspa dupla ou uma quebra de linha.
• Uma aspa dupla dentro de um campo entre aspas escreve-se duas vezes.

id,name,note
1,"Smith, Ada","She said ""hello"" twice"
2,"Multi
line note",fine

A linha 1 tem três campos e não quatro, porque a vírgula está dentro de aspas. As aspas duplicadas reduzem-se a uma só na leitura. A linha 2 contém uma quebra de linha verdadeira dentro de um campo entre aspas, o que é válido e é a razão por que não se pode dividir um CSV de forma fiável a contar linhas.

O escape com barra invertida (\, ou \") é uma convenção diferente, usada por algumas ferramentas de bases de dados. Não é RFC 4180 e um analisador estrito não a compreende, por isso, se o seu ficheiro usar barras invertidas, diga-o explicitamente a quem o vai ler.

A avaria mais comum por esse mundo fora é um ficheiro escrito por concatenação de cadeias, em que um valor com uma vírgula ou uma aspa perdida nunca chegou a ser posto entre aspas. Isso produz linhas com mais campos do que o cabeçalho, que é exatamente o género de coisa para que um validador aponta.

O BOM, e porque é que os carateres acentuados se transformam em mojibake

Uma marca de ordem de bytes (BOM) são três bytes invisíveis mesmo no início de um ficheiro que o assinalam como UTF-8. O Excel em Windows dependeu historicamente dela: sem BOM, pode recuar para uma codificação regional antiga, e então Müller aparece como Müller e café como café.

A contrapartida é que outros analisadores nem sempre a removem. Se o seu script indicar uma primeira coluna chamada \ufeffid em vez de id, ou se uma procura por "id" falhar misteriosamente apenas na primeira coluna, o culpado é um BOM. Leia o ficheiro com a codificação utf-8-sig e ele desaparece.

Regra prática: inclua o BOM em ficheiros destinados a ser abertos no Excel e deixe-o de fora nos ficheiros destinados a ser lidos por software.

Fins de linha

As ferramentas Windows escrevem \r\n no fim de cada linha, as ferramentas Unix escrevem \n. A maioria dos analisadores lida com ambos, mas uma divisão ingénua por \n deixa um retorno de carro colado ao último campo de cada linha, e é por isso que um valor que parece UK se recusa a ser igual a "UK" nas comparações.

Como descobrir que separador um ficheiro usa de facto

Abra o ficheiro num editor de texto simples, nunca numa folha de cálculo, porque a folha de cálculo já fez o seu palpite. Depois:

1. Olhe para as duas ou três primeiras linhas. Qual dos carateres candidatos (vírgula, ponto e vírgula, tabulação, barra vertical) aparece entre aquilo que são claramente valores de campos?
2. Conte cada candidato em várias linhas. O separador verdadeiro dá a mesma contagem em todas as linhas, uma unidade abaixo do número de colunas. Um caráter que aparece sete vezes numa linha e duas na seguinte é dado, não estrutura.
3. Faça essa contagem fora das secções entre aspas. Em "Smith, Ada" a vírgula é conteúdo, e contá-la ingenuamente é o que faz as vírgulas parecerem inconsistentes num ficheiro que é genuinamente separado por vírgulas.
4. Atenção às vírgulas decimais. Um ficheiro europeu tem muitas vezes separadores de ponto e vírgula e vírgulas dentro dos números, como em Ada;1.234,56;UK. Se as vírgulas só aparecerem entre dígitos, são casas decimais e não separadores.
5. Verifique os primeiros bytes à procura de uma linha sep= ou de um BOM antes de concluir seja o que for.

As tabulações merecem uma menção especial. Um ficheiro separado por tabulações é normalmente a coisa mais segura para entregar a uma folha de cálculo, porque as tabulações quase nunca aparecem dentro de valores reais e o Excel lida bem com elas pela via da importação. O senão é que as tabulações são invisíveis, por isso um ficheiro em que alguém alinhou as colunas com espaços parece igual e não se lê como nada de útil.

Depois de saber qual é o separador, converter o ficheiro é mecânico: leia-o com o delimitador e as aspas corretos e escreva-o depois com aquilo que o destino espera, ou converta-o para JSON e evite por completo a questão do delimitador.