Стаття
Чому 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 стандартним парсером, що враховує лапки, і ніколи не перевизначає тип значення, тож поштовий індекс чи назва гена залишаються точно тим текстом, який ви передали. Він повністю працює у вашому браузері, нічого з того, що ви вставляєте, нікуди не завантажується. Але це захищає лише етап конвертації: якщо результат усе одно буде відкрито в електронній таблиці потім, позначте чутливі стовпці як текст і там теж.
Інструменти з цієї статті
Поширені запитання
Як зупинити видалення Excel провідних нулів із CSV?
Не відкривайте файл подвійним кліком. Натомість імпортуйте його через «Дані > З тексту/CSV» (чи «Отримати дані»), що показує вибір типу даних по кожному стовпцю; встановіть стовпець поштового індексу, ID чи телефону на «Текст» до завершення імпорту. Швидке виправлення для окремої комірки: ввести апостроф перед значенням, наприклад '02134, що примусово робить текст, не змінюючи те, що показується.
Чому Excel перетворив мій ID чи код на дату?
Тип стовпця «Загальний» в Excel активно шукає текст, схожий на дату, шаблони на кшталт короткого слова плюс число, чи двох чисел, розділених тире чи скісною рискою, і перетворює будь-що, що збігається, без жодного підтвердження. Саме це змусило перейменувати генні символи, такі як SEPT2 і MARCH1, у 2020 році. Імпортуйте стовпець як текст, так само, як і для провідних нулів, щоб зупинити здогадку до того, як вона станеться.
Чи повністю уникає цих проблем конвертація мого CSV іншим способом?
Це уникає їх для цього конкретного етапу конвертації, не назавжди. Парсер, що трактує кожне поле як звичайний текст, як-от наш конвертер даних, не перепише поштовий індекс чи назву гена, перетворюючи CSV на JSON. Але якщо результат усе одно потім відкриють в електронній таблиці, ви чи той, хто його отримає, власний імпортер тієї програми отримує ще один шанс перевизначити значення, тож чутливі стовпці потрібно позначити як текст і там теж.