Как очистить грязный файл CSV

Почему экспортные товары приходят сломанными

CSV — это скорее семейство соглашений, чем формат. Два приложения могут заявлять о поддержке CSV и не соглашаться относительно разделителя, стиля кавычек, окончания строки и кодировки символов.

Разделитель — самая распространенная неожиданность. Программное обеспечение, продаваемое в странах, где в качестве десятичного разделителя используется запятая, часто экспортирует файлы, разделенные точкой с запятой, поскольку запятая уже занята. Эти файлы по-прежнему заканчиваются расширением .csv, а инструмент, предполагающий использование запятых, сжимает каждую строку в один столбец.

Кодирование — второе. Файл UTF-8 часто начинается с невидимого трехбайтового знака порядка байтов. Excel в Windows использует его для распознавания UTF-8; многие другие инструменты этого не ожидают, поэтому оно привязывается к первому имени заголовка, а столбец с именем id автоматически становится столбцом, которому никогда не будет соответствовать никакой поиск.

Диагностика в трёх цифрах

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

• Количество столбцов. Если это 1, разделитель неправильный. Попробуйте точку с запятой, затем табуляцию.
• Количество строк. Если оно намного выше ожидаемого, файл, вероятно, содержит символы новой строки в кавычках, которые уже были испорчены чем-то вышестоящим.
• Количество предупреждений. В каждом предупреждении указывается номер строки, подсчитанный так же, как в текстовом редакторе, так что вы можете пойти и посмотреть.

Чтение предупреждений

Неровная строка означает, что в строке содержится другое количество ячеек, чем в заголовке. Для упорядочения сетки ничего не отбрасывается: короткие строки дополняются пробелами, а длинные строки сохраняют свои дополнительные значения. Тихое удаление чьих-либо данных, чтобы таблица выглядела прямоугольной, — худшее, что может сделать инструмент очистки, поэтому этого не делают.

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

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

Чистите в правильном порядке

Экспорт, безопасный для внедрения формул, включен по умолчанию и является единственным вариантом, имеющим реальные последствия для безопасности. Ячейка, начинающаяся с =, +, - или @, обрабатывается приложениями для работы с электронными таблицами как формула, поэтому значение, полученное из ненадежного источника, может выполниться, когда кто-то откроет ваш файл. При безопасном экспорте к этим ячейкам добавляется апостроф, что останавливает их вычисления, включая любые, которые вы намеревались вычислить, что является следствием устранения, а не ошибки.

1. Сначала обрежьте пробелы. Две строки, отличающиеся только конечным пробелом, не являются дубликатами, пока вы их не обрежете.
2. Во-вторых, удалите повторяющиеся строки. Первое вхождение сохраняется, поэтому сохранившийся порядок не изменяется.
3. Переименуйте заголовки и выберите столбцы для экспорта.
4. Отсортируйте, если вам нужно. Числовые столбцы обнаруживаются и сортируются по числам, поэтому 9 предшествует 10.
5. Перед загрузкой проверьте параметры экспорта, особенно настройку ввода формулы.

Выбор кодировки экспорта

Включите метку порядка байтов UTF-8, если файл предназначен для Excel в Windows, который использует его для определения кодировки; без него символы с диакритическими знаками и нелатинские буквы могут отображаться как моджибаке при двойном щелчке мыши.

Оставьте это значение OFF, если файл передает скрипт, импорт базы данных или другую программу, поскольку некоторые строгие анализаторы рассматривают эту метку как данные.

Рабочий пример: экспорт строки 12,000, которая открывается как один столбец.

Экспорт клиента из европейского CRM открывается в вашем редакторе в виде 12,000 строк и одного столбца. В строке заголовка указано Имя;Электронная почта;Страна;Дата регистрации.

1. Загрузите файл. Автоматическое обнаружение столбцов 4 в отчетах; если это не так, установите разделитель на точку с запятой вручную.
2. Прочтите предупреждения: скажем, 3 неровные строки в 418, 2,905 и 7,110.
3. Посмотрите на эти три ряда. Внутри названия компании они содержат неэкранированную точку с запятой — лишние ячейки сохраняются, поэтому ничего не потеряно.
4. Обрежьте пробелы, затем удалите дубликаты.
5. Экспортируйте как CSV с запятой в качестве разделителя и отметкой порядка байтов, поскольку файл передается коллеге, который использует Excel.

Результат: Файл UTF-8, разделенный запятыми, с отметкой порядка байтов, четырьмя столбцами и удаленными повторяющимися строками. Три проблемные строки по-прежнему присутствуют, а их дополнительные ячейки не повреждены и помечены для исправления в источнике, а не для удаления, чтобы привести таблицу в порядок.

Откройте инструмент

Очистите CSV в своем браузере.

Анализирует веб-воркер на вашем устройстве, сохраняет каждую найденную ячейку и сообщает о проблемах, о которых он даже не догадывается.

Что это не распространяется

• Весь файл хранится в памяти вашего устройства. Ограничение — 50 MB, и телефон будет испытывать проблемы намного раньше, чем ноутбук.
• В предварительном просмотре отображаются только первые 100 строки, хотя каждая строка очищается и экспортируется.
• Обнаружение кодировки ограничено меткой порядка байтов UTF-8. Файл, сохраненный как Windows-1252 или Shift-JIS, читается как UTF-8 и может содержать символы замены; повторно экспортируйте его как UTF-8 из исходного приложения.
• Записывается только UTF-8. Устаревшие кодировки не создаются.
• Поддержка нескольких листов отсутствует, поскольку CSV не имеет понятия листов.
• Сообщается о файле, цитирование которого уже повреждено, но не восстановлено.
• Отмена ограничена последними 20 операциями.