Статья
Почему Excel портит ваши CSV-файлы
Идентификатор пациента, начинающийся с трёх нулей. Ген с названием SEPT2. Шестнадцатизначный номер счёта. Откройте любой из них в таблице как обычный CSV-файл, и импортёр Excel не просто отображает их, он переписывает: нули исчезают, название гена превращается в дату, а номер счёта теряет несколько последних цифр. Формально это не совсем ошибка, это то, что происходит, когда универсальный угадыватель чисел и дат встречает текст, который лишь выглядит как число или дата. Вот что на самом деле происходит с CSV-файлом в момент, когда его открывает Excel, и как это остановить заранее.
Ведущие нули исчезают
Введите 02134 в ячейку, и стандартный тип столбца Excel, «Общий», прочитает это как число и отбросит ведущий ноль: 2134. Это затрагивает любой код, который хранится цифрами, но по сути не является количеством: почтовые индексы США, французские почтовые индексы, табельные номера или номера счетов, телефонные номера. Формально из исходного CSV-файла ничего не теряется, текст рядом с ячейкой остаётся неизменным, пока вы не сохраните книгу: в этот момент Excel запишет обратно то, что он отображал, и нули исчезнут навсегда. Решение: сообщить Excel, что столбец текстовый, ещё до того, как он получит шанс угадать: через «Данные > Из текста/CSV» (или «Получить данные > Из файла») можно явно задать тип данных для каждого столбца, либо поставить апостроф перед значением в отдельной ячейке, чтобы принудительно сделать его текстом.
Текст без спроса превращается в даты
Тот же угадыватель также ищет текст, похожий на дату. Ячейка со значением 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; безопасный путь при получении такого файла: импортировать через «Данные > Из текста/CSV», что позволяет явно выбрать исходную кодировку вместо того, чтобы доверить двойному щелчку угадать правильно.
Перенос данных между форматами без открытия в Excel
На самом деле ничего из этого не является настоящей ошибкой Excel, это то, для чего и создан угадыватель чисел и дат в таблице, и это применимо к любому способу открытия файла, не только в Excel. Самый надёжный способ преобразовать CSV-файл или убедиться, что столбец действительно остаётся текстом до того, как он попадёт в любую таблицу, это работать с ним напрямую: наш конвертер данных разбирает CSV стандартным парсером с учётом кавычек и никогда не переосмысливает тип значения, так что почтовый индекс или название гена остаются ровно тем текстом, который вы задали. Он работает целиком в браузере, ничего из вставленного вами никуда не загружается. Правда, это защищает только сам шаг конвертации: если результат всё равно потом откроется в таблице, чувствительные столбцы нужно будет там тоже пометить как текст.
Инструменты из этой статьи
Частые вопросы
Как остановить удаление ведущих нулей из CSV в Excel?
Не открывайте файл двойным щелчком. Вместо этого импортируйте его через «Данные > Из текста/CSV» (или «Получить данные»), где показывается выбор типа данных для каждого столбца; установите тип «Текст» для столбца с почтовым индексом, идентификатором или номером телефона перед завершением импорта. Быстрое решение для отдельной ячейки: поставить апостроф перед значением, вот так '02134, что принудительно делает его текстом, не меняя отображение.
Почему Excel превратил мой идентификатор или код в дату?
Тип столбца «Общий» в Excel активно ищет текст, похожий на дату, шаблоны вроде короткого слова плюс число или двух чисел, разделённых дефисом или косой чертой, и преобразует всё подходящее без запроса подтверждения. Именно это вынудило переименовать символы генов вроде SEPT2 и MARCH1 в 2020 году. Импортируйте столбец как «Текст», так же как и для ведущих нулей, чтобы предотвратить это угадывание заранее.
Избавляет ли конвертация CSV другим способом от этих проблем полностью?
Она избавляет от них только на этом шаге конвертации, не навсегда. Парсер, который обрабатывает каждое поле как обычный текст, как наш конвертер данных, не станет переписывать почтовый индекс или название гена при превращении CSV в JSON. Но если результат всё же в итоге откроется в таблице, вами или тем, кто его получит, собственный импортёр этой программы получит новый шанс переосмыслить значения, так что чувствительные столбцы нужно будет пометить как текст и там тоже.