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 немає поля, яке вказувало б кодування, тому програми вгадують. Маркер порядку байтів — це три байти на самому початку файлу, EF BB BF, які сигналізують програмам, що їх шукають, про UTF-8. Excel у Windows часто відкриває файл UTF-8 без BOM у місцевому кодуванні, тож BOM — поширене виправлення.

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

Кома чи крапка з комою

CSV означає «значення, розділені комами», і специфікація 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". MIME-тип для CSV, зареєстрований IANA, — 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.

Чи завантажується моя таблиця під час конвертації? Інструмент працює у вашому браузері, тож файл обробляється на вашому пристрої. Перевірити це можна за допомогою посібника з перевірки завантаження.