Редактируем CSV-файлы, чтобы не сломать данные



    Продукты HFLabs в промышленных объемах обрабатывают данные: адреса, ФИО, реквизиты компаний и еще вагон всего. Естественно, тестировщики ежедневно с этими данными имеют дело: обновляют тест-кейсы, изучают результаты очистки. Часто заказчики дают «живую» базу, чтобы тестировщик настроил сервис под нее.

    Первое, чему мы учим новых QA — сохранять данные в первозданном виде. Все по заветам: «Не навреди». В статье я расскажу, как аккуратно работать с CSV-файлами в Excel и Open Office. Советы помогут ничего не испортить, сохранить информацию после редактирования и в целом чувствовать себя увереннее.

    Материал базовый, профессионалы совершенно точно заскучают.

    Что такое CSV-файлы


    Формат CSV используют, чтобы хранить таблицы в текстовых файлах. Данные очень часто упаковывают именно в таблицы, поэтому CSV-файлы очень популярны.


    CSV-файл состоит из строк с данными и разделителей, которые обозначают границы столбцов

    CSV расшифровывается как comma-separated values — «значения, разделенные запятыми». Но пусть название вас не обманет: разделителями столбцов в CSV-файле могут служить и точки с запятой, и знаки табуляции. Это все равно будет CSV-файл.

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

    Из-за своих преимуществ CSV — сверхпопулярный формат обмена данными, хотя ему уже лет 40. CSV используют прикладные промышленные программы, в него выгружают данные из баз.

    Одна беда — текстового редактора для работы с CSV мало. Еще ничего, если таблица простая: в первом поле ID одной длины, во втором дата одного формата, а в третьем какой-нибудь адрес. Но когда поля разной длины и их больше трех, начинаются мучения.


    Следить за разделителями и столбцами — глаза сломаешь

    Еще хуже с анализом данных — попробуй «Блокнотом» хотя бы сложить все числа в столбце. Я уж не говорю о красивых графиках.

    Поэтому CSV-файлы анализируют и редактируют в Excel и аналогах: Open Office, LibreOffice и прочих.

    Ветеранам, которые все же дочитали: ребята, мы знаем об анализе непосредственно в БД c помощью SQL, знаем о Tableau и Talend Open Studio. Это статья для начинающих, а на базовом уровне и небольшом объеме данных Excel с аналогами хватает.

    Как Excel портит данные: из классики


    Все бы ничего, но Excel, едва открыв CSV-файл, начинает свои лукавые выкрутасы. Он без спроса меняет данные так, что те приходят в негодность. Причем делает это совершенно незаметно. Из-за этого в свое время мы схватили ворох проблем.

    Большинство казусов связано с тем, что программа без спроса преобразует строки с набором цифр в числа.

    Округляет. Например, в исходной ячейке два телефона хранятся через запятую без пробелов: «5235834,5235835». Что сделает Excel? Лихо превратит номера́ в одно число и округлит до двух цифр после запятой: «5235834,52». Так мы потеряем второй телефон.

    Приводит к экспоненциальной форме. Excel заботливо преобразует «123456789012345» в число «1,2E+15». Исходное значение потеряем напрочь.

    Проблема актуальна для длинных, символов по пятнадцать, цифровых строк. Например, КЛАДР-кодов (это такой государственный идентификатор адресного объекта: го́рода, у́лицы, до́ма).

    Удаляет лидирующие плюсы. Excel считает, что плюс в начале строки с цифрами — совершенно лишний символ. Мол, и так ясно, что число положительное, коль перед ним не стоит минус. Поэтому лидирующий плюс в номере «+74955235834» будет отброшен за ненадобностью — получится «74955235834». (В реальности номер пострадает еще сильнее, но для наглядности обойдусь плюсом).

    Потеря плюса критична, например, если данные пойдут в стороннюю систему, а та при импорте жестко проверяет формат.

    Разбивает по три цифры. Цифровую строку длиннее трех символов Excel, добрая душа, аккуратно разберет. Например, «8 495 5235834» превратит в «84 955 235 834».

    Форматирование важно как минимум для телефонных номеров: пробелы отделяют коды страны и города от остального номера и друг от друга. Excel запросто нарушает правильное членение телефона.

    Удаляет лидирующие нули. Строку «00523446» Excel превратит в «523446».
    А в ИНН, например, первые две цифры — это код региона. Для Республики Алтай он начинается с нуля — «04». Без нуля смысл номера исказится, а проверку формата ИНН вообще не пройдет.

    Меняет даты под локальные настройки. Excel с удовольствием исправит номер дома «1/2» на «01.фев». Потому что Windows подсказал, что в таком виде вам удобнее считывать даты.

    Побеждаем порчу данных правильным импортом


    Если серьезно, в бедах виноват не Excel целиком, а неочевидный способ импорта данных в программу.

    По умолчанию Excel применяет к данным в загруженном CSV-файле тип «General» — общий. Из-за него программа распознает цифровые строки как числа. Такой порядок можно победить, используя встроенный инструмент импорта.

    Запускаю встроенный в Excel механизм импорта. В меню это «Data → Get External Data → From Text».

    Выбираю CSV-файл с данными, открывается диалог. В диалоге кликаю на тип файла Delimited (с разделителями). Кодировка — та, что в файле, обычно определяется автоматом. Если первая строка файла — шапка, отмечаю «My Data Has Headers».

    Перехожу ко второму шагу диалога. Выбираю разделитель полей (обычно это точка с запятой — semicolon). Отключаю «Treat consecutive delimiters as one», а «Text qualifier» выставляю в «{none}». (Text qualifier — это символ начала и конца текста. Если разделитель в CSV — запятая, то text qualifier нужен, чтобы отличать запятые внутри текста от запятых-разделителей.)

    На третьем шаге выбираю формат полей, ради него все и затевалось. Для всех столбцов выставляю тип «Text». Кстати, если кликнуть на первую колонку, зажать шифт и кликнуть на последнюю, выделятся сразу все столбцы. Удобно.

    Дальше Excel спросит, куда вставлять данные из CSV — можно просто нажать «OK», и данные появятся в открытом листе.


    Перед импортом придется создать в Excel новый workbook

    Но! Если я планирую добавлять данные в CSV через Excel, придется сделать еще кое-что.

    После импорта нужно принудительно привести все-все ячейки на листе к формату «Text». Иначе новые поля приобретут все тот же тип «General».

    • Нажимаю два раза Ctrl+A, Excel выбирает все ячейки на листе;
    • кликаю правой кнопкой мыши;
    • выбираю в контекстном меню «Format Cells»;
    • в открывшемся диалоге выбираю слева тип данных «Text».


    Чтобы выделить все ячейки, нужно нажать Ctrl+A два раза. Именно два, это не шутка, попробуйте

    После этого, если повезет, Excel оставит исходные данные в покое. Но это не самая твердая гарантия, поэтому мы после сохранения обязательно проверяем файл через текстовый просмотрщик.

    Альтернатива: Open Office Calc


    Для работы с CSV-файлами я использую именно Calc. Он не то чтобы совсем не считает цифровые данные строками, но хотя бы не применяет к ним переформатирование в соответствии с региональными настройками Windows. Да и импорт попроще.

    Конечно, понадобится пакет Open Office (OO). При установке он предложит переназначить на себя файлы MS Office. Не рекомендую: хоть OO достаточно функционален, он не до конца понимает хитрое микрософтовское форматирование документов.

    А вот назначить OO программой по умолчанию для CSV-файлов — вполне разумно. Сделать это можно после установки пакета.

    Итак, запускаем импорт данных из CSV. После двойного клика на файле Open Office показывает диалог.


    Заметьте, в OO не нужно создавать новый воркбук и принудительно запускать импорт, все само

    1. Кодировка — как в файле.
    2. «Разделитель» — точка с запятой. Естественно, если в файле разделителем выступает именно она.
    3. «Разделитель текста» — пустой (все то же, что в Excel).
    4. В разделе «Поля» кликаю в левый-верхний квадрат таблицы, подсвечиваются все колонки. Указываю тип «Текст».

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

    Помимо Calc у нас в HFLabs популярен libreOffice, особенно под «Линуксом». И то, и другое для CSV применяют активнее, чем Excel.

    Бонус-трек: проблемы при сохранении из Calc в .xlsx


    Если сохраняете данные из Calc в экселевский формат .xlsx, имейте в виду — OO порой необъяснимо и масштабно теряет данные.


    Белая пустошь, раскинувшаяся посередине, в оригинальном CSV-файле богато заполнена данными

    Поэтому после сохранения я еще раз открываю файл и убеждаюсь, что данные на месте.

    Если что-то потерялись, лечение — пересохранить из CSV в .xlsx. Или, если установлен Windows, импортнуть из CSV в Excel и сохранить оттуда.

    После пересохранения обязательно еще раз проверяю, что все данные на месте и нет лишних пустых строк.

    Если интересно работать с данными, посмотрите на наши вакансии. HFLabs почти всегда нужны аналитики, тестировщики, инженеры по внедрению, разработчики. Данными обеспечим так, что мало не покажется :)
    HFLabs
    60.25
    Качество и интеграция клиентских данных
    Share post

    Comments 26

      +1
      Неужели нет каких-то специализированных CSV-редакторов? =)
      (Хотя, помню, лет 15 назад коллега сам написал инструмент для бережного редактирования CSV.)
        0
        Язык программирования и приложение GAWK (GNU AWK). Разве что для графиков придётся использовать gnuplot/rrdtools или что-то такое.
          0
          да уж просто R-Studio тогда (или Jupyter Notebook =)
          0

          На tcl в свое время каких только CSV-parser-ов ни написали!

            +2
            Под Windows Ron's Editor / Rons CSV Editor хорош.
              +1

              UltraEdit (платный)

              +2
              Deleted
              Честно говоря, не понял эти дополнительные действия в Excel.
              Если вы добавляете файл через встроенный импорт и на 3 шаге все столбцы определите, как текстовые, то уже во вставленной таблице эти ячейки будут иметь текстовый формат. Зачем после этого ещё раз выставлять формат ячеек, не понимаю. И вообще, судя по приложенной гифке, у вас эти вставленные ячейки имеют общий формат (general), хотя казалось бы вы вставили их, как текстовый.

              Проверьте, может у вас не обновленная версия или это баг? Проверил у себя: если определить, как текстовый, то и ячейка будет тоже текстовая.

              Извиняюсь, честно говоря невнимательно прочёл. Действительно, для новых ячеек формат останется General, а выставляется формат на 3 шаге только для вставляемых. Мой косяк.

              Ну а так, конечно стоит поправить, что у вас DSV формат, а не CSV. Я лично предпочитаю TSV (разделенные табуляцией) плюс в том, что запятая, которая может быть в данных не используется, при этом импорт и экспорт такого файла можно производить очень быстро обычным копипастом.
                0
                Честно говоря, не понял эти дополнительные действия в Excel.
                Если вы добавляете файл через встроенный импорт и на 3 шаге все столбцы определите, как текстовые, то уже во вставленной таблице эти ячейки будут иметь текстовый формат. Зачем после этого ещё раз выставлять формат ячеек, не понимаю.

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

                Пытался объяснить это в тексте, но недостаточно четко, наверное.

                И вообще, судя по приложенной гифке, у вас эти вставленные ячейки имеют общий формат (general), хотя казалось бы вы вставили их, как текстовый.

                Да, с гифкой я напортачил. Спасибо, что обратили внимание! Заменил ее на правильную.
                  0
                  Ну а так, конечно стоит поправить, что у вас DSV формат, а не CSV.

                  Строго говоря, вы правы. В статье пишу «CSV», потому что о формальностях почти всегда забывают и называют так все семейство форматов. Лично мой опыт таков.
                  +1
                  Excel 2010: «Сохранить как» / «CSV (разделители запятые) (*.csv)»
                  получается файл csv с разделителями ";" (точка с запятой).

                  Много проблем c изменением данных, например СНИЛС превращается в тыкву.
                    +1
                    Данное сохранение решает только проблему открытия файла с разделителем региональных настроек EU, но совершенно не решает проблемы из раздела Как Excel портит данные (приоритеты автозамены формата: Date > Number > Text). Можно указать Excel открыть CSV с любым разделителем, если в первой строке написать sep=;

                    Проблемы начинаются с нечёткого описания стандарта RFC 4180 (значения без " Excel читает в формате General), и заканчиваются отсутствием настройки "Текстовый формат ячеек вместо General" в Excel для новой книги; причём с приходом формата XLSX это делается сменой одного атрибута numFmtId="0" на numFmtId="49" в файле xls?\xl\styles.xml. Только непонятно, откуда Excel берёт шаблон?
                      0
                      Чтобы эксель сохранял в CSV с запятой, надо в региональных настройках винды сменить «Разделитель элементов списка» на желаемый, в частности запятую.
                      +2
                      О, у нас на сервисе пользователям очень часто объясняем почему открывая CSV в экселе они видят даты вместо цен :)

                      Ужасное и неизлечимое поведение. Жаль, что короме импорта (который довольно сложен для простых пользователей) нет адекватного решения.
                        0
                        программа CSVed.
                          +2
                          >> После этого, если повезет, Excel оставит исходные данные в покое. Но это не самая твердая гарантия, поэтому мы после сохранения обязательно проверяем файл через текстовый просмотрщик.
                          Можно после всех выполненных действий выполнить еще немного ручной работы, но уже точно быть уверенным, что формат данных не изменится.
                          Для этого:
                          1). Выделить один (!!!) столбец
                          2). Данные -> Тест по столбцам
                          3). Два раза нажать «Далее»
                          4). Еще раз принудительно указываем формат данных
                          5). Нажать «Готово».

                          После этого никаких проблем с данными уже не возникает :)
                            +2
                            Ого, какие пляски с бубном. Я передам ребятам, спасибо!
                            0
                            По кодировке — как Excel ее определяет?
                            Есть csv. Если его открывать в Excel просто как файл, кодировка определяется неверно (ISO-8859-1). Если же его подгружать через импорт текста, то как правило, сразу выбирает верную кодировку (UTF-8).
                              +1
                              awk, perl, python,… эволюция средств работы со структурированными данными.
                              cut и sed просто с текстовым потоком.
                              OpenOffice уже не тот. Это уже имя для коммерческого продукта от Oracle. Ранее это был StarOffice от SUN Microsystem.
                              LibreOffice — вынужденный уход от имени OpenOffice. Функционал очень неплох для свободного продукта.
                              Еще стоит упомянуть IBM 123 и Supercalc.
                                0

                                На MacOS есть стандартное приложение Numbers из iWork, которое открывает CSV гораздо быстрее маковского Excel, при этом не портя сам файл.

                                0
                                Дык КЛАДР же уже того вроде несколько лет как?
                                Насчет инструментов — чем Jupyter/Pandas не угодил?
                                  0

                                  Из подобных примеров: номер полиса ОМС — 16 символов, все цифры. В итоге при невнимательности получаем Е на конце и теряем последнюю цифру.

                                  +1
                                  Еще стоит сказать, что Эксель автоматом меняет линуксовый перенос строки на виндовый, из-за чего файл на линуксовом веб-сервере не распарсится вообще.
                                    0
                                    Вопрос не по теме. А если ли КДПВ в болшом разрешении? Очень понравилась, хотел бы на фоновый рисунок поставить.
                                      0
                                      Только 1024x512 px, увы :(

                                      Передал ваш комментарий иллюстратору, ей очень-очень лестно. Спасибо!
                                      0
                                      Не пробовали Powershell использовать (Import-Csv, Export-Csv)?
                                      Есть гибкие возможности по фильтрации.
                                      6-я версия работает и на Linux.
                                        0
                                        Я на ардуине генерировал csv с разделителем запятой. Чтобы русифицированный Excel правильно их воспринимал — дописывал в начало каждого файла строку
                                        sep=,
                                        т.е. явно указывал разделитель. Все правильно импортировалось.

                                        Only users with full accounts can post comments. Log in, please.