Excel в CSV без кракозябр: UTF-8, BOM и разделители

Большинство «кракозябр» в CSV возникает из-за несовпадения кодировки: файл сохранён в UTF-8, а открывается в одной из кодировок Windows. Экспортируйте таблицу как CSV UTF-8 с меткой порядка байтов (BOM), если файл будет открывать Excel, и выберите разделитель, принятый в вашем регионе. Перед экспортом форматируйте столбцы с почтовыми индексами и телефонами как текст, чтобы нули сохранились.

Excel в CSVНужен CSV, а файл в ExcelСделать бесплатно

Почему «é» превращается в «Ã©»

Символы в файле хранятся как байты. В UTF-8 буква é занимает два байта: C3 и A9. Программа, которая читает эти байты как Windows-1252, распространённую в Windows кодировку, видит символ à вместо C3 и © вместо A9. Поэтому «café» выглядит как «café». Проверить это можно самостоятельно: закодировав é в UTF-8 и прочитав результат как Windows-1252, вы получите é. Так же ломаются ü, ñ и ö.

Исправлять нужно не символы, а настройку: укажите программе, в какой кодировке записан файл.

UTF-8 и BOM

UTF-8 — стандартная кодировка текста в вебе и в большинстве современного ПО. В обычном CSV-файле нет поля, которое сообщает кодировку, поэтому программы её угадывают. Метка порядка байтов (BOM) — это три байта в самом начале файла, EF BB BF, по которым программы распознают UTF-8. Excel в Windows часто открывает файл UTF-8 без BOM в локальной кодировке, поэтому BOM — частое решение.

В новых версиях Excel в меню «Сохранить как» есть вариант CSV UTF-8, который добавляет BOM. Также можно импортировать файл через «Данные» → «Из текста/CSV» и в поле «Источник файла» указать 65001, UTF-8. У BOM есть минус: программа, которая его не ожидает, может принять его за часть имени первого столбца. Большинство парсеров и табличных программ справляются с ним, но если импортёр данных выдаёт странный заголовок, уберите BOM.

Запятая или точка с запятой

CSV — это сокращение от comma-separated values, то есть значения, разделённые запятыми, и спецификация RFC 4180 описывает поля, разделённые запятыми. Во многих странах, включая те, где принята десятичная запятая, как в 1,5 для полутора, разделителем списков в Excel является точка с запятой. Тогда файл с запятыми, открытый в Excel, может разбиться неправильно, а число вроде 1,5 превратится в два поля.

Выбирайте разделитель, который ожидает целевая программа. Инструмент Excel в CSV предлагает запятую, точку с запятой и табуляцию. Если файл предназначен для программы в регионе с десятичной запятой, точка с запятой обычно надёжнее.

Даты превращаются в порядковые числа

Excel хранит даты как порядковые номера, отсчитывая дни от начальной точки. В системе дат 1900 года 1 января 1900 — это номер 1. Excel также считает 1900 год високосным, поэтому учитывает 29 февраля 1900 года, которого не было, и присваивает ему номер 60. Из-за этого 1 марта 1900 года получает номер 61, и каждая последующая дата сдвигается на один день. Ошибка пошла из Lotus 1-2-3, а Microsoft сохранила эту систему для совместимости. Другое ПО может читать даты до 1 марта 1900 года с ошибкой в один день.

При экспорте вы можете увидеть 44927 вместо 2023-01-01 или сдвиг на день у старых дат. Инструмент превращает даты в понятный текст. Если экспортируете вручную, отформатируйте столбец с датами как текст в формате ISO yyyy-mm-dd, который все программы читают одинаково.

Ведущие нули пропадают

Почтовый индекс вроде 02134 — это текст, но Excel считает введённое число числом и показывает 2134. Ноль пропадает из таблицы, и CSV, записанный из неё, тоже его не содержит. Так же ведут себя телефонные номера, начинающиеся с 0, и длинные идентификаторы. Очень длинные числа могут терять цифры, потому что Excel сохраняет только около 15 значащих цифр.

Решение: сохраняйте такие столбцы как текст до ввода или импорта данных. Если вы открываете CSV, в котором нули уже есть, используйте «Данные» → «Из текста/CSV» и задайте столбцу тип «Текст», а не открывайте файл двойным щелчком.

Правила кавычек

RFC 4180 определяет общий формат CSV. Его основные правила для кавычек:

• Поле, содержащее запятую, двойную кавычку или перенос строки, нужно заключить в двойные кавычки.
• Двойная кавычка внутри заключённого в кавычки поля записывается как две двойные кавычки.
• В спецификации строки завершаются CRLF, хотя большинство инструментов принимают и LF.

Например, строка "Müller, Anna","She said ""ok""" содержит два поля: Müller, Anna и She said "ok". Зарегистрированный в IANA MIME-тип для CSV — text/csv.

RFC 4180 не допускает двойную кавычку внутри незаключённого в кавычки поля, и многие парсеры отвергают такую строку или разбирают её неверно. Заключите поле в кавычки правильно или удалите лишний символ.

Чек-лист перед экспортом

1. Экспортируйте как CSV UTF-8, с BOM, если файл будет открывать Excel.
2. Выберите разделитель, который ожидает целевая программа.
3. Форматируйте столбцы с идентификаторами, такими как почтовые индексы и телефоны, как текст.
4. Записывайте даты в формате yyyy-mm-dd.
5. Откройте файл в простом текстовом редакторе и проверьте первую строку и несколько имён с акцентами.

Скрытые символы, например неразрывные пробелы, тоже могут вызывать ошибки импорта. Инструмент «Удаление невидимых символов» находит и удаляет их, а статья о скрытых символах в тексте объясняет, откуда они берутся. Когда файл очищен, CSV в JSON поможет конвертировать его для разработчика, как описано в статье CSV в JSON и обратно.

Перед экспортом стоит проверить и сами ячейки: проверка формул в Excel показывает список формул, которые выводят ошибку или ссылаются на другой файл, а именно такие ячейки попадают в CSV пустыми. После экспорта сравните две таблицы, чтобы увидеть, что изменилось между версиями.

Быстрые ответы

Почему файл выглядит нормально в одной программе и сломанным в Excel? Если в файле UTF-8 нет BOM, Excel в Windows может прочитать его в своей локальной кодировке. Добавление BOM или импорт с выбранной UTF-8 решают проблему.

Нужен ли BOM для Excel на Mac? Некоторые источники утверждают, что да, поэтому проверьте файл с именем с акцентом, прежде чем отправлять его.

Можно ли исправить даты после экспорта? Да, если они ещё в виде порядковых чисел, но лучше сразу экспортировать их в формате yyyy-mm-dd.

Загружается ли моя таблица на сервер при конвертации? Инструмент работает в браузере, поэтому файл обрабатывается на вашем устройстве. Проверить это можно по руководству о проверке загрузки файлов.