Perché il tuo CSV si apre male in Excel

Excel non guarda dentro un file .csv per capire come è separato. Quando ne apri uno con un doppio clic, usa il separatore di elenco definito nelle impostazioni internazionali del tuo sistema operativo, e se quello non corrisponde al file, ogni riga finisce in una sola colonna.

Nel Regno Unito e negli Stati Uniti il separatore di elenco è la virgola. In gran parte dell'Europa continentale e in buona parte dell'America Latina, dove il separatore decimale si scrive come una virgola, il separatore di elenco è il punto e virgola. Così un file valido separato da virgole spedito da Londra si apre come un'unica colonna schiacciata ad Amsterdam, a Berlino o a Madrid, e un file con i punti e virgola fa lo stesso nel verso opposto. Non c'è nulla di corrotto; il file viene semplicemente letto partendo dal presupposto sbagliato.

Tre modi per aprirlo correttamente

1. Importa invece di aprire. In Excel, Dati, poi Recupera dati oppure Da testo/CSV, e scegli il delimitatore e la codifica nella finestra di dialogo. È l'unica strada che ti dà pieno controllo, e l'unica che sopravvive a una spedizione in un altro paese.
2. Rinomina il file in .txt. Con un file .txt Excel si rifiuta di indovinare e mostra invece la procedura guidata di importazione, che ti chiede qual è il separatore.
3. Converti il file al separatore che si aspetta chi lo legge prima di spedirlo. Se sai che il destinatario ha una locale con il punto e virgola, mandagli un file con il punto e virgola.

La riga sep=

Sia Excel sia LibreOffice riconoscono una prima riga speciale che scavalca l'impostazione della locale:

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

Funziona, ed è un rimedio frequente al «il cliente non riesce ad aprire la mia esportazione». Tieni presente che è una convenzione dei fogli di calcolo, non fa parte di alcuna specifica CSV. Dai lo stesso file in pasto a uno script o a un'importazione in un database e sep=; verrà letto come una normale prima riga, che di solito diventa un'intestazione di colonna fasulla. Usalo per i file destinati a una persona con un foglio di calcolo, non per lo scambio fra macchine.

Quotatura ed escape

Il riferimento informale per il CSV è la RFC 4180, che descrive la pratica corrente invece di imporla. Le sue regole sono brevi:

• Un campo deve essere racchiuso fra virgolette doppie se contiene il separatore, una virgoletta doppia o un'interruzione di riga.
• Una virgoletta doppia all'interno di un campo fra virgolette si scrive due volte.

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

La riga 1 ha tre campi, non quattro, perché la virgola sta dentro le virgolette. Le virgolette raddoppiate si riducono a una sola in fase di parsing. La riga 2 contiene una vera interruzione di riga dentro un campo fra virgolette, il che è lecito ed è il motivo per cui non puoi dividere in modo affidabile un CSV contando le righe.

L'escape con la barra rovesciata (\, o \") è una convenzione diversa, usata da alcuni strumenti di database. Non è la RFC 4180 e un parser rigoroso non la capirà, quindi se il tuo file usa le barre rovesciate dichiaralo esplicitamente a qualsiasi cosa lo legga.

Il guasto più diffuso in circolazione è un file scritto per concatenazione di stringhe, dove un valore contenente una virgola o una virgoletta vagante non è mai stato messo fra virgolette. Il risultato sono righe con più campi dell'intestazione, che è esattamente il genere di cosa che un validatore ti mette sotto il naso.

Il BOM, e perché le lettere accentate diventano mojibake

Un byte order mark (BOM) è una sequenza di tre byte invisibili proprio all'inizio di un file, che lo contrassegnano come UTF-8. Excel su Windows storicamente ci ha fatto affidamento: senza un BOM può ripiegare su una vecchia codifica regionale, così Müller compare come Müller e café come café.

Il rovescio della medaglia è che altri parser non sempre lo tolgono. Se il tuo script riporta una prima colonna che si chiama \ufeffid invece di id, oppure se una ricerca su "id" fallisce misteriosamente solo sulla prima colonna, il colpevole è un BOM. Leggi il file con la codifica utf-8-sig e sparisce.

Regola pratica: includi il BOM nei file destinati a essere aperti in Excel, lascialo fuori dai file destinati a essere interpretati da un software.

Fine riga

Gli strumenti Windows scrivono \r\n alla fine di ogni riga, quelli Unix scrivono \n. La maggior parte dei parser gestisce entrambi, ma una divisione ingenua su \n lascia un ritorno a capo incollato all'ultimo campo di ogni riga, ed è il motivo per cui un valore che sembra UK si rifiuta di risultare uguale a "UK" in un confronto.

Come capire quale separatore usa davvero un file

Apri il file in un editor di testo semplice, mai in un foglio di calcolo, perché il foglio di calcolo ha già formulato la sua ipotesi. Poi:

1. Guarda le prime due o tre righe. Quale carattere candidato (virgola, punto e virgola, tabulazione, barra verticale) compare fra quelli che sono chiaramente valori di campo?
2. Conta ogni candidato su diverse righe. Il separatore vero dà lo stesso conteggio su ogni riga, uno in meno del numero di colonne. Un carattere che compare sette volte su una riga e due su quella dopo è dato, non struttura.
3. Fai quel conteggio fuori dalle sezioni fra virgolette. In "Smith, Ada" la virgola è contenuto, e contarla in modo ingenuo è ciò che fa sembrare incoerenti le virgole in un file che è davvero separato da virgole.
4. Fai attenzione alle virgole decimali. Un file europeo ha spesso sia separatori punto e virgola sia virgole dentro i numeri, come in Ada;1.234,56;UK. Se vedi virgole solo ed esclusivamente fra cifre, sono separatori decimali, non separatori di campo.
5. Controlla i primi byte alla ricerca di una riga sep= o di un BOM prima di concludere qualsiasi cosa.

Le tabulazioni meritano una menzione a parte. Un file separato da tabulazioni è in genere la cosa più sicura da consegnare a un foglio di calcolo, perché le tabulazioni non compaiono quasi mai dentro valori reali ed Excel le gestisce bene attraverso l'importazione. Il tranello è che le tabulazioni sono invisibili, quindi un file in cui qualcuno ha allineato le colonne con degli spazi ha lo stesso aspetto e in fase di parsing non produce nulla di utile.

Una volta che conosci il separatore, convertire il file è meccanico: leggilo con il delimitatore e le regole di quotatura corretti, poi riscrivilo con quello che si aspetta la tua destinazione, oppure convertilo in JSON e salta del tutto la questione del delimitatore.