Ensure

Proč se vaše CSV v Excelu otevře špatně

Čárky, středníky a tabulátory podle lokalizace, k tomu uvozování, escapování, BOM v UTF-8 a spolehlivý způsob, jak zjistit, který oddělovač soubor doopravdy používá.

Excel se do souboru .csv nedívá, aby zjistil, čím je oddělený. Když na něj dvakrát kliknete, použije oddělovač seznamu z místního nastavení vašeho operačního systému, a pokud ten souboru neodpovídá, skončí každý řádek v jediném sloupci.

Ve Spojeném království a ve Spojených státech je oddělovačem seznamu čárka. Ve většině kontinentální Evropy a ve velké části Latinské Ameriky, kde se desetinná tečka píše jako čárka, je oddělovačem seznamu středník. Platný soubor oddělený čárkami poslaný z Londýna se tak v Amsterdamu, Berlíně nebo Madridu otevře jako jeden slepený sloupec a soubor se středníky to udělá obráceně. Nic není poškozené; soubor se jen čte se špatným předpokladem.

Tři způsoby, jak ho otevřít správně

  1. Importujte místo otevírání. V Excelu Data, pak Načíst data nebo Z textu/CSV, a v dialogu vyberte oddělovač a kódování. Tohle je jediná cesta, která vám dá plnou kontrolu, a jediná, která přežije odeslání do jiné země.
  2. Přejmenujte soubor na .txt. U souboru .txt Excel odmítá hádat a místo toho zobrazí průvodce importem, který se vás na oddělovač zeptá.
  3. Převeďte soubor na oddělovač, který příjemce očekává, ještě než ho odešlete. Pokud víte, že příjemce má lokalizaci se středníkem, pošlete mu soubor se středníky.

Řádek sep=

Excel i LibreOffice rozumějí speciálnímu prvnímu řádku, který nastavení lokalizace přebije:

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

Funguje to a je to běžné řešení situace „klient nemůže otevřít můj export“. Mějte na paměti, že jde o konvenci tabulkových procesorů, ne o součást nějaké specifikace CSV. Předhoďte tentýž soubor skriptu nebo importu do databáze a sep=; se přečte jako obyčejný první řádek, obvykle se z něj stane nesmyslná hlavička sloupce. Používejte ho pro soubory určené člověku s tabulkovým procesorem, ne pro výměnu mezi stroji.

Uvozování a escapování

Neformální referencí pro CSV je RFC 4180, které běžnou praxi spíš popisuje, než by ji předepisovalo. Jeho pravidla jsou krátká:

  • Pole se musí obalit dvojitými uvozovkami, pokud obsahuje oddělovač, dvojitou uvozovku nebo zalomení řádku.
  • Dvojitá uvozovka uvnitř uvozeného pole se píše dvakrát.
id,name,note
1,"Smith, Ada","She said ""hello"" twice"
2,"Multi
line note",fine

Řádek 1 má tři pole, ne čtyři, protože čárka sedí uvnitř uvozovek. Zdvojené uvozovky se při parsování sloučí do jednoduchých. Řádek 2 obsahuje skutečné zalomení řádku uvnitř uvozeného pole, což je legální, a proto nelze CSV spolehlivě dělit počítáním řádků.

Escapování zpětným lomítkem (\, nebo \") je jiná konvence, kterou používají některé databázové nástroje. Není to RFC 4180 a striktní parser jí nebude rozumět, takže pokud váš soubor zpětná lomítka používá, řekněte to výslovně tomu, co ho čte.

Nejčastější poškození, na jaké v praxi narazíte, je soubor sestavený spojováním řetězců, kde hodnota obsahující čárku nebo zatoulanou uvozovku nebyla vůbec uvozena. Vzniknou tak řádky s více poli, než má hlavička, což je přesně to, na co validátor ukáže.

BOM a proč se z písmen s diakritikou stává mojibake

Byte order mark (BOM) jsou tři neviditelné bajty na úplném začátku souboru, které ho označují jako UTF-8. Excel na Windows na něm historicky staví: bez BOM se může vrátit ke staršímu regionálnímu kódování, takže se Müller zobrazí jako Müller a café jako café.

Nevýhodou je, že ostatní parsery ho vždy neodstraní. Pokud váš skript hlásí první sloupec pojmenovaný \ufeffid místo id, nebo pokud vyhledání podle "id" záhadně selže jen u prvního sloupce, může za to BOM. Přečtěte soubor s kódováním utf-8-sig a zmizí.

Praktické pravidlo: do souborů určených k otevření v Excelu BOM přidejte, ze souborů určených k parsování softwarem ho vynechte.

Konce řádků

Nástroje na Windows píší na konec každého řádku \r\n, unixové nástroje \n. Většina parserů zvládne obojí, ale naivní dělení podle \n nechá na konci každého řádku návrat vozíku přilepený k poslednímu poli, a proto se hodnota, která vypadá jako UK, při porovnání odmítá rovnat "UK".

Jak poznat, který oddělovač soubor skutečně používá

Otevřete soubor v prostém textovém editoru, nikdy v tabulkovém procesoru, protože ten už svůj odhad udělal. Pak:

  1. Podívejte se na první dva nebo tři řádky. Který z kandidátů (čárka, středník, tabulátor, svislítko) se objevuje mezi tím, co jsou zjevně hodnoty polí?
  2. Spočítejte každého kandidáta na několika řádcích. Skutečný oddělovač dá na každém řádku stejný počet, o jedna menší než počet sloupců. Znak, který se na jednom řádku objeví sedmkrát a na dalším dvakrát, jsou data, ne struktura.
  3. Počítejte mimo uvozené úseky. V "Smith, Ada" je čárka obsahem a její naivní započítání je to, kvůli čemu čárky vypadají nekonzistentně v souboru, který je skutečně oddělený čárkami.
  4. Dejte pozor na desetinné čárky. Evropský soubor má často zároveň středníkové oddělovače a čárky uvnitř čísel, jako v Ada;1.234,56;UK. Pokud čárky vidíte vždy jen mezi číslicemi, jsou to desetinné oddělovače, ne oddělovače polí.
  5. Než z něčeho vyvodíte závěr, zkontrolujte první bajty na řádek sep= nebo na BOM.

Tabulátory si zaslouží zvláštní zmínku. Soubor oddělený tabulátory je obecně nejbezpečnější věc, jakou lze tabulkovému procesoru předat, protože tabulátory se uvnitř skutečných hodnot objevují jen velmi zřídka a Excel si s nimi přes import dobře poradí. Háček je v tom, že tabulátory nejsou vidět, takže soubor, ve kterém někdo zarovnal sloupce mezerami, vypadá stejně a naparsuje se na nic použitelného.

Jakmile oddělovač znáte, je převod souboru mechanický: načtěte ho se správným oddělovačem a uvozováním a pak ho zapište s tím, který očekává cíl, nebo ho převeďte do JSON a otázku oddělovače přeskočte úplně.