Статия
Защо Excel обърква вашите CSV файлове
ID на пациент, започващ с три нули. Ген, наречен SEPT2. Шестнадесетцифрен номер на сметка. Отворете кой да е от тях в електронна таблица като обикновен CSV файл и импортерът на Excel не просто ги показва, той ги пренаписва: нулите изчезват, името на гена се превръща в дата, а номерът на сметката губи последните си няколко цифри. Нищо от това не е точно бъг, това е, което се случва, когато универсален предполагач на числа и дати срещне текст, който само прилича на число или дата. Ето какво всъщност се случва с CSV файл в момента, в който Excel го отвори, и как да го спрете, преди да се случи.
Водещите нули изчезват
Въведете 02134 в клетка и подразбиращият се тип колона на Excel, General, го чете като число и премахва водещата нула: 2134. Това засяга всеки код, съхраняван като цифри, но не е наистина количество: американски пощенски кодове, френски пощенски кодове, номера на служители или фактури, телефонни номера. Технически нищо не се губи от основния CSV файл, текстът до клетката остава непроменен, докато не запазите работната книга, момент, в който Excel записва обратно каквото е показал, и нулите изчезват завинаги. Решението е да кажете на Excel, че колоната е текст, преди да получи шанс да гадае: чрез Data > From Text/CSV (или Get Data > From File) можете да зададете тип данни на всяка колона изрично, или да добавите апостроф преди стойност в единична клетка, за да наложите текст.
Текстът се превръща в дати без питане
Същият предполагач търси и текст с форма на дата. Клетка, съдържаща MAR1 или SEPT2, прочетена като 1 март или 2 септември, се конвертира тихомълком в стойност дата и се преформатира, без да се покаже предупреждение. Това не е хипотетично: през 2020 г. Комитетът за номенклатура на човешките гени (HUGO) преименува около 27 символа на човешки гени, включително SEPT2 и MARCH1, конкретно защото софтуерът за електронни таблици постоянно ги превръщаше в дати всеки път, когато набор от данни бъде отворен, проблем достатъчно сериозен, че проучване от 2016 г. на геномни статии откри грешки от автокорекция като тази в приблизително всяка пета публикация, изпратила придружаващи Excel файлове. Урокът се обобщава отвъд имената на гени: всеки кратък буквено-цифров код, който случайно прилича на ден и месец, е изложен на риск в момента, в който някой отвори вашия CSV в електронна таблица, не само вашия.
Много дългите числа губят последните си цифри
Excel съхранява всяко число като стойност с плаваща запетая с 15 значещи цифри точност, ограничение, което Microsoft документира директно. Шестнадесетцифрен номер на карта или сметка като 4111111111111111 се закръгля, за да пасне, така че последната цифра или две тихомълком се превръщат в нула и стойността вече не е тази, която сте въвели. Форматирането на клетката след това няма да върне липсващите цифри, защото основното съхранено число вече е закръглено; единственото решение е да импортирате колоната като текст, или да добавите апостроф пред стойността, преди Excel изобщо да я третира като число.
Текстът с ударения се превръща в безсмислица
Кодирането е отделен режим на провал от автокорекцията. CSV файл без маркер за реда на байтовете е обикновени байтове; ако е бил записан като UTF-8, Excel под Windows обикновено ще го отвори, предполагайки собствената кодова страница на системата вместо това, така че букви с ударения, валутни символи и емоджита излизат като мойджибаке, низ като café се показва като нещо по-близо до café. Безопасният път при изготвяне на CSV за Excel е да го запазите като CSV UTF-8 или да добавите UTF-8 маркер за реда на байтовете; безопасният път при консумиране на такъв е да импортирате чрез Data > From Text/CSV, което ви позволява да изберете изходното кодиране изрично, вместо да разчитате на двойно щракване да познае правилно.
Преместване на данни между формати, без да отваряте Excel
Нищо от това не е наистина бъг на Excel, това е, което предполагачът на числа и дати на електронна таблица е изграден да прави, и важи независимо как се отваря файлът, не само в Excel. Най-безопасният начин да преобразувате CSV файл или да проверите дали колона наистина остава текст, преди да стигне до електронна таблица, е да работите директно с него: нашият конвертор на данни разбира CSV със стандартен парсър, съобразен с кавички, и никога не преинтерпретира типа на стойност, така че пощенски код или име на ген остава точно текстът, който сте му дали. Работи изцяло в браузъра ви, нищо, което поставите, не се качва никъде. Това обаче защитава само стъпката на конвертиране: ако резултатът все пак ще бъде отворен в електронна таблица след това, маркирайте чувствителните колони като текст и там.
Инструменти от тази статия
Често задавани въпроси
Как да спра Excel да премахва водещите нули от CSV?
Не отваряйте файла с двойно щракване. Импортирайте го вместо това чрез Data > From Text/CSV (или Get Data), което показва избор на тип данни колона по колона; задайте колоната с пощенски код, ID или телефонен номер на Text, преди да завършите импорта. Бърза поправка за отделна клетка е да въведете апостроф преди стойността, като '02134, което налага текст, без да променя показваното.
Защо Excel превърна моето ID или код в дата?
Подразбиращият се тип колона General на Excel активно търси текст с форма на дата, шаблони като кратка дума плюс число, или две числа, разделени с тире или наклонена черта, и конвертира всичко, което съвпада, без да пита за потвърждение. Точно това наложи преименуването на генни символи като SEPT2 и MARCH1 през 2020 г. Импортирайте колоната като Text, по същия начин както при водещите нули, за да спрете предположението, преди да се случи.
Избягва ли конвертирането на моя CSV по друг начин тези проблеми напълно?
Избягва ги за тази стъпка на конвертиране, не завинаги. Парсър, който третира всяко поле като обикновен текст, като нашия конвертор на данни, няма да пренапише пощенски код или име на ген, докато превръща CSV в JSON. Но ако резултатът все пак се озове отворен в електронна таблица по-късно, от вас или от този, който го получава, собственият импортер на онази програма получава друг шанс да преинтерпретира стойностите, така че чувствителните колони трябва да се маркират като текст и там.