Структура файла CSV
Файл с разделителями-запятыми (CSV) представляет собой простой текстовый файл, который содержит список данных. Эти файлы часто используются для обмена данными между различными приложениями. Например, базы данных и менеджеры контактов часто поддерживают файлы CSV.
Эти файлы иногда могут называться символьно-разделенными значениями или файлами с разделителями-запятыми. В основном они используют запятую для разделения данных, но иногда используют другие символы, такие как точки с запятой. Идея состоит в том, что вы можете экспортировать сложные данные из одного приложения в файл CSV, а затем импортировать данные из этого файла CSV в другое приложение.
Вот пример такого строки с разделителями:
«Name»,»OperatingSystem»,»LastLogonDate»,»Modified»,»Enabled» ,»Ping»,»DistinguishedName» «DC01″,»Windows Server 2008 R2 Standard»,»02.11.2018 6:14:02″, «21.12.2018 15:56:16″,»Torge»,»False»,»CN=DC01, OU=ComputerStore,OU=root,DC=pyatilistnik,DC=org»
теперь представьте, что таких строк сотни или тысячи, а вы хотите все скопировать в ваш Exсel и по столбцам, вот тут вы и поймете, что одностроковый формат требует преобразования. Благо, это делается очень быстро и просто. Вот пример моего тестового csv файла.
Проблемы и их решения при открытии CSV
Поскольку формат не стандартизован, не всегда содержимое документа правильно отображается, при чтении файлов у пользователя могут возникать некоторые проблемы.
Некорректное отображение CSV
Одна из самых распространённых проблем – неупорядоченный текст, сбившийся в одну область, который пользователь видит при открытии файла вместо стройных столбцов и строк. Источник неприятности в таком случае стоит искать в региональных параметрах операционной системы и самого файла. Решение заключается в следующем:
- идём в «Параметры» сочетанием клавиш Win+I или через меню Пуск (на «Семёрке» переходим в Панель управления);
- открываем «Время и язык» – «Регион» – «Дополнительные параметры даты и времени» – «Региональные стандарты» и жмём внизу окна кнопку «Дополнительные параметры»;
- в окне настройки формата проверяем, чтобы напротив пункта «Разделитель целой и дробной части» стояла запятая, а напротив «Разделитель элементов списка» – точка с запятой. Если это не так, применяем нужные значения. Жмём «Ок». При этом помним, что указывать разделитель следует и в «Мастере текстов».
Исчезновение нулей в числовых данных
Ещё одна частая ошибка при открытии из Проводника – отсутствие нулей, которые должны быть вначале. Обусловлено это округлением чисел в Excel, и если вы встретились с такой проблемой, то открывайте CSV путём импортирования (см. выше), при этом потребуется выделить некорректные столбцы и указать текстовый формат данных столбца.
Подмена числовых значений датой
Суть проблемы та же, но числовые значения здесь подменяются датами. Решается вопрос аналогичным способом – отмечаем в «Мастере текста» неправильно отображающиеся столбцы и изменяем формат на текстовый.
Сложности, возникающие при работе с CSV, обусловлены плохой стандартизацией, и столкнуться с ними может любой пользователь, но с использованием программных инструментов открыть табличный документ можно независимо от применённого в исходнике разделителя.
8 ответов
65
При открытии CSV-файлов Excel будет использовать региональный параметр системы, называемый , чтобы определить, какой разделитель по умолчанию использовать.
В Windows вы можете изменить параметр в , как указано в веб-сайт поддержки Office :
Примечание : это работает только в том случае, если десятичный символ также не обозначен как запятая (в соответствии с цитированием Википедии выше). Если это так, Excel будет not использовать запятую как разделитель List, даже если она выбрана. Для многих регионов, не входящих в США, запятая является десятичным символом по умолчанию.
В Mac OS X этот параметр, как представляется, выводится из установки десятичного разделителя (в Language & Region Системные настройки , перейдите в Дополнительно ). Если Decimal Separator является точкой, то разделитель CSV по умолчанию будет запятой, но если Decimal Separator является запятой, то разделитель CSV по умолчанию будет точкой с запятой.
Как вы сами сказали в комментарии, есть альтернатива для пользователей Mac, чтобы быстро просмотреть эти файлы CSV. Это плагин для Quick Look , называемый quicklook-csv , который обрабатывает обнаружение разделителей.
73
Если вы не хотите изменять формат файла и ТОЛЬКО для таргетинга на Excel, вы можете использовать следующий трюк Excel, который поможет вам.
Добавьте новую строку вверху файла с текстом (включая кавычки), чтобы Excel мог открыть файл с помощью «,» в качестве разделителя списков.
Это очень простой трюк, чтобы не менять региональные настройки Windows и получать согласованный результат. Но это особый характер Excel.
9
Вам не нужны кавычки вокруг — пока это первая строка файла, в которой он будет работать, по крайней мере, с Excel 2016.
Я обнаружил, что если файл имеет разделитель табуляции, отлично работает с кавычками и без них.
1
Если разделитель в региональных настройках не является запятой, а точкой с запятой (голландский разделитель), переименуйте файл CSV в файл TXT. Щелкните правой кнопкой мыши файл TXT и выберите «Открыть с помощью» и выберите «Excel». В Excel выберите первый столбец, выберите данные в ленте и разделите текст на столбцы.
ИЛИ
Установите LibreOffice и откройте файл CSV с помощью LibreOffice Calc.
Обязательно проверьте CSV-файл в простом редакторе, например «Блокнот», чтобы убедиться, что он правильно отформатирован.
Я добавил этот ответ после того, как я решил глупую ошибку, в которой файлы CSV, созданные мной с помощью VB, не были открыты с отдельными столбцами в Excel. Я обнаружил, что так, как я написал строки, завернутые в каждую строку кавычками. Excel спрятал кавычки и показал всю строку в столбце A, создавая впечатление, что он игнорирует мои разделители запятой.
лучший способ — сохранить его в текстовом файле с расширением csv
Для голландцев у меня возникла проблема, что Excel 2008 в Windows 7 не придерживался RFC4180:
Файл с разделенной запятой правильно имел поля с запятой (голландский десятичный разделитель), заключенный в двойные кавычки. В голландском языке десятичный разделитель и разделитель списков , поэтому Excel не смог прочитать файл с , используемый для обоих (даже не после явного изменения разделителя списков на в панели управления).
Вход выглядит следующим образом:
Решение было задано @ user280725:
Используйте Блокнот для вставки в качестве первой строки:
(Это означает, что Примечание пользователя @zakinster в его решении больше не требуется применять.)
Интересно: если файл csv все еще загружен, если теперь вы установите локаль на US English на панели управления и сохраните файл, он будет преобразован в формат US English (запятая в качестве разделителя списков и точка в виде десятичного разделителя).
Принятый ответ правильный, но я визуальный человек. Вот каждый шаг в скриншоте, как это сделать в Windows 10.
Бонус-трек: проблемы при сохранении из Calc в .xlsx
Если сохраняете данные из Calc в экселевский формат .xlsx, имейте в виду — OO порой необъяснимо и масштабно теряет данные.
Белая пустошь, раскинувшаяся посередине, в оригинальном CSV-файле богато заполнена данными
Поэтому после сохранения я еще раз открываю файл и убеждаюсь, что данные на месте.
Если что-то потерялись, лечение — пересохранить из CSV в .xlsx. Или, если установлен Windows, импортнуть из CSV в Excel и сохранить оттуда.
После пересохранения обязательно еще раз проверяю, что все данные на месте и нет лишних пустых строк.
Если интересно работать с данными, посмотрите на наши вакансии. HFLabs почти всегда нужны аналитики, тестировщики, инженеры по внедрению, разработчики. Данными обеспечим так, что мало не покажется
Чем открыть CSV-файл на компьютере
Для работы с электронными таблицами предусмотрена специальная категория софта – табличные процессоры. С их помощью пользователь сможет выполнить такие действия как создание и редактирование CSV-файла, оформление и печать, создание многостраничных документов с таблицами, объединённых формулами, а также работать с таблицами как с базами данных, решая различные задачи, перевести файл в другой формат и многое другое.
Если вы ищете, чем открыть CSV, воспользуйтесь одной из популярных программ, рассмотренных далее. Они позволяют просматривать и редактировать таблицы на компьютере.
Microsoft Excel
Популярный продукт от компании Microsoft, входящий в состав офисного пакета. Если Microsoft Office устанавливался, то табличный процессор уже есть на компьютере и с его помощью можно открыть файл CSV независимо от версии программы, а также преобразовать существующую таблицу в собственный формат Excel. Чтобы табличные данные были корректно отображены с сохранением первоначальной структуры, необходимо правильно произвести первичную настройку.
Как открыть CSV в программе Excel:
- из Проводника путём нажатия ПКМ по файлу, выбора из контекстного меню опции «Открыть с помощью» и софта, работающего с таблицами (способ подходит, если Excel уже стоит на компьютере, при этом нет проблем с локализацией или отображением кириллицы);
- с помощью меню «Файл» в окне табличного процессора (жмём «Файл» – «Открыть», из Проводника находим элемент и открываем, после чего работа ведётся посредством запустившегося «Мастера текстов»);
- применение встроенного в Excel «Мастера текстов», чтобы импортировать данные.
ВНИМАНИЕ. Открытие CSV в Excel не означает, что файл изменил свой формат, то есть объект не преобразуется в «.xls» или «.xlsx»
Преобразование CSV выполняется, если импортировать данные из файла «.csv» в имеющийся или новый лист Excel.
Разберём пошагово, как выполняется импорт CSV в Microsoft Excel с настройками определённых опций:
- в окне Microsoft Excel выбираем пункт «Открыть» («File»), затем нажимаем «Обзор»;
- выставляем тип отображения элементов «Все файлы» и идём в папку с нужным объектом, открываем;
- если данные открылись без «Мастера текстов», не отображаются должным образом, то потребуется перейти в раздел «Данные» – «Получить данные» – «Из файла» – «Из тестового/CSV-файла». После этого из проводника нужно выбрать соответствующий элемент и открыть;
- выбираем разделитель, чтобы таблицы правильно отображались (разделить текст по столбцам можно, выбрав запятую или точку с запятой в зависимости от документа), а в строчке «Источник файла» выставим «Юникод (UTF-8)», затем жмём кнопку «Преобразовать данные»;
- попутно можете настроить и другие параметры, чтобы откорректировать документ, например, пункт «Управление столбцами» позволяет выполнить настройку формата данных столбцов;
- Нажимаем «Закрыть и загрузить», после чего документ откроется в новом листе.
СОВЕТ. Символом, разделяющим текст в русскоязычных и локализованных для русских версий ПО документах, является точка с запятой. Если же импортируется файл CSV, адаптированный под англоязычные стандарты, выбираем в качестве разделителя запятую.
Как создать CSV-файл в Microsoft Excel:
- открываем новую таблицу (если требуется конвертация готовой таблицы, перейдите к третьему пункту инструкции);
- прописываем заголовки, имена полей в верхнем ряду ячеек, затем вносим необходимые данные в таблицу под каждым из столбцов;
- когда все данные внесены, в меню «Файл» используем опцию «Сохранить как», выбираем тип файла из выпадающего списка (эти же действия выполняем, если потребовалось конвертировать таблицу в нужный формат);
- присваиваем имя файлу и жмём «Сохранить».
LibreOffice Calc
Ещё один многим известный табличный процессор – LibreOffice Calc. Он распространяется бесплатно в составе пакета LibreOffice.
Для открытия CSV нужно выполнить следующие действия:
- в меню программы жмём «Открыть файл» и выбираем файл посредством проводника;
- в следующем окне «Импорт текста» выставляем метод кодировки «Юникод (UTF-8)», указываем язык, а также настраиваем параметры разделителя в блоке ниже, после чего жмём «Ок»;
- содержимое CSV будет открыто в виде таблицы, доступны просмотр и редактирование.
OpenOffice Calc
Программный продукт Calc, входящий в состав пакета OpenOffice, похож на рассмотренные выше варианты, поддерживает формат CSV, а принцип работы с софтом аналогичен. Для взаимодействия с файлами выполняем шаги:
- в окне OpenOffice Calc выбираем объект нажатием соответствующей кнопки;
- выполняем первичные настройки (тип, язык, разделитель и т. п.);
- документ Calc запустится в обработанном виде.
Функция OPENROWSET в T-SQL
OPENROWSET – функция подключается к источнику данных и выполняет необходимый запрос.
У данной функции уже много параметров, с помощью которых мы указываем все необходимые сведения о подключении, которые требуются для доступа к источнику данных OLE DB.
Для того чтобы обратиться к Excel, можно написать следующий запрос.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;
Database=D:\TestExcel.xls',
);
Вместо можно в апострофах указать необходимый SQL запрос, например:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;
Database=D:\TestExcel.xls',
'SELECT ProductName, Price FROM ');
Преобразование CSV в Excel: проблемы и решения
Формат CSV используется уже более 30 лет, но несмотря на его длительную историю, он никогда не был официально задокументирован. Название CSV (Comma-Separated Values) возникло из-за использования запятых для разделения полей данных. Но это в теории. На самом деле, множество так называемых CSV-файлов используют другие символы для разделения данных, например:
- Табуляция – TSV-файлы (tab-separated values)
- Точка с запятой – SCSV-файлы (semicolon separated values)
Некоторые вариации файлов CSV разделяют поля данных одинарными или двойными кавычками, другие требуют маркер последовательности байтов из Юникода (BOM), например, UTF-8, для корректной интерпретации Юникода.
Это отсутствие стандартов порождает разнообразные проблемы, с которыми Вы можете столкнуться, пытаясь преобразовать файл Excel в CSV , и особенно, когда импортируете файл CSV в Excel. Давайте разберёмся с известными проблемами, начиная с самой распространённой.
Файл CSV отображается в Excel неправильно
Признаки: Вы пытаетесь открыть файл CSV в Excel, и все данные попадают в первый столбец.
Причина: Корень проблемы кроется в том, что в Ваших региональных и языковых настройках Windows и в Вашем файле CSV установлены различные разделители. В Северной Америке и некоторых других странах разделителем полей списка по умолчанию является запятая. В то время как в Европейских странах запятая используется как разделитель десятичных разрядов, а разделителем полей списка является точка с запятой.
Решение: Есть несколько возможных решений этой проблемы. Вы можете быстро просмотреть приведённые ниже рекомендации и выбрать наиболее подходящие для конкретно Вашей задачи.
Укажите правильный разделитель непосредственно в файле CSV. Откройте файл CSV в любом текстовом редакторе (подойдёт даже обычный блокнот) и в первой строке вставьте следующий текст
Обратите внимание, что это должна быть отдельная строка перед любыми другими данными: Чтобы установить разделитель запятую: sep=,
Чтобы установить разделитель точку с запятой: sep=;
Замечание: Все показанные решения изменяют разделитель только для данного файла CSV. Если Вы хотите раз и навсегда изменить разделитель, заданный по умолчанию, то Вам подойдёт следующее решение.
Примечание переводчика: Данные настройки приведены для английской локализации Excel (и ряда других стран). Для русской локализации привычнее будет использовать запятую в качестве разделителя целой и дробной части и точку с запятой для разделения элементов списка.
- Дважды нажмите ОК, чтобы закрыть диалоговые окна – всё готово! С этого момента Microsoft Excel будет открывать и отображать все файлы CSV (с разделителем запятой) корректно.
Замечание: Установка в Панели управления Windows символов-разделителей целой и дробной части и элементов списка изменит настройки символов, заданные по умолчанию, для всех программ на Вашем компьютере, а не только в Microsoft Excel.
Первые нули теряются при открытии файла CSV в Excel
Признаки: Ваш файл CSV содержит значения с первыми нулями, и эти нули теряются при открытии файла CSV в Excel.
Причина: По умолчанию, Microsoft Excel отображает файл CSV в формате General (Общий), в котором первые нули отсекаются.
Решение: Вместо того, чтобы открывать файл .csv в Excel, запустите, как мы это делали ранее, Мастер импорта текстов, чтобы конвертировать файл CSV в Excel.
На шаге 3 мастера выберите столбцы, содержащие значения с первыми нулями и измените формат этих столбцов на текстовый. Так Вы конвертируете Ваш файл CSV в Excel, сохранив нули на своих местах.
Excel преобразует некоторые значения в даты при открытии файла CSV
Признаки: Некоторые значения в Вашем файле CSV похожи на даты, и Excel автоматически преобразует такие значения из текстового формата в формат даты.
Решение: Преобразуйте файл CSV в Excel при помощи Мастера импорта текстов. На шаге 3 мастера выберите столбцы с записями, похожими на даты, и измените формат столбца на текстовый.
Если Вам нужно достичь противоположного результата, то есть в определённом столбце преобразовать значения в даты, тогда установите формат Date (Дата) и выберите подходящий формат даты в выпадающем списке.
XLS в CSV
Конвертировать XLS в CSV – онлайн и бесплатно – эта страница также содержит информацию о расширениях файлов XLS и CSV.
Перетащите сюда файлы или выберите ссылку
Или выберите новый формат
И согласиться с нашими Условиями
Files to Convert
Overall conversion/upload progress:
| Расширение файла | .xls |
| Категория | Document File |
| Описание | «Microsoft Excel» является коммерческим приложением электронных таблиц, написанным и распростроняемым «Microsoft» для «Microsoft Windows» и «Mac OS X». Версии «Excel» до 2007 года для сохранения файлов используют формат XLS. В него входят вычисления, графические инструменты, сводные таблицы и макро язык программирования «Visual Basic» для приложений. Он стал широко используемой электронной таблицей на данных платмормах, особенно после 5 версии в 1993 году, а также он почти полностью заменил «Lotus 1-2-3» в качестве стадарта индустрии для электронных таблиц. «Excel» является частью «Microsoft Office». Последние версии – 2010 для «Microsoft Windows» и 2011 для «Mac OS X». |
| Действия | XLS в CSV – Конвертировать файл сейчасView other document file formats |
| Технические детали | До 2007 года «Microsoft Excel» использовал собственный бинарный формат файлов, называемый «Binary Interchange File Format (BIFF)» в качестве главного формата. Используемый в качестве основы для XLS-файлов это постоянный формат, который поддерживает авторинг и манипулирование содержанием рабочих книг и шаблонов рабочих книг. Большинство версий «Microsoft Excel» могут читать форматы CSV, DBF, SYLK, DIF и др. |
| Ассоциированные программы | Microsoft ExcelMicrosoft Excel ViewerOpenOffice |
| Разработано | Microsoft |
| Тип MIME | application/vnd.ms-excel |
| Полезные ссылки | Подробнее о формате XLSКак отркыть файл XLS без «Microsoft Excel»Спецификация бинарного формата файлов «Microsoft Office»Convert XLS file |
| Расширение файла | .csv |
| Категория | Document File |
| Описание | Файл CSV – это способ сбора данных из любой таблицы, так что она может быть передана в качестве в другое таблично ориентированное приложение, такое как приложения реляционных баз данных. Microsoft Excel, таблица или приложение реляционная база данных, могут читать CSV файлы.CSV-файл иногда называют плоским файлом. |
| Действия | Convert CSV fileView other document file formats |
| Технические детали | В компьютерах, файл CSV содержит различные значения в таблице в виде серии ASCII (American Standard Code for Information Interchange) строк текста, которые затем организовали так, что каждое значение столбца отделяется запятой от следующего значения столбцов и каждая строка начинает новую строку. CSV является одним из примеров текстового файла с разделителями, который использует запятые для разделения значений (многие другие реализации CSV позволяют различным сепараторы, такие как слеши). Однако CSV отличается от других разделенные между собой форматов в использовании двойных кавычек вокруг полей, содержащих зарезервированные символы (например, запятыми или символами новой строки). Преимущество этого подхода заключается в том, что он позволяет передачу данных между различными приложениями. |
| Ассоциированные программы | The CSV file format is very simple and supported by almost all spreadsheets and database management systems |
| Разработано | Microsoft |
| Тип MIME | text/comma-separated-values |
| Полезные ссылки | Более детальная информация о CSV-файлахКак использовать CSV-файлыПрограммы, которые открывают CSV-файлыConvert CSV file |
Преобразование файлов XLS
Используя Zamzar можно конвертировать файлы XLS во множество других форматов
- xls в bmp (Windows bitmap)
- xls в csv (Comma Separated Values)
- xls в gif (Compuserve graphics interchange)
- xls в html (Hypertext Markup Language)
- xls в html4 (Hypertext Markup Language)
- xls в html5 (Hypertext Markup Language)
- xls в jpg (JPEG compliant image)
- xls в mdb (Microsoft Access Database)
- xls в numbers (Apple iWork Numbers Spreadsheet)
- xls в numbers09 (Apple iWork ’09 Numbers Spreadsheet)
- xls в ods (OpenDocument spreadsheet)
- xls в pdf (Portable Document Format)
- xls в png (Portable Network Graphic)
- xls в rtf (Rich Text Format)
- xls в tiff (Tagged image file format)
- xls в txt (Text Document)
- xls в xlsx (Microsoft Excel 2007 Spreadsheet)
- xls в xml (Extensible Markup Language)
Файл CSV открывается в один столбец в Excel
Симптомы. При открытии CSV-файла в Excel все данные отображаются в одном столбце.
Причина. Чтобы разделить данные на столбцы, Excel использует разделитель списка, установленный в региональных настройках Windows. Это может быть как запятая (в Северной Америке и некоторых других странах), так и точка с запятой (в странах Европы). Если разделитель, используемый в конкретном CSV-файле, отличается от разделителя по умолчанию, этот файл открывается в одном столбце.
Решения. В этом случае есть несколько возможных решений, включая макросы VBA или глобальное изменение настроек Windows. Мы покажем, как быстро решить эту проблему, не меняя разделитель списка по умолчанию на вашем компьютере, чтобы ни одно из ваших приложений не пострадало.
Изменить разделитель в файле CSV
Чтобы Excel мог читать CSV с другим разделителем, вы можете определить разделитель непосредственно в этом файле. Для этого откройте файл в любом текстовом редакторе (Блокнот подойдет) и добавьте в первую строку приведенный ниже текст
Обратите внимание, это должна быть отдельная строка перед любыми другими данными:
- Для разделения запятой: sep=,
- Для разделения точкой с запятой: sep=;
Точно так же вы можете установить любой другой пользовательский разделитель — просто введите его после знака равенства.
Определив соответствующий разделитель, вы теперь можете открыть файл обычным способом, из самого Excel или из проводника Windows.
Укажите разделитель при импорте файла CSV в Excel
Вместо того, чтобы открывать CSV-файл в Excel, импортируйте его с помощью мастера импорта текста (во всех версиях) или Power Query (в Excel 365 — 2016).
Мастер импорта текста предоставляет несколько вариантов разделителей на шаге 2. Как правило, вы должны выбрать:
- Запятая для файлов значений, разделенных запятыми
- Вкладка для текстовых файлов
- Точка с запятой для файлов значений, разделенных точкой с запятой
Если вы не уверены, какой разделитель содержится в ваших данных, попробуйте разные разделители и посмотрите, какой из них работает прямо в предварительном просмотре данных.
При создании подключения Power Query вы можете выбрать разделитель в диалоговом окне предварительного просмотра:
Подробные пошаговые инструкции см. в приведенных выше примерах.
Разделить ячейки с помощью функции «Текст в столбцы»
Если ваши данные уже перенесены в Excel, вы можете разделить их на разные столбцы с помощью кнопки Текст в столбцы особенность. По сути, он работает как мастер импорта текста: вы выбираете разделитель и Предварительный просмотр данных отражает изменения на лету:
Подробную информацию см. в разделе Как разделить ячейки в Excel.
Настройка кодировки в Excel
1. Проверьте выбранную кодировку
Перед импортом CSV-файла в Excel важно убедиться, что выбрана правильная кодировка. Изначально по умолчанию установлены кодировки в соответствии с языком операционной системы, на которой запущен Excel
Однако, если CSV-файл содержит данные на другом языке, возможно потребуется изменить кодировку.
2. Измените кодировку для импорта CSV-файла
Если CSV-файл содержит данные на другом языке, вы можете изменить кодировку перед импортом. Для этого в Excel выберите «Файл» > «Открыть», выберите ваш CSV-файл и нажмите кнопку «Открыть». В появившемся окне выберите нужную кодировку в строке «Кодировка». Если кодировки не совпадают, Excel автоматически предложит изменить кодировку CSV-файла.
3. Измените кодировку для сохранения файла
Если при сохранении CSV-файла в Excel кодировка была выбрана неправильно, текст может быть записан неправильно. Чтобы изменить кодировку при сохранении файла, выберите «Файл» > «Сохранить как», введите имя файла, выберите нужную кодировку в строке «Кодировка» и нажмите кнопку «Сохранить». Это позволит сохранить файл с выбранной кодировкой.
Также, при сохранении CSV-файла в Excel, следует убедиться, что разделитель — запятая («,») или точка с запятой («;») — соответствует тому, что указано в CSV-файле. Если разделитель не совпадает с тем, что в CSV-файле, данные могут быть записаны неправильно.
Побеждаем порчу данных правильным импортом
Если серьезно, в бедах виноват не Excel целиком, а неочевидный способ импорта данных в программу. По умолчанию Excel применяет к данным в загруженном CSV-файле тип «General» — общий. Из-за него программа распознает цифровые строки как числа. Такой порядок можно победить, используя встроенный инструмент импорта.
Запускаю встроенный в Excel механизм импорта.
В меню это «Data → Get External Data → From Text».
Выбираю CSV-файл с данными, открывается диалог.
В диалоге кликаю на тип файла Delimited (с разделителями). Кодировка — та, что в файле, обычно определяется автоматом. Если первая строка файла — шапка, о.
Перехожу ко второму шагу диалога.
Выбираю разделитель полей (обычно это точка с запятой — 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 оставит исходные данные в покое. Но это не самая твердая гарантия, поэтому мы после сохранения обязательно проверяем файл через текстовый просмотрщик.
Комментарии
Если нужно чтобы выводились данные до первой пустой ячейки первого столбца.: После строки If Len(Range2CSV) > 50000 Then buffer$ = buffer$ & Range2CSV: Range2CSV = “” Добавляем строку: If arr(i + 1, 1) = “” Then i = UBound(arr, 1) ‘Если значение первой ячейки следующей строки массива пустое, то завершаем цикл
Еще одна проблема если в ячейке ошибка, то функция вылетает не выдавая никаких ошибок. Будьте внимательны, делайте так, чтобы в выводимой области не было ошибок типа #Н/Д
Автору спасибо! Много полезной инфы на сайте.
Спасибо огромное за помощь!
Доброго времени суток!
А как сделать,чтоб значения в ячейках выгружались в числовом или денежно формате с двумя знаками после запятой? Вот например есть 4499795,40 выгружается 4499795,4 или 900 000,00 выгружается 900000
Здравствуйте, Виталий Возможно, кодировка нужна другая. Перекодировать CSV файл (например, в UTF-8) можно этой функцией: http://ExcelVBA.ru/code/encode
Всё хорошо. Только при импорте файла csv в престашоп возникает ошибка . С чем может быть связана?
спасибо! теперь запускается.
подскажите, почему таблица остается таблицей, а не переходит в вид столбца в выгружаемом файле? при этом, если в строке CSVtext$ = Range2CSV(ra, “;”) поменять точку с запятой на запятую, то таблица становится столбцом, но запятые в цифрах при этом путаются с запятыми-разделителями. если же поставить точку, то она будет путаться с точками в дате.
не понимаю, как работает эта последняя функция, поэтому не знаю, как это победить
В статье дана ссылка на функцию SaveTXTfile Надо было код этой функции тоже вставить в ваш файл – и всё сразу заработает.
на SaveTXTfile пишет Sub or Function not defined
Ну так сделайте цикл, в котором будут просматриваться все строки таблицы.
Пример кода написать не могу, не видя ваш файл. Если сами не справитесь – можете заказать разработку такого макроса (оформите заказ, прикрепив вашу таблицу, и разъяснив, что и куда должно выгружаться)
Подскажите, пожалуйста, как сделать, чтоб выгружать не сплошной диапазон, а только строки, у которых (например) в 11 колонке написано имя формируемого файла? В идеале будет генерироваться несколько файлов, имена файлов которых прописаны в 11 колонке. В диапазоне возможно наличие строк, у которых значение равно цифре 0
Спасибо, за оперативный ответ. В новом файле все заработало. Буду искать где были конфликты. Отдельное спасибо за полезный ресурс.
Не должна эта строка выдавать ошибку, если только мой макрос не конфликтует с существующими в том же файле вашими макросами.
Попробуйте код на новом файле (в котором нет других макросов), и заново скопируйте необходимые макросы с моего сайта. Если в новом файле проблема исчезнет – ищите проблему несовместимости в своих макросах.
В строке txt = “”: For j = LBound(arr, 2) To UBound(arr, 2): txt = txt & ColumnsSeparator$ & arr(i, j): Next j Выделяет (txt =) с коментарием: Expected function or variable
Спасибо за оперативный ответ, попробуем
Ну почему же “код должен быть чисто своим”? Я вот и чужой код часто использую – просто не удивляюсь, если он работает не совсем так, как хочелось бы, а дорабатываю его под свои нужды.
Я использовал функцию только для выгрузки диапазонов, заведомо не содержащих символа разделителя в обрабатываемых ячейках. Если такие символы присутствуют – надо знать, как их экранировать.
Тут есть много вариантов:
- предварительно заменить разделитель в ячейке на какой-то другой символ
- заменить разделитель на другой символ только в выгрузке CSV (в ячейке оставить как было)
- экранировать сам символ разделителя (например, текст 123;456 заменить на 123″;”456 или на 123/;456)
- экранировать всю ячейку в выгрузке (например, текст 123;456 заменить на “123;456”)
- и т.д. и т.п. (при нескольких разделителях в ячейке появляются ещё варианты)
Предусматривать все эти варианты в макросе не очень хочется – объём кода заметно увеличится, а желающих внести какие-то дополнительные “навороты” только прибавится.
В вашем случае всё решалось заменой одной строки кода:
Надо вместо arr(i, j) подставить некую функцию, которая произведёт изменения текста в элементе массива при наличии в нём символов разделителей.
к сожалению не совсем корректно работает Function Range2CSV – если внутри ячейки содержится разделитель (у меня это была сложная гиперссылка) -исходнй ЦээСВэ искажается – при копипасте исохранить в формате csv -всё прекрасно
ну даже не знаю говорить ли спасибо? полдня промудохался СПАСИБО – будет Муку наука – код должен быть чисто своим