Excel do CSV bez rozbité diakritiky: UTF-8, BOM a oddělovače

Většina rozbité diakritiky v CSV souboru pochází z nesouladu kódování: soubor je uložen jako UTF-8, ale otevřen jako některé windowsové kódování. Exportujte list jako CSV UTF-8 s bajtovou značkou (BOM), pokud soubor bude otevírat Excel, a zvolte oddělovač, který očekává vaše nastavení regionu. Před exportem naformátujte sloupce jako PSČ a telefonní čísla jako text, aby se nuly zachovaly.

Excel na CSVPotřebuji CSV, mám soubor ExceluVyzkoušet zdarma

Proč se z „é“ stane „é“

Znak je v souboru uložen jako bajty. V UTF-8 je písmeno é tvořeno dvěma bajty, C3 a A9. Program, který tyto bajty načte jako Windows-1252, běžné windowsové kódování, vidí pro C3 znak à a pro A9 znak ©. Slovo „café“ se tak zobrazí jako „café“. Můžete si to ověřit sami: zakódujete-li é do UTF-8 a výsledek dekódujete jako Windows-1252, dostanete é. Stejně se rozbije ü, ñ nebo ö.

Opravou není měnit znaky. Je třeba programu sdělit, jaké kódování soubor používá.

UTF-8 a BOM

UTF-8 je standardní kódování textu na webu a ve většině moderního softwaru. Prostý soubor CSV nemá pole, které by říkalo, jaké kódování používá, takže programy hádají. Bajtová značka (BOM) jsou tři bajty na úplném začátku souboru, EF BB BF, které programům, jež ji hledají, signalizují UTF-8. Excel ve Windows často otevře soubor UTF-8 bez BOM pomocí místního kódování, proto je BOM běžnou opravou.

V novějších verzích Excelu nabízí nabídka Uložit jako možnost CSV UTF-8, která BOM přidá. Soubor můžete také importovat přes Data > Z textu/CSV a v poli Původ souboru nastavit 65001, UTF-8. BOM má jednu nevýhodu: program, který ho nečeká, jej může považovat za součást názvu prvního sloupce. Většina parserů a tabulkových programů si s ním poradí, ale pokud importér dat ohlásí podivnou hlavičku, BOM odstraňte.

Čárka, nebo středník

CSV znamená hodnoty oddělené čárkou a specifikace RFC 4180 popisuje pole oddělená čárkami. Ve valné části zemí, včetně těch, které používají desetinnou čárku, jako je 1,5 pro jeden a půl, je oddělovačem seznamu v Excelu středník. Když se to stane, soubor oddělený čárkami otevřený v Excelu se může špatně rozdělit a číslo 1,5 se rozpadne na dvě pole.

Zvolte oddělovač, který očekává cílový program. Nástroj Excel na CSV nabízí čárku, středník i tabulátor. Je-li soubor určen pro program v prostředí s desetinnou čárkou, bývá bezpečnější volbou středník.

Data se mění na sériová čísla

Excel ukládá data jako sériová čísla počítaná od výchozího bodu. V systému dat z roku 1900 je 1. ledna 1900 sériové číslo 1. Excel také považuje rok 1900 za přestupný, takže počítá 29. února 1900, které nikdy neexistovalo, jako sériové číslo 60. Díky tomu má 1. března 1900 sériové číslo 61 a každé pozdější datum se posune o jeden den. Chyba pochází z Lotus 1-2-3 a Microsoft ji kvůli kompatibilitě zachoval. Jiný software může data před 1. březnem 1900 načíst o jeden den jinak.

Při exportu můžete vidět 44927 místo 2023-01-01, nebo u starých dat o den jinak. Nástroj převede data na čitelný text. Pokud exportujete ručně, naformátujte sloupec s daty jako text ve tvaru ISO yyyy-mm-dd, který každý program čte stejně.

Ztrácejí se úvodní nuly

PSČ jako 02134 je text, ale Excel zadané číslo bere jako číslo, takže zobrazí 2134. Nula je z listu pryč a CSV zapsané z něj ji také nebude mít. Stejně dopadají telefonní čísla začínající nulou a dlouhá identifikační čísla. Velmi dlouhá čísla mohou ztratit i číslice, protože Excel si udrží jen přibližně 15 platných číslic.

Oprava spočívá v tom, že sloupce uložíte jako text ještě před zadáním nebo importem dat. Otevíráte-li CSV, které už nuly obsahuje, použijte Data > Z textu/CSV a sloupci nastavte Text, místo dvojkliku na soubor.

Pravidla pro uvozovky

RFC 4180 definuje běžný formát CSV. Jeho hlavní pravidla pro uvozovky jsou:

• Pole obsahující čárku, dvojitou uvozovku nebo zalomení řádku musí být uzavřeno do dvojitých uvozovek.
• Dvojitá uvozovka uvnitř uzavřeného pole se zapisuje jako dvě dvojité uvozovky.
• Specifikace uvádí konce řádků jako CRLF, ačkoli většina nástrojů přijímá LF.

Například řádek "Müller, Anna","She said ""ok""" má dvě pole: Müller, Anna a She said "ok". Typ MIME pro CSV registrovaný u IANA je text/csv.

RFC 4180 nepovoluje dvojitou uvozovku uvnitř neuzavřeného pole a mnoho parserů takový řádek odmítne nebo rozdělí špatně. Pole řádně uzavřete, nebo nadbytečný znak odstraňte.

Kontrolní seznam před exportem

1. Exportujte jako CSV UTF-8, s BOM, pokud soubor bude otevírat Excel.
2. Zvolte oddělovač, který očekává cílový program.
3. Sloupce s čísly podobnými identifikátorům, jako PSČ a telefonní čísla, naformátujte jako text.
4. Zapisujte data jako yyyy-mm-dd.
5. Soubor otevřete v prostém textovém editoru a zkontrolujte první řádek a vzorek jmen s diakritikou.

Skryté znaky, například nezalomitelné mezery, mohou také způsobit chyby importu. Nástroj Odstranit skryté znaky je najde a odstraní a skryté znaky v textu vysvětluje, odkud se berou. Až bude soubor čistý, může jej pro vývojáře převést Převod CSV na JSON, jak je popsáno v článku Převod CSV na JSON a zpět.

Před exportem stojí za to podívat se i na buňky: kontrola vzorců v Excelu vypíše vzorce, které zobrazují chybu nebo odkazují na jiný soubor, a právě tyto buňky v CSV dorazí prázdné. Po exportu porovnejte tabulky a zjistíte, co se mezi verzemi změnilo.

Rychlé odpovědi

Proč můj soubor vypadá v jednom programu správně a v Excelu rozbitě? Pokud UTF-8 soubor nemá BOM, může ho Excel ve Windows číst pomocí svého místního kódování. Přidání BOM nebo import s vybraným UTF-8 to opraví.

Potřebují verze Excelu pro Mac BOM? Některé zdroje tvrdí, že ano, takže před odesláním otestujte soubor s jménem obsahujícím diakritiku.

Mohu data opravit až po exportu? Ano, pokud jsou stále sériová čísla, ale je lepší exportovat je jako yyyy-mm-dd od začátku.

Nahrává se moje tabulka při převodu na server? Nástroj běží ve vašem prohlížeči, takže se soubor zpracovává na vašem vlastním zařízení. Můžete si to ověřit podle návodu, jak zjistit, zda online nástroj nahrává soubory.