Xlsx csv что это
Конвертация данных из формата CSV в XLSX
Самый простой и самый рискованный способ открыть файл CSV с помощью Excel, если он не превышает 1 048 576 строк – двойной клик по нему левой кнопкой мыши. Опасность скрывается в том, что Excel по умолчанию применяет к данным из CSV-файла формат «Общий» (General) и вносит изменения в виде удаления лидирующих нулей, округления чисел до двух символов после запятой, перевода к экспоненциальной форме (если число более 15 цифр, исходное значение теряется), преобразования числа в формат даты, и это не все возможные варианты.
Но есть способ избежать этих изменений с помощью встроенного в Excel механизма импорта.
Для этого необходимо создать новый файл Excel, в нем зайти в «Данные» => «Получение внешних данных» => «Из текста». В открывшемся обозревателе найти и выбрать свой CSV-файл, нажать «Импорт», появится окно «Мастер текстов (импорт)».
Шаг №1 – выбрать формат данных «с разделителями», в списке с форматами файла выбрать тот, с которым в окне предварительного просмотра все символы станут читаемыми (чаще всего используют: «65001:Юникод(UTF-8)», «20127:Windows-1251», «Windows(ANSI)»).
Шаг №3 – В образце разбора данных передвигаем «бегунок» вправо до конца => зажимаем «левый Shift» => «левой кнопкой мыши» кликаем на последнюю колонку => в «формат данных столбца» выбираем текстовый => «Готово».
После нажатия «Готово» встроенный в Excel механизм импорта открывает CSV файл без изменения исходной информации.
Но это не панацея, в случае если в ячейках файла содержатся символы «переноса» или «табуляции», то при открытии CSV файла даже встроенным в Excel импортом, данные съезжают и ломается структура таблицы.
Самый эффективный способ борьбы — исключить их в момент выгрузки. Например, наш SQL запрос:
Символы «переноса» или «табуляции» содержатся в колонке « ». Добавляем функцию REPLACE и заменяем данные символы на пробелы:
Если нет возможности повторно выгрузить CSV файл, то для его обработки можно воспользоваться Python и символы «переноса» или «табуляции» проигнорировать.
Преобразование форматов CSV в XLS или XLSX
В соответствии с Методическими рекомендациями публикации откртых данных 3 версии наборы данных большого объема более 80 тыс записей скачиваются в форматах JSON или CSV. Формат CSV иногда вызывает трудности с преобразованием в xls или xlsx. Инструкция по шагам расскажет как импортировать данные из CSV с использованием стандартного инструментария Exsel 2007-20ХХ Инструкция по конвертации CSV в XSL.
Как открыть в Excel файл CSV в формате открытых данных версии МР 3.0
Выгрузки данных в формате CSV (comma-separated values) обычно делаются с разделителями значений – запятыми и в кодировке UTF-8. Однако Microsoft Excel по умолчанию не может правильно отображать их содержимое при стандартной процедуре открытия файла (неправильно отображается кириллица и данные не разбиваются по колонкам).
Для того, чтобы при открытии файла сохранились колонки и правильное отображение русского языка, необходимо выполнить следующее.
Перейти на вкладку «Данные» и выбрать пункт меню «Из текста».
В стандартном диалоговом окне открытия файла выбрать файл, который необходимо открыть, и нажать кнопку «Импорт».
Откроется окно мастера импорта файлов. В поле «Формат исходных данных» необходимо выбрать «с разделителями», в выпадающем окне «Формат файла» выбрать «Юникод (UTF-8)».
В самом низу окна находится поле предварительного просмотра. В нем можно проверить, что текст на русском языке отображается корректно.
После настройки параметров необходимо нажать кнопку «Далее».
В следующем окне необходимо правильно задать разделитель значений. Для этого в поле «Символом-разделителем является» поставить галочку «запятая» и снять все остальные галочки, если они где-то стоят. При этом можно увидеть, что в поле предпросмотра в нижней части окна значения начнут отображаться в виде колонок.
Необходимо нажать кнопку «Далее».
Последнее окно служит для настройки формата данных столбцов, аналогично тому, как это делается для ячеек таблицы Excel. Как правило, настройка не требуется, за исключением колонок, в которых находятся даты.
При импорте даты часто искажаются, поэтому рекомендуется указать мышью столбец, содержащий дату (он выделится черным цветом), и назначить ему значение даты в нужном формате (для случая на экране у нас формат год-месяц-день, поэтому в выпадающем списке «дата:» необходимо выбрать значение «ГМД».
По окончании настройки нажать кнопку «Готово».
После этого на экране появится диалоговое окно «Импорт данных». Требуется указать ячейку, начиная с которой на листе будут вставлены данные из файла CSV (это будет левый верхний угол таблицы). После нажатия кнопки «ОК» данные появятся на листе.
Далее сохранить в необходимом формате Excel.
Конвертация многостраничного xls/xslx в csv при помощи PHPExcel
В моих проектах часто нужно было собирать данные из разных источников в CSV формат, и пока не нужно было получать данные из нескольких страниц таблиц xls, мне хватало простого fgetcsv() / fputcsv(). Но вот наступил всё-таки тот день, когда передо мной была поставленна задача «получать данные со всех страниц документа». И, как водится, я начал искать готовое решение, чтобы не строить свой «велосипед». Но, к сожалению, именно того, что мне нужно было, я не нашёл: было похожее решение, которое выводило многостраничный документ на экран, но использовалась другая библиотека, которая, как я понял, не поддерживала формат xslx (Excel 2007 +). Ещё немного поискав другие варианты, я понял, что дело гиблое и решил разобраться с библиотекой самостоятельно. Совместив некоторые подсказки по работе с библиотекой PHPExcel в одно целое, я получил следующий скрипт. Итак, приступим.
Для начала нам понадобится сам PHPExcel. Сразу отмечу, что библиотека отлично ставится через composer, однако нигде не указана явно полная версия библиотеки. Методом подбора я указал версию 1.8 с добавлением признака «неточности».
В моём composer.json, которую я добавил в блок «require-dev»:<>, получилась вот такая запись:
На данный момент установилась версия 1.8.1. Так как библиотека PHPExcel наследует SPL, который есть в PHP, начиная с версии 5.3, то вместо стандартных обходов масива строк и ячеек документа при помощи foreach() я решил использовать Итераторы.
Подключаем библиотеку, загружаем документ и определяем некоторые первоначальные данные:
Далее получаем Итератор страниц, для обхода которых нам ненужно знать их количество:
Обход итератора производится при помощи вот такой несложной конструкции:
Аналогичным образом были получены и обходятся строки и ячейки документа. Как красиво получить данные из ячейки, я нашёл здесь же, на Хабре, в статье Универсальное чтение ячеек в PHPExcel. Я не буду подробно расписывать все проверки, у кого есть желание — можете прочитать в указанной статье.
Запись в CSV файл, я произвожу то же через эту библиотеку вот таким образом.
Наполнение объекта для записи в файл я покажу ниже в полном варианте скрипта. Единственное, что могу добавить: если вам нужно будет записывать даты заново в документы xls/xlsx и указать явное форматирование, то при подготовке объекта PHPExcel можно воспользоваться следующей конструкцией:
Где формат задаётся при помощи константы PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2. В данном случае это формат yyyy-mm-dd, который можно сразу использовать в запросе MySql.
Кстати, вот все доступные константы библиотеки:
В итоге у меня получился скрипт который пишет каждую страницу документа в отдельный csv-файл, при этом получает правильные значения ячеек независимо от наличия внешних данных и форматирует дату в подходящем формате.
На этапе тестирования все значения выводятся на экран, далее кому не нужно естественно вывод можно убрать.
Критика, дополнения и исправления приветствуются. Всем спасибо, и буду очень рад если моя статья кому-то поможет и сократит несколько часов работы.
Импорт содержимого CSV-файла в Excel
CSV – популярное расширение файлов, которые используются, в основном, для обмена данными между различными компьютерными программами. Чаще всего необходимости в открытии и редактировании таких документов нет. Однако в некоторых случаях перед пользователями может встать такая задача. Программа Excel позволяет это сделать, но в отличие от стандартных файлов в формате XLS и XLSX, простое открытие документа двойным щелчком мыши не всегда дает качественный результат, что может выражаться в некорректном отображении информации. Давайте посмотрим, каким образом можно открыть файлы с расширением CSV в Экселе.
Открываем CSV-файлы
Для начала давайте разберемся, что из себя представляют документы в данном формате.
CSV – аббревиатура, которая расшифровывается как “Comma-Separated Values” (на русском языке означает “значения, разделенные запятыми”).
Как следует из названия, в таких документах используются разделители:
Во время открытия документа в Excel основная задача (проблема) заключается в выборе способа кодировки, примененного при сохранении файла. Если будет выбрана не та кодировка, скорее всего, пользователь увидит множество нечитаемых символов, и полезность информации будет сведена к минимуму. Помимо этого, ключевое значение имеет используемый разделитель. Например, если документ был сохранен в англоязычной версии, а затем его пытаются открыть в русскоязычной, скорее всего, качество отображаемой информации пострадает. Причина, как мы ранее отметили, заключается в том, что в разных версиях используются разные разделители. Давайте посмотрим, как избежать этих проблем и как правильно открывать файлы CSV.
Метод 1: двойным щелчком или через контекстное меню
Прежде, чем приступить к более сложным методам, давайте рассмотрим самый простой. Он применим только в тех случаях, когда файл был создан/сохранен и открывается в одной и той же версии программы, а значит, проблем с кодировкой и разделителями быть не должно. Здесь возможно два варианта, опишем их ниже.
Excel установлена как программа по умолчанию для открытия CSV-файлов
Если это так, открыть документ можно как и любой другой файл – достаточно просто дважды щелкнуть по нему.
Для открытия CSV-фалов назначена другая программа или не назначена вовсе
Алгоритм действия в таких ситуациях следующий (на примере Windows 10):
Независимо от того, какой из описанных выше способов был выбран, результатом будет открытие CSV-файла. Как мы упомянули выше, корректно отображаться содержимое будет только при соответствии кодировки и разделителей.
В остальных случаях может показываться нечто подобное:
Поэтому описанный метод подходит не всегда, и мы переходим к следующим.
Метод 2: применяем Мастер текстов
Воспользуемся интегрированным в программу инструментом – Мастером текстов:
Метод 3: через меню “Файл”
И последний метод, которым можно воспользоваться заключается в следующем:
Заключение
Таким образом, несмотря на кажущуюся сложность, программа Эксель вполне позволяет открывать и работать с файлами в формате CSV. Главное – определиться с методом реализации. Если при обычном открытии документа (двойным щелчком мыши или через контекстное меню) его содержимое содержит непонятные символы, можно воспользоваться Мастером текста, который позволяет выбрать подходящую кодировку и знак разделителя, что напрямую влияет на корректность отображаемой информации.
Преобразовываем CSV в Excel: как импортировать файлы CSV в электронные таблицы Excel
В этой статье Вы найдёте 2 простых способа преобразовать файл CSV в Excel. Кроме того, Вы узнаете, как импортировать в Excel несколько файлов CSV и как справиться с ситуациями, когда часть данных из файла CSV не отображается корректно на листе Excel.
Эта статья покажет Вам, как открывать CSV в Excel и как импортировать одновременно несколько файлов CSV. Мы также обозначим возможные подводные камни и предложим наиболее эффективные решения.
Как преобразовать CSV в Excel
Если Вам нужно вытащить какую-то информацию на лист Excel из базы данных Вашей компании, то первая же идея, что приходит на ум, – экспортировать базу данных в файл CSV, а затем импортировать файл CSV в Excel.
Существует 3 способа преобразования CSV в Excel: Вы можете открыть файл с расширением .csv непосредственно в Excel, дважды кликнуть по файлу в Проводнике Windows либо импортировать CSV в Excel, как внешний источник данных. Далее я подробно расскажу об этих трёх способах и укажу преимущества и недостатки каждого из них.
Как открыть файл CSV в Excel
Даже если файл CSV создан в другой программе, Вы всегда можете открыть его как книгу Excel при помощи команды Open (Открыть).
Если Вы открываете файл CSV, то Excel откроет его сразу же вставив данные в новую книгу Excel. Если Вы открываете текстовый файл (.txt), то Excel запустит Мастер импорта текстов. Подробнее об этом читайте в разделе Импортируем CSV в Excel.
Замечание: Когда Microsoft Excel открывает файл CSV, то для того, чтобы понять, как именно импортировать каждый столбец данных, он использует настройки форматирования, заданные по умолчанию.
Если данные соответствуют хотя бы одному из следующих пунктов, то воспользуйтесь Мастером импорта текстов:
Чтобы заставить Excel запустить Мастер импорта текстов, Вы можете либо изменить расширение фала с .csv на .txt (прежде чем открывать файл), либо импортировать CSV в Excel так, как это будет описано далее.
Как открыть файл CSV при помощи Проводника Windows
Это самый быстрый способ открыть CSV в Excel. В Проводнике Windows дважды кликните по файлу .csv, и он откроется как новая книга Excel.
Однако, этот способ сработает только в том случае, если приложение Microsoft Excel установлено как программа, заданная по умолчанию, для открытия файлов .csv. Если это так, то Вы будете видеть знакомую иконку в Проводнике Windows рядом с именем файла.
Если Excel не является программой по умолчанию, вот как Вы можете это исправить:
Как импортировать CSV в Excel
Этим способом Вы можете импортировать данные из файла .csv в существующий или в новый лист Excel. В отличие от предыдущих двух способов, он не просто открывает CSV в Excel, а именно конвертирует формат .csv в .xlsx (если Вы используете Excel 2007, 2010 или 2013) или .xls (в версиях Excel 2003 и более ранних).
Прежде, чем мы двинемся дальше, пожалуйста, посмотрите на снимок экрана ниже, на нем показан исходный файл CSV и желаемый результат в Excel. Надеюсь, это поможет Вам лучше понять, почему мы выбираем те или иные параметры настроек в последующем примере.
Совет: Если в Вашем файле CSV используется последовательно более одной запятой или другого символа-разделителя, то поставьте галочку для параметра Treat consecutive delimiters as one (Считать последовательные разделители одним), чтобы избежать появления пустых ячеек.
Совет: Вы можете нажать кнопку Properties (Свойства), чтобы настроить дополнительные параметры, такие как обновление, разметка и форматирование для импортированных данных.
Замечание: Если Ваш файл CSV содержит численные данные или даты, Excel может преобразовать их с ошибками. Чтобы изменить формат импортированных данных, выберите столбец (столбцы), в которых возникли ошибки, кликните по ним правой кнопкой мыши и в контекстном меню выберите Format cells (Формат ячеек).
Преобразование CSV в Excel: проблемы и решения
Формат CSV используется уже более 30 лет, но несмотря на его длительную историю, он никогда не был официально задокументирован. Название CSV (Comma-Separated Values) возникло из-за использования запятых для разделения полей данных. Но это в теории. На самом деле, множество так называемых CSV-файлов используют другие символы для разделения данных, например:
Некоторые вариации файлов CSV разделяют поля данных одинарными или двойными кавычками, другие требуют маркер последовательности байтов из Юникода (BOM), например, UTF-8, для корректной интерпретации Юникода.
Файл CSV отображается в Excel неправильно
Признаки: Вы пытаетесь открыть файл CSV в Excel, и все данные попадают в первый столбец.
Причина: Корень проблемы кроется в том, что в Ваших региональных и языковых настройках Windows и в Вашем файле CSV установлены различные разделители. В Северной Америке и некоторых других странах разделителем полей списка по умолчанию является запятая. В то время как в Европейских странах запятая используется как разделитель десятичных разрядов, а разделителем полей списка является точка с запятой.
Решение: Есть несколько возможных решений этой проблемы. Вы можете быстро просмотреть приведённые ниже рекомендации и выбрать наиболее подходящие для конкретно Вашей задачи.
Замечание: Все показанные решения изменяют разделитель только для данного файла CSV. Если Вы хотите раз и навсегда изменить разделитель, заданный по умолчанию, то Вам подойдёт следующее решение.
Примечание переводчика: Данные настройки приведены для английской локализации Excel (и ряда других стран). Для русской локализации привычнее будет использовать запятую в качестве разделителя целой и дробной части и точку с запятой для разделения элементов списка.
Замечание: Установка в Панели управления Windows символов-разделителей целой и дробной части и элементов списка изменит настройки символов, заданные по умолчанию, для всех программ на Вашем компьютере, а не только в Microsoft Excel.
Первые нули теряются при открытии файла CSV в Excel
Признаки: Ваш файл CSV содержит значения с первыми нулями, и эти нули теряются при открытии файла CSV в Excel.
Причина: По умолчанию, Microsoft Excel отображает файл CSV в формате General (Общий), в котором первые нули отсекаются.
На шаге 3 мастера выберите столбцы, содержащие значения с первыми нулями и измените формат этих столбцов на текстовый. Так Вы конвертируете Ваш файл CSV в Excel, сохранив нули на своих местах.
Excel преобразует некоторые значения в даты при открытии файла CSV
Признаки: Некоторые значения в Вашем файле CSV похожи на даты, и Excel автоматически преобразует такие значения из текстового формата в формат даты.
Причина: Как упоминалось выше, Excel открывает файл CSV в формате General (Общий), при этом значения, похожие на даты, преобразует из текстового формата в формат даты. Например, если Вы открываете файл CSV, содержащий логины пользователей, то запись «апр23» будет преобразована в дату.
Решение: Преобразуйте файл CSV в Excel при помощи Мастера импорта текстов. На шаге 3 мастера выберите столбцы с записями, похожими на даты, и измените формат столбца на текстовый.
Если Вам нужно достичь противоположного результата, то есть в определённом столбце преобразовать значения в даты, тогда установите формат Date (Дата) и выберите подходящий формат даты в выпадающем списке.
Как импортировать в Excel несколько файлов CSV
Думаю, Вы знаете, что Microsoft Excel позволяет открывать несколько файлов CSV при помощи команды Open (Открыть).
Этот способ простой и быстрый, и мы могли бы назвать его отличным, если бы не одно обстоятельство – каждый файл CSV открывается таким образом как отдельная книга Excel. На практике переключение туда-сюда между несколькими открытыми файлами Excel может быть крайне неудобным и обременительным.
Надеюсь, теперь Вы легко сможете преобразовать любой файл CSV в Excel. Если у Вас возникают какие-либо вопросы или сложности, смело пишите мне в комментариях. И благодарю за терпение каждого, кто осилил чтение этой длинной статьи до самого конца! 🙂