Почему CSV файлы ломаются в Excel: Кодировка UTF-8, разделители и ведущие нули
Содержание
- Прямая диагностика: Формат файла против автоопределения Excel
- Ловушка «кракозябр» (Mojibake): UTF-8 против UTF-8 с BOM
- Конфликт запятой и точки с запятой (Все данные в столбце A)
- Кавычки и переводы строк внутри полей (Стандарт RFC 4180)
- Удаление ведущих нулей и ограничение точности в 15 знаков
- Официальный порядок импорта в Microsoft Excel (Power Query)
- Локальные браузерные инструменты GoToolstack для CSV
- Технические стандарты и официальные спецификации
1. Прямая диагностика: Формат файла против автоопределения Excel
Файл CSV (значения, разделенные запятыми) представляет собой обычный неформатированный текстовый поток. Он содержит только символы, разделители полей и переводы строк. Когда CSV открывается в Excel с искаженными буквами, всеми данными в столбце A или усеченными номерами карт, сам исходный файл почти всегда исправен. Проблема заключается в алгоритмах автоматического угадывания Excel при двойном клике.
Двойной клик заставляет Excel угадывать кодировку и разделители на основе региональных параметров Windows. Чтобы решить проблему навсегда, необходимо учитывать четыре технических правила:
2. Ловушка «кракозябр» (Mojibake): UTF-8 против UTF-8 с BOM
Современные веб-приложения и базы данных экспортируют текст в стандартной кодировке UTF-8 без метки порядка байтов (BOM). Однако при двойном клике в русской версии Windows настольный Excel по умолчанию использует кодовую страницу Windows-1251 (ANSI).
Без 3-байтовой сигнатуры (BOM: 0xEF, 0xBB, 0xBF) в самом начале файла Excel считывает двухбайтовые русские буквы как отдельные символы ANSI, превращая текст в нечитаемые «кракозябры»:
| Исходный текст | Искажение в Excel (без BOM) | Причина сбоя |
|---|---|---|
München / İstanbul |
München / İstanbul |
UTF-8 2-byte → ANSI Mojibake |
Français / Español |
Français / Español |
Diacritics parsed as individual code points |
€ 1,500 / ₺ 5,000 / ¥ 10,000 |
€ 1,500 / ₺ 5,000 / ¥ 10,000 |
Multi-byte currency symbols decoded into ANSI artifacts |
При подготовке CSV для пользователей Excel всегда добавляйте метку UTF-8 BOM. В конвертере JSON ↔ CSV от GoToolstack достаточно отметить пункт Добавить UTF-8 BOM (\uFEFF) для Excel.
3. Конфликт запятой и точки с запятой (Все данные в столбце A)
В США и Великобритании десятичные дроби разделяются точкой (12.50), а элементы списков — запятой. В России и странах Европы десятичным разделителем по закону является запятая (12,50).
Поскольку запятая не может одновременно быть десятичным знаком и разделителем колонок, в Windows для этих регионов стандартным разделителем списков назначена точка с запятой (;). Открывая стандартный CSV с запятыми по двойному клику, Excel не находит точек с запятой и помещает всю строку целиком в Столбец A.
Экспортируйте данные с точкой с запятой или используйте мастер Excel Данные > Из текста/CSV, где можно явно выбрать запятую.
4. Кавычки и переводы строк внутри полей (Стандарт RFC 4180)
Если значение ячейки содержит запятую, точку с запятой или перенос строки, стандарт RFC 4180 требует заключать поле в двойные кавычки. Внутренние кавычки экранируются удвоением (""):
ID,Customer,Address,Notes
101,"Acme, Inc.","123 Industrial Way
Suite 400","Order confirmed with ""Express"" shipping"5. Удаление ведущих нулей и ограничение точности в 15 знаков
При чтении CSV Excel пытается автоматически угадать тип данных. Если ячейка состоит только из цифр, Excel преобразует ее в число с плавающей запятой, что приводит к двум невосполнимым потерям данных:
Удаление ведущих нулей: Индексы, ИНН и номера телефонов (например, "01234") преобразуются в числа (1234). Начальные нули навсегда удаляются из памяти.
Ограничение точности в 15 знаков (Excel против IEEE 754): Вычислительный движок Microsoft Excel ограничивает числовую точность ровно 15 значащими цифрами. Хотя стандарт IEEE 754 для чисел с двойной точностью поддерживает целые числа до 253 (9 007 199 254 740 992, то есть 16 знаков) без потерь, Excel по внутреннему регламенту заменяет все цифры после 15-й на нули (1234567890123456 → 1234567890123450)!
ВАЖНОЕ ПРЕДУПРЕЖДЕНИЕ: Изменение формата ячейки на «Текстовый» после открытия файла не восстановит утерянные цифры. Значения были уничтожены в памяти сразу при загрузке.
6. Официальный порядок импорта в Microsoft Excel (Power Query)
Чтобы корректно открыть любой CSV-файл без потери цифр и искажения кодировки, используйте Power Query:
- Откройте чистую книгу Excel (не нажимайте дважды на файл CSV).
- Перейдите на вкладку Данные > Получить данные > Из файла > Из текстового/CSV-файла.
- В окне предварительного просмотра укажите Источник файла:
65001: Unicode (UTF-8). - Выберите правильный Разделитель (запятая или точка с запятой).
- Нажмите Преобразовать данные. В редакторе Power Query смените тип данных столбцов с номерами карт и телефонами на Текст.
- Нажмите Закрыть и загрузить, чтобы перенести точные данные на лист.
- Сохраните книгу в формате книги Excel (
.xlsx), а не перезаписывайте исходный CSV-файл (raw original CSV), чтобы сохранить исходные данные в безопасности.
7. Локальные браузерные инструменты GoToolstack для CSV
Обрабатывайте табличные данные прямо в браузере без отправки на серверы:
- Конвертер JSON ↔ CSV: Добавление UTF-8 BOM, выбор разделителей и настройка переводов строк CRLF/LF.
- Конвертер Excel ↔ CSV/JSON: Локальная конвертация через SheetJS в памяти устройства.
- Центр инструментов разработчика: Полная коллекция утилит форматирования данных.
Технические стандарты и официальные спецификации
- IETF RFC 4180: Общий формат и MIME-тип для файлов значений, разделенных запятыми (CSV) (Правила кавычек и CRLF).
- Microsoft Learn / Support: Импорт или экспорт текстовых файлов (.txt или .csv) в Microsoft Excel (Сохранение типов данных через Power Query).
- Unicode Consortium: Часто задаваемые вопросы – Руководство по UTF-8, UTF-16 и метке порядка байтов (BOM).