Почему Excel портит ваш CSV
Почтовые индексы теряют ведущий ноль, артикулы превращаются в даты, длинные идентификаторы незаметно округляются. Это происходит при открытии, а не при сохранении, — поэтому многие так и не понимают, что пошло не так.
Проверено
Порча происходит, когда вы открываете файл
Именно это делает проблему такой трудной для диагностики. CSV — текстовый файл: каждое значение в нём — это символы, и в файле нигде не сказано, что они означают. Открывая его, Excel угадывает тип каждого столбца, преобразует значения под этот тип и с этого момента хранит уже преобразованную версию. Сохраните файл — и преобразования запишутся обратно.
Итак, исходный файл был в порядке, сохранённый — уже нет, и ничто ни разу вас не предупредило. Люди решают, что сломан экспорт, выгружают файл заново и получают тот же результат, — потому что порча происходит на их собственном компьютере, уже после скачивания.
Четыре преобразования, которые всё портят
Удаляются ведущие нули. Почтовый индекс 01234, код французского департамента, номер счёта, телефон с нулём в начале — всё это похоже на числа, поэтому ноль отбрасывается как незначащий. А он значащий: это часть идентификатора.
Длинные числа округляются. Электронная таблица хранит числа с плавающей точкой — около пятнадцати значащих цифр, — поэтому номер заказа, номер банковской карты, IMEI или 64-битный идентификатор незаметно округляются: последние цифры становятся нулями. Ячейка выглядит как число, и это не то число.
Всё, что похоже на дату, переформатируется по местным правилам. 03/05 в одной стране — март, в другой — май, так что один и тот же файл, открытый в двух офисах, даёт два разных набора данных. Хуже того, в даты превращаются значения, которые датами никогда не были: название гена вроде SEPT1 становится датой — проблема оказалась настолько живучей, что генетики в итоге переименовали гены, чтобы не бороться с ней дальше.
Буквы с диакритикой превращаются в «кракозябры». CSV никак не хранит сведения о кодировке, поэтому файл, сохранённый в UTF-8 и прочитанный в старой кодовой странице (или наоборот), портит ровно те строки, где есть имена с такими буквами.
Решение: импортируйте, а не открывайте
Никогда не открывайте двойным щелчком CSV, который вам дорог. Двойной щелчок разрешает Excel угадывать, а угадывает он уверенно.
Вместо этого используйте Данные → Из текстового/CSV-файла. В окне импорта можно задать тип каждого столбца до того, как что-либо будет разобрано: отметьте столбец с индексами и столбец с идентификаторами как Текстовый, и они придут ровно такими, как в файле. Там же можно указать кодировку и разделитель, а не полагаться на догадки. Это занимает двадцать секунд, и это всё решение.
В Google Таблицах то же самое: Файл → Импортировать, и снимите флажок «Преобразовывать текст в числа, даты и формулы». LibreOffice Calc показывает окно выбора типов столбцов по умолчанию — одна из немногих областей, где он просто ведёт себя лучше.
Посмотрите файл до импорта
Если что-то уже пошло не так, первым делом полезно посмотреть на файл так, чтобы его не трогала электронная таблица. Просмотр CSV показывает значения ровно такими, какие они в байтах: ведущие нули на месте, длинные числа целы, даты — в том виде, в каком их записали. Сразу видно, что было неправильно — экспорт или импорт.
Там же видны две вещи, которые CSV никогда не сообщает о себе сам: разделитель — запятая, точка с запятой или табуляция (в европейских локалях экспортируют точку с запятой, потому что запятая у них — десятичный разделитель) — и кодировка. Зная обе до импорта, вы избавляетесь почти от всех оставшихся догадок.
Если CSV создаёте вы
Несколько решений на стороне экспорта предотвращают всё это на стороне получателя.
Записывайте UTF-8 с меткой порядка байтов (BOM), если файл предназначен для Excel в Windows. По этой метке Excel распознаёт UTF-8, а без неё портит буквы с диакритикой, — один из редких случаев, когда добавить BOM правильно, а не досадно.
Заключайте в кавычки каждое поле, которое можно прочитать неверно, а столбцы с идентификаторами — всегда. Кавычки не помешают Excel преобразовать значения при открытии, но сделают намерение однозначным для всех остальных программ.
Подумайте, нужен ли вообще CSV. Если получатель может принять JSON или настоящую электронную таблицу — оба формата явно хранят типы и лишены всех этих проблем. Достоинство CSV в том, что его читает всё; слабость — в том, что никто не согласен, что в нём написано.
Частые вопросы
Можно ли исправить порчу после сохранения?
Надёжно — нет. Удалённый ведущий ноль и округлённые цифры потеряны: в файле не из чего их восстановить. Выгрузите данные заново из исходного источника — поэтому исходный экспорт стоит хранить нетронутым, он стоит места на диске.
Почему мой CSV открывается одним длинным столбцом?
Разделитель не совпадает с тем, что ожидает таблица, — обычно это файл с точкой с запятой, открытый в локали с запятой, или наоборот. В окне импорта разделитель можно указать, и это быстрее, чем менять региональные настройки системы.
Что за странный символ в начале имени первого столбца?
Это метка порядка байтов (BOM), прочитанная как текст, а не как подсказка о кодировке. Та самая метка, которая исправляет буквы с диакритикой в Excel, — поэтому она и полезна, и раздражает. Большинство нормальных парсеров CSV удаляют её автоматически.
Существует ли стандарт CSV?
Только рекомендательная спецификация 2005 года, описывающая то, что делает большинство инструментов, — и множество программ, которые делают иначе. Поэтому разделители, кавычки, окончания строк и кодировки различаются — и поэтому на реальных файлах работает только один подход: определять их, а не предполагать.
Почему в текстовом редакторе одна строка разбивается на несколько?
Потому что поле в кавычках вполне законно может содержать переносы строк — обычно это поле с адресом. Запись всё равно остаётся одной строкой; всё, что делит файл по переводам строк, испортит именно такие записи. Поэтому делить CSV по запятым или переводам строк — неправильный способ его читать.