Язвы и грабли CSV и Excel: проблемы и решения
CSV является стандартом де-факто для связи между собой разнородных систем, для передачи и обработки объемных данных с «жесткой», табличной структурой. Во многих скриптовых языках программирования есть встроенные средства разбора и генерации, он хорошо понятен как программистам, так и рядовым пользователям, а проблемы с самими данными в нем хорошо обнаруживаются, как говорится, на глаз.
История этого формата насчитывает не менее 30 лет. Но даже сейчас, в эпоху повального использования XML, для выгрузки и загрузки больших объемов данных по-прежнему используют CSV. И, несмотря на то, что сам формат довольно неплохо описан в RFC, каждый его понимает по-своему.
Начнем с того, что форматом CSV на самом деле называют три разных текстовых формата, отличающихся символами-разделителями: собственно сам CSV (comma-separated values — значения, разделенные запятыми), TSV (tab-separated values — значения, разделенные табуляциями) и SCSV (semicolon separated values — значения, разделенные точкой с запятой). В жизни все три могут называться одним CSV, символ-разделитель в лучшем случае выбирается при экспорте или импорте, а чаще его просто «зашивают» внутрь кода. Это создает массу проблем в попытке разобраться.
Как иллюстрацию возьмем казалось бы тривиальную задачу: импортировать в Microsoft Outlook данные из таблицы в Microsoft Excel.
В Microsoft Excel есть средства экспорта в CSV, а в Microsoft Outlook — соответствующие средства импорта. Что могло быть проще — сделал файлик, «скормил» почтовой программе и — дело сделано? Как бы не так.
Создадим в Excel тестовую табличку:
… и попробуем экспортировать ее в три текстовых формата:
| «Текст Unicode» | Кодировка — UTF-16, разделители — табуляция, переводы строк — 0×0D, 0×0A, объем файла — 222 байт |
| «CSV (разделители — запятые)» | Кодировка — Windows-1251, разделители — точка с запятой (не запятая!), во второй строке значение телефонов не взято в кавычки, несмотря на запятую, зато взято в кавычки значение «01;02», что правильно. Переводы строк — 0×0D, 0×0A. Объем файла — 110 байт |
| «Текстовые файлы (с разделителями табуляции)» | Кодировка — Windows-1251, разделители — табуляция, переводы строк — 0×0D, 0×0A. Значение «01;02» помещено в кавычки (без особой нужды). Объем файла — 110 байт |
Какой вывод мы делаем из этого. То, что здесь Microsoft называет «CSV (разделители — запятые)», на самом деле является форматом с разделителями «точка с запятой». Формат у Microsoft — строго Windows-1251. Поэтому, если у вас в Excel есть Unicode-символы, они на выходе в CSV отобразятся в вопросительные знаки. Также то, что переводами строк является всегда пара символов, то, что Microsoft тупо берет в кавычки все, где видит точку с запятой. Также то, что если у вас нет Unicode-символов вообще, то можно сэкономить на объеме файла. Также то, что Unicode поддерживается только UTF-16, а не UTF-8, что было бы сильно логичнее.
| «Значения, разделенные табуляцией(Windows)» | Скармливаем аутлуку файл tsv, с разделенными табуляцией значениями и. — чтобы вы думали. Outlook склеивает поля и табуляцию не замечает. Заменяем в файле табуляцию на запятые и, как видим, поля уже разбирает, молодец. |
| «Значения, разделенные запятыми (Windows)» | А вот аутлук как раз понимает все верно. Comma — это запятая. Поэтому ожидает в качестве разделителя запятую. А у нас после экселя — точка с запятой. В итоге аутлук распознает все неверно. |
Два майкрософтовских продукта не понимают друг друга, у них напрочь отсутствует возможность передать через текстовый файл структурированные данные. Для того, чтобы все заработало, требуются «пляски с бубном» программиста.
Мы помним, что Microsoft Excel умеет работать с текстовыми файлами, импортировать данные из CSV, но в версии 2007 он делает это очень странно. Например, если просто открыть файл через меню, то он откроется без какого-либо распознавания формата, просто как текстовый файл, целиком помещенный в первую колонку. В случае, если сделать дабл-клик на CSV, Excel получает другую команду и импортирует CSV как надо, не задавая лишних вопросов. Третий вариант — вставка файла на текущий лист. В этом интерфейсе можно настраивать разделители, сразу же смотреть, что получилось. Но одно но: работает это плохо. Например, Excel при этом не понимает закавыченных переводов строк внутри полей.
Более того, одна и та же функция сохранения в CSV, вызванная через интерфейс и через макрос, работает по-разному. Вариант с макросом не смотрит в региональные настройки вообще.
Стандарта CSV как такового, к сожалению, нет, но, между тем, существует т.н. memo. Это RFC 4180 года, в котором описано все довольно толково. За неимением ничего большего, правильно придерживаться хотя бы RFC. Но для совместимости с Excel следует учесть его собенности.
Вот краткая выжимка рекомендаций RFC 4180 и мои комментарии в квадратных скобках:
Вот в нотации ABNF описание формата:
Также при реализации формата нужно помнить, что поскольку здесь нет указателей на число и тип колонок, поскольку нет требования обязательно размещать заголовок, здесь есть условности, о которых необходимо не забывать:
Пример валидного CSV, который можно использовать для тестов:
точно такой же SCSV:
Второй файлик, который по логике SCSV, экселом воспринимается и выходит вот что:
С приведением типов сработало, но зато теперь не обрабатываются нормально переводы строк и осталась проблема с ведущими нулями, кавычками и лишними пробелами. Да и пользователям так открывать CSV крайне неудобно.
Есть эффективный способ, как заставить Excel не приводить типы, когда это нам не нужно. Но это будет CSV «специально для Excel». Делается это помещением знака «=» перед кавычками везде, где потенциально может возникнуть проблема с типами. Заодно убираем лишние пробелы.
И вот что случаеся, если мы открываем этот файлик в экселе:
Чтобы построить хороший и удобный импортер CSV, необходимо помнить о следующем:
Что такое файл значений, разделенных запятыми (файл CSV)?
Что означает файл с разделителями-запятыми (CSV)?
Обычно все поля разделяются запятыми, а все записи разделяются сложной строкой символов. В таблицах Excel все поля с запятыми заключены в двойные кавычки, которые обычно называются квалификаторами текста, т.е. одна ячейка с зеленым, синим и желтым цветом будет записана как «зеленый, синий и желтый».
Что такое файл CSV?
Это так просто, как кажется. В конце концов, вам не нужно быть технически подкованным, чтобы понимать, как работают файлы CSV. Как правило, файл с расширением CSV используется там, где есть фрагмент данных, который нужно отправить в другое приложение. Расширение файла, с другой стороны, помогает операционной системе определить, с какой программой, в частности, связан файл.
Если, например, я передаю файл с именем ‘minutes.doc», расширение, которое заканчивается на «.doc», означает, что файл следует открывать в Microsoft Word. Почти всегда файл CSV открывается такими программами, как Microsoft Excel, Google Docsи OpenOffice Расчет.
Предположим, у вас уже установлен Microsoft Excel на вашем компьютере, CSV-файл должен автоматически открываться в Excel при нажатии пользователем. Иногда компьютер дает вам возможность с помощью виджета «Открыть с помощью», чтобы выбрать наиболее предпочтительное приложение. Кроме того, если вы хотите получить доступ к файлу CSV напрямую, вам нужно нажать Файл, становятся Откройте и выберите файл CSV с этой точки.
Чтобы сохранить электронную таблицу в формате CSV,
При создании файла CSV стандартной практикой является разделение полей данных ограничитель, который всегда в форме запятой. Имейте в виду, это должен быть один символ, иначе ваши данные не будут иметь никакого логического смысла. Далее следует убедиться, что список заголовков разделен так же, как и весь файл. И почему это является неотъемлемой частью всего создания файла CSV? Это поможет вам получить точные результаты при вводе запроса.
Как открыть файл CSV
Преимущественно, программы для работы с электронными таблицами широко используются для открытия файлов CSV. Здесь гораздо проще их прочитать. Программа электронных таблиц систематически сортирует данные в CSV-файле по столбцам. Это помогает отфильтровать все содержимое файла. Не забывая, что пользователь может получить максимальную отдачу от бесплатных инструментов для работы с электронными таблицами, таких как Kingsoft Spreadsheets или очень распространенного OpenOffice Calc.
Если у вас есть OpenOffice Calc уже установлен на вашем компьютере, файл CSV должен открываться автоматически при двойном щелчке по нему. Как еще один вариант, ваш компьютер покажет Открыть с помощью подскажите, откуда вы можете выбрать программу для открытия файла CSV.
Кроме того, вы можете использовать онлайн-программы для работы с электронными таблицами, такие как Google Sheets или Zoho Office, которые имеют инновационные функции на борту. Эти инструменты позволяют нескольким пользователям получать доступ к одному файлу в режиме реального времени. Если быть точным, в Google Sheets более интересно то, что он позволяет конвертировать файлы Excel в листы и наоборот.
Вы можете использовать Google Drive, чтобы открыть файл CSV. Процесс довольно прост. Просто следуйте этим простым шагам;
A Текстовый редактор также используется для открытия файла CSV. Как правило, операционные системы Mac поставляются с программой TextEdit, в то время как Windows использует Notepad в качестве предустановленного приложения. К сожалению, такие программы не способны обрабатывать файлы с огромными объемами данных. Чтобы устранить это препятствие, существует распространенность известных сторонних приложений, которые пригодятся, когда пользователь хочет иметь дело со сложными файлами CSV.
Итак, почему текстовые редакторы обычно используются для обработки файла CSV?
С этого момента вы можете создать файл из square один, используя язык программирования, такой как CSS или HTML. Кроме того, пользователь может редактировать файлы, которые заканчиваются расширениями, такими как TXT, XML, CSV или PHP, и это лишь некоторые из них.
Преобразование файла CSV
Этот шаг крайне необходим, если вы собираетесь время от времени взаимодействовать с файлами CSV. Любой файл CSV может быть преобразован в формат Microsoft Excel одним нажатием кнопки. Пользователь должен просто нажать Файл, и перейдите в Сохранить как кнопка. Здесь доступны форматы, среди которых XLS, TXT, XML, SQL и многие другие. Стоит отметить, что Google Docs также помогает с конвертацией файлов.
Если на вашем компьютере нет программы для преобразования файлов, вы можете перейти прямо в браузере и использовать бесплатное программное обеспечение для преобразования файлов. Вы можете сделать это онлайн без необходимости утомительного процесса загрузки программы.
Кроме обычного CSV (с разделителями-запятыми), существуют другие форматы, которые вступают в игру;
Итак, как мне экспортировать мой файл Excel в CSV?
Практически говоря, данные экспортируются из файла Excel в файл CSV с кодировками UTF-8 или UTF-16. Итак, позвольте мне объяснить различия. С UTF-8 кодировка более сжатая, поскольку для каждого символа используется что-нибудь между байтами 1 и 4. Этот тип кодирования совместим со всеми форматами символов на основе ASCII (американский стандартный код для обмена информацией).
UTF-16 сохраняет один символ в 2 в байтах 4. Он используется, когда ваши данные имеют азиатские символы, такие как японский и китайский.
Файл CSV замечательно используется во многих областях. Если мы приведем в уравнение индустрию электронной коммерции, то на самом деле мы можем иметь к ней так много общего. Вы можете импортировать и экспортировать файлы для обновления вашего инвентаря и информации о продукте. Добавление новых или существующих продуктов вполне осуществимо. Владелец интернет-магазина может использовать эту стратегию для передачи данных о новых покупателях и осуществлять плавное управление запасами Вы можете использовать файлы для импорта номеров отслеживания для всех продуктов, которые находятся на пути к покупателям.
CSV Структурирование
Все структуры, используемые в CSV-файле, просты в создании и требуют меньше усилий, поскольку все они следуют одинаковому формату. Если вы хотите сделать это вручную, то вы можете использовать текстовый редактор, например Notepad ++, который поддерживает несколько доминирующих Программирование языки. Кроме того, использование программы для работы с электронными таблицами может лучше удовлетворять потребности пользователя, поскольку в ней есть ячейки, в которых данные сортируются по строкам и столбцам.
Как создать файл CSV
Интересно, что хотя вы импортируете данные таблицы с помощью файла CSV, это текстовый файл. Другими словами, вы можете создать свой CSV-файл с помощью любого редактора, способного изменять текст. Чаще всего файл CSV создается путем экспорта файла и нажатия кнопок «Файл»> «Экспорт». Если вы создали электронную таблицу в программе, вы можете просто экспортировать содержимое этого файла в формат CSV при использовании программы.
Возможно, вы обнаружите, что экспортировать и импортировать файлы CSV будет проще, если вы не будете использовать большое количество знаков препинания, таких как кавычки, точка с запятой и другое содержимое в своих табличных данных.
В программе Excel щелкните меню файла и выберите вариант для Сохранить как. Вы должны увидеть опцию «Сохранить как тип», где вы можете выбрать формат файла CSV в качестве одного из вариантов. Щелкните параметр значений, разделенных символами, и введите имя файла, под которым вы хотите сохранить содержимое. Нажмите «Да», когда вам будет предложено подтвердить, что вы хотите использовать этот тип файла, и закончите, нажав «Сохранить».
Также можно загружать различные файлы из Интернета в формате CSV. Например, вы можете загрузить списки адресов электронной почты или данные счетов в текстовом формате CSV, чтобы упростить загрузку файла электронной таблицы в другую службу.
Файлы CSV доступны для открытия в любой программе для работы с электронными таблицами, включая Google Таблицы, Open Office и Microsoft Excel. Вы можете открыть файл CSV в простом текстовом редакторе, и он совместим с большинством платформ для работы с электронными таблицами. Однако у простоты CSV есть некоторые недостатки. Например, вы можете сохранить в файле только один лист без формул или форматирования.
Файлы XLS чаще встречаются в файлах книги Excel, если у вас есть версия между 97-2003 годами, более поздние версии, скорее всего, будут в формате XLSX. Расширение XLS специально разработано для Microsoft Excel, но это не значит, что вы не можете использовать его и в других средах.
Вы можете открывать файлы XLS с помощью средства просмотра Excel, что упрощает печать информации из файла значений. Microsoft Excel также может читать шаблон файла CSV, но могут возникнуть некоторые проблемы с разделителем в зависимости от вашего компьютера.
Файлы Excel, как правило, являются лучшим выбором, если вы храните более сложные данные, в то время как файлы CSV поддерживаются практически всеми интерфейсами загрузки данных. Если вы планируете перемещать данные между платформами, возможно, вам лучше использовать формат CSV.
CSV формат файла: инструкция по работе с расширением
CSV (не путать с CVS) — это формат для представления прямоугольного массива (матрицы) числовых и текстовых значений. Это пример формата «плоского файла».
Общее описание формата csv
Поля, содержащие специальный символ (запятая, CR, LF или двойные кавычки), «экранированы», путем заключения в двойные кавычки (Hex 22). Необязательная строка заголовка отображается как первая строка документа в том же формате, что и обычные строки записи. Этот заголовок будет содержать имена, соответствующие полям в документе, и должен содержать то же количество полей, что и записи в остальной части документа.
CSV использует US-ASCII в качестве набора символов, но разрешены другие наборы символов. Иногда файл CVS и CSV путают.
Важная информация о редактировании файлов csv
Как структурированы csv файлы
Шаблоны CSV или файлы данных можно загрузить по ссылкам в верхней части инструмента «Загрузить данные». Первая строка шаблона или файла данных содержит заголовки столбцов. Каждая последующая строка соответствует записи в базе данных. Когда загружается шаблон CSV, он содержит только заголовки столбцов. Поскольку шаблоны используются для добавления новых записей, новые строки будут добавляться для каждой записи.
Когда документ данных CSV загружается, первая строка содержит заголовок столбца, а последующие строки содержат записи данных, которые уже существуют в базе данных. Записи в этих строках можно редактировать или удалять.
В документе CSV каждая строка содержит упорядоченную последовательность заголовков столбцов или значений, разделенных запятыми. Запятые используются для сохранения файловой структуры. Каждая запятая в первой строке (которая содержит заголовки столбцов) разделяет заголовок столбца и место в упорядоченной последовательности столбцов.
Запятые в последующих строках также поддерживают последовательность упорядоченных столбцов, поэтому первое значение в каждой последующей строке представляет значение в первом столбце, второе значение в каждой последующей строке представляет значение во втором столбце и так далее. В отличие от стандартной пунктуации предложений, после запятой не ставится пробел.
Большинство значений заключено в двойные кавычки. Исключением является односимвольное значение, например 1 или 0 (ноль). Заключение значения в двойные кавычки позволяет использовать в поле сложные значения, например, содержащие запятые, без нарушения структуры документа. Например, поле, содержащее ряд элементов, например избранные цвета, может иметь такое значение:
“красный, зеленый и синий”
Вы не будете знать об этих цитатах при просмотре файла данных в приложении для работы с электронными таблицами, но они появляются, когда file просматривается в текстовом редакторе.
Как и чем открыть файл csv расширения
Формат CSV, или значения, разделенные запятыми, это общий формат для хранения и передачи контента, включая контакты, календарные встречи и статистические данные. Excel по умолчанию читает файлы CSV, но в большинстве случаев, когда открывается таблица CSV в Excel, видно только зашифрованные данные, которые невозможно прочитать.
В инструкции ниже показано, как легко правильно открывать файлы CSV в Excel и просматривать их без необходимости конвертации чего-либо.
Программы для работы с CSV
Файл CSV можно открыть в любой программе, однако для большинства пользователей CSV формат лучше всего просматривать в программе для работы с электронными таблицами, например в Microsoft Excel, OpenOffice Calc или Google Docs.
Microsoft excel
Microsoft Excel — полезная программа для анализа данных и документации. Это программа для работы с электронными таблицами, которая содержит ряд столбцов и строк, где каждое пересечение столбца и строки является «ячейкой». Каждая ячейка содержит одну точку данных или одну часть информации. Организуя хранение данных таким образом, вы упростите поиск информации и получите возможность автоматически извлекать информацию из изменяющихся данных.
Открытие csv сразу с разделением столбцов в эксель
Вы можете либо щелкнуть файл правой кнопкой мыши и открыть его в Excel, либо перейти к файлу из Excel и открыть его напрямую.
После открытия использовуйте пункт меню «Данные → текст в столбцы» для преобразования. Выберите «по разделителю» в поле выбора и используйте запятую в качестве единственного разделителя убедитесь, что это единственный отмеченный пункт.
Ручной метод превращения таблицы эксель в CSV файл
Libreoffice calc
LibreOffice — это бесплатный полнофункциональный пакет офисных приложений. Его родным файловым форматом является Open Document Format (ODF), открытый стандартный формат, который принимается правительствами во всем мире в качестве необходимого формата файлов для публикации и приема документов. LibreOffice также может открывать и сохранять документы во многих других форматах, включая те, которые используются в нескольких версиях Microsoft Office, в том числе и CSV.
Openoffice calc
Apache OpenOffice — это офисный программный пакет с открытым исходным кодом для обработки текстов, электронных таблиц, презентаций, графики, баз данных и многого другого. Он доступен на многих языках и работает на всех распространенных компьютерах. Он хранит все ваши данные в международном формате открытого стандарта, а также может читать и записывать файлы из других распространенных пакетов офисного программного обеспечения. Его можно скачать и использовать совершенно бесплатно для любых целей.
Блокнот
Блокнот — это универсальный текстовый редактор, включенный во все версии Microsoft Windows, который позволяет создавать, открывать и читать файлы с открытым текстом. Если файл содержит специальное форматирование или не является файлом с открытым текстом, его нельзя прочитать в Блокноте.
Notepad++
Notepad ++ — это бесплатный текстовый редактор для Microsoft Windows, который предоставляет дополнительные функции, которых нет в стандартном текстовом редакторе Windows, Блокноте. Впервые он был выпущен 24 ноября 2003 года разработчиком Дэном Хо и все еще находится в активной разработке.
Notepad ++ специально разработан для редактирования исходного кода. «++» в названии — это ссылка на оператор в таких языках программирования, как C, C ++, Java и JavaScript.
Safari
Safari — это интернет-браузер, который был впервые представлен 30 июня 2003 года и поддерживается на операционных системах Apple MacOS X и IOS.
Microsoft outlook
Microsoft Outlook — это приложение для управления электронной почтой и задачами, которое доступно в большинстве версий Microsoft Office. Впервые он был выпущен с Exchange Server 5.5 как связанная программа, а затем интегрирован в качестве основного приложения с пакетом Microsoft Office 97 и последующими версиями.
Просмотр формата csv онлайн
Для того, чтобы открыть CSV формат на компьютере без установки сторонних программ, можно использовать онлайн-сервисы. Вот список самых распространенных из них:
Открываем расширение csv на android
Для того, чтобы открыть расширение CSV формата на смартфоне под управлением Android, необходимо найти в Play Market подходящую программу, поддерживающую этот формат. Вот список самых распространенных из них:
Решение проблем с файлами csv
Проблема 1: Сталкиваюсь с ошибками преобразования набора символов.
Используйте программу uconv для устранения ошибок при преобразовании набора символов.
Iconv входит в состав большинства дистрибутивов ОС UNIX, а uconv – нет. iconv ограничен памятью. uconv намного лучше подходит для работы с большими файлами. Используйте app-get или другой менеджер пакетов UNIX, чтобы получить uconv, если его нет. Затем введите команду:
uconv –from-code ISO_8859-1 –to-code UTF8
Проблема 2: Что делают эти посторонние управляющие символы?
Иногда файл выглядит нормально, но не загружается. Он может содержать непечатаемые символы ASCII, которые обычно не используются в файлах CSV. Их бывает сложно отследить. Может возникнуть эта проблема, если вы получаете необычные сообщения об ошибках при попытке загрузить файл и не можете отследить причину ошибки.
Используйте эту команду Perl, чтобы удалить эти символы, пропустив через нее файл:
Проблема 3: Не знаю, как обращаться со значениями NULL.
Обратите внимание, что у Postgres есть некоторые проблемы с импортом этих типов файлов, если они содержат значения NULL. Postgres не позволит указать значение NULL как пустую строку (,,). Обычно это хороший способ представления NULL, но он не будет правильно импортирован как NULL в Postgres.Необходимо использовать явный указатель NULL, например \ N, поэтому, если вас действительно волнуют значения NULL, вы должны использовать \ N.
