Come pulire un file CSV disordinato

Perché le esportazioni arrivano interrotte

CSV è una famiglia di convenzioni piuttosto che un formato. Due applicazioni possono entrambe rivendicare il supporto di CSV e non essere d'accordo sul delimitatore, sullo stile delle virgolette, sulla fine della riga e sulla codifica dei caratteri.

Il delimitatore è la sorpresa più comune. Il software venduto nei paesi che utilizzano la virgola come separatore decimale spesso esporta file separati da punto e virgola, perché la virgola è già occupata. Quei file terminano ancora con .csv e uno strumento che presuppone le virgole comprime ogni riga in un'unica colonna.

La codifica è la seconda. Un file UTF-8 inizia spesso con un segno invisibile di ordine dei byte di tre byte. Excel su Windows lo utilizza per riconoscere UTF-8; molti altri strumenti non se lo aspettano, quindi viene incollato al nome della prima intestazione e una colonna chiamata id diventa silenziosamente una colonna a cui nessuna ricerca potrà mai corrispondere.

Diagnosi in tre numeri

Il rilevamento automatico analizza un campione con ciascun delimitatore candidato e sceglie quello che produce il risultato rettangolare più coerente, motivo per cui non viene ingannato dalle virgole all'interno dei campi tra virgolette di un file separato da punto e virgola. È giusto la maggior parte delle volte; l'anteprima ti dice quando non lo è.

• Conteggio delle colonne. Se è 1, il delimitatore è sbagliato. Prova con il punto e virgola, quindi con la tabulazione.
• Conteggio delle righe. Se è molto più alto del previsto, il file probabilmente contiene ritorni a capo tra virgolette che qualcosa a monte ha già alterato.
• Conteggio avvertimenti. Ogni avviso indica un numero di riga, conteggiato nel modo in cui conta un editor di testo, quindi puoi andare a guardare.

Leggere gli avvertimenti

Una riga irregolare significa che una riga aveva un numero di celle diverso rispetto all'intestazione. Nulla viene scartato per riordinare la griglia: le righe corte sono riempite con spazi vuoti e le righe lunghe mantengono i loro valori extra. Eliminare silenziosamente i dati di qualcuno per far sembrare rettangolare una tabella è la cosa peggiore che uno strumento di pulizia possa fare, quindi non viene eseguita.

Una virgoletta non chiusa significa che una virgoletta doppia è stata aperta e mai chiusa, quindi tutto ciò che è seguito è stato letto come un unico campo enorme. Si tratta quasi sempre di un vero e proprio danneggiamento dell'origine e vale la pena correggere il punto in cui è stato prodotto il file anziché applicare patch a valle.

Lo strumento li segnala invece di ripararli, perché entrambi i casi hanno diverse possibili soluzioni corrette e solo tu sai quale si applica.

Pulisci nel giusto ordine

L'esportazione sicura per l'iniezione di formule è attiva per impostazione predefinita ed è l'unica opzione con una reale conseguenza in termini di sicurezza. Una cella che inizia con =, +, - o @ viene trattata come una formula dalle applicazioni per fogli di calcolo, quindi un valore proveniente da una fonte non attendibile può essere eseguito quando qualcuno apre il file. L'esportazione sicura antepone a quelle celle un apostrofo, che impedisce loro di eseguire il calcolo, incluso quello che intendevi calcolare, il che è inerente alla mitigazione piuttosto che a un bug.

1. Taglia prima gli spazi bianchi. Due righe che differiscono solo per uno spazio finale non sono duplicate finché non le tagli.
2. Rimuovi le righe duplicate per secondo. La prima occorrenza viene mantenuta, quindi l'ordine sopravvissuto rimane invariato.
3. Rinomina le intestazioni e scegli le colonne da esportare.
4. Ordina se necessario. Le colonne numeriche vengono rilevate e ordinate numericamente, quindi 9 viene prima di 10.
5. Controlla le opzioni di esportazione prima del download, in particolare l'impostazione dell'iniezione della formula.

Scelta della codifica di esportazione

Attiva il segno dell'ordine dei byte UTF-8 se il file è destinato a Excel su Windows, che lo utilizza per rilevare la codifica; senza di esso, i caratteri accentati e gli script non latini possono apparire come mojibake con un doppio clic.

Lasciarlo OFF se il file alimenta uno script, un'importazione di database o un altro programma, perché alcuni parser rigorosi trattano il segno come dati.

Esempio realizzato: un'esportazione di 12,000-riga che si apre come una colonna

L'esportazione di un cliente da un CRM europeo si apre nel tuo editor come 12,000 righe e una singola colonna. La riga di intestazione riporta Nome; E-mail; Paese; Data di registrazione.

1. Carica il file. Rilevamento automatico colonne 4 dei rapporti; in caso contrario, impostare manualmente il delimitatore sul punto e virgola.
2. Leggi gli avvisi: ad esempio 3 righe irregolari in 418, 2,905 e 7,110.
3. Guarda quelle tre righe. Contengono un punto e virgola senza caratteri di escape all'interno del nome di un'azienda: le celle extra vengono mantenute, quindi nulla è andato perso.
4. Taglia gli spazi bianchi, quindi rimuovi i duplicati.
5. Esporta come CSV con la virgola come delimitatore e il segno dell'ordine dei byte attivo, perché il file verrà inviato a un collega che utilizza Excel.

Risultato: Un file UTF-8 separato da virgole con un contrassegno dell'ordine dei byte, quattro colonne e le righe duplicate scomparse. Le tre righe problematiche sono ancora presenti con le celle extra intatte, contrassegnate per essere risolte all'origine anziché eliminate per rendere la tabella ordinata.

Apri lo strumento

Pulisci un CSV nel tuo browser

Analizza in un Web Worker sul tuo dispositivo, conserva ogni cella che trova e segnala i problemi che non indovinerà.

Ciò che questo non copre

• L'intero file viene mantenuto nella memoria del dispositivo. Il limite è 50 MB e un telefono avrà difficoltà molto prima di un laptop.
• Nell'anteprima vengono mostrate solo le prime 100 righe, sebbene ogni riga venga pulita ed esportata.
• Il rilevamento della codifica è limitato al contrassegno dell'ordine dei byte UTF-8. Un file salvato come Windows-1252 o Shift-JIS viene letto come UTF-8 e può mostrare caratteri sostitutivi; riesportarlo come UTF-8 dall'applicazione di origine.
• Viene scritto solo UTF-8. Le codifiche legacy non vengono prodotte.
• Non è disponibile il supporto per più fogli, perché CSV non ha il concetto di fogli.
• Un file la cui quotazione è già danneggiata viene segnalato, non riparato.
• L'annullamento è limitato alle ultime operazioni 20.