Эксель сохранить с разделителем
Обновлено: 06.07.2024
CSV является стандартом де-факто для связи между собой разнородных систем, для передачи и обработки объемных данных с «жесткой», табличной структурой. Во многих скриптовых языках программирования есть встроенные средства разбора и генерации, он хорошо понятен как программистам, так и рядовым пользователям, а проблемы с самими данными в нем хорошо обнаруживаются, как говорится, на глаз.
История этого формата насчитывает не менее 30 лет. Но даже сейчас, в эпоху повального использования XML, для выгрузки и загрузки больших объемов данных по-прежнему используют CSV. И, несмотря на то, что сам формат довольно неплохо описан в RFC, каждый его понимает по-своему.
В этой статье я попробую обобщить существующие знания об этом формате, указать на типичные ошибки, а также проиллюстрировать описанные проблемы на примере кривой реализации импорта-экспорта в Microsoft Office 2007. Также покажу, как обходить эти проблемы (в т.ч. автоматическое преобразование типов Excel-ом в DATETIME и NUMBER) при открытии .csv.
Начнем с того, что форматом 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, что было бы сильно логичнее.
Теперь посмотрим, как на это смотрит Outlook. Попробуем импортировать эти файлы из него, указав такие же источники данных. Outlook 2007: Файл -> Импорт и экспорт… -> Импорт из другой программы или файла. Далее выбираем формат данных: «Значения, разделенные запятыми (Windows)» и «Значения, разделенные табуляцией (Windows)».
| «Значения, разделенные табуляцией(Windows)» | Скармливаем аутлуку файл tsv, с разделенными табуляцией значениями и. — чтобы вы думали. Outlook склеивает поля и табуляцию не замечает. Заменяем в файле табуляцию на запятые и, как видим, поля уже разбирает, молодец. |
| «Значения, разделенные запятыми (Windows)» | А вот аутлук как раз понимает все верно. Comma — это запятая. Поэтому ожидает в качестве разделителя запятую. А у нас после экселя — точка с запятой. В итоге аутлук распознает все неверно. |
Два майкрософтовских продукта не понимают друг друга, у них напрочь отсутствует возможность передать через текстовый файл структурированные данные. Для того, чтобы все заработало, требуются «пляски с бубном» программиста.
Мы помним, что Microsoft Excel умеет работать с текстовыми файлами, импортировать данные из CSV, но в версии 2007 он делает это очень странно. Например, если просто открыть файл через меню, то он откроется без какого-либо распознавания формата, просто как текстовый файл, целиком помещенный в первую колонку. В случае, если сделать дабл-клик на CSV, Excel получает другую команду и импортирует CSV как надо, не задавая лишних вопросов. Третий вариант — вставка файла на текущий лист. В этом интерфейсе можно настраивать разделители, сразу же смотреть, что получилось. Но одно но: работает это плохо. Например, Excel при этом не понимает закавыченных переводов строк внутри полей.
Более того, одна и та же функция сохранения в CSV, вызванная через интерфейс и через макрос, работает по-разному. Вариант с макросом не смотрит в региональные настройки вообще.
Стандарта CSV как такового, к сожалению, нет, но, между тем, существует т.н. memo. Это RFC 4180 года, в котором описано все довольно толково. За неимением ничего большего, правильно придерживаться хотя бы RFC. Но для совместимости с Excel следует учесть его собенности.
Вот краткая выжимка рекомендаций RFC 4180 и мои комментарии в квадратных скобках:
- между строками — перевод строки CRLF [на мой взгляд, им не стоило ограничивать двумя байтами, т.е. как CRLF (0×0D, 0×0A), так и CR 0×0D]
- разделители — запятые, в конце строки не должно быть запятой,
- в последней строке CRLF не обязателен,
- первая строка может быть строкой заголовка (никак не помечается при этом)
- пробелы, окружающие запятую-разделитель, игнорируются.
- если значение содержит в себе CRLF, CR, LF (символы-разделители строк), двойную кавычку или запятую (символ-разделитель полей), то заключение значения в кавычки обязательно. В противном случае — допустимо.
- т.е. допустимы переводы строк внутри поля. Но такие значения полей должны быть обязательно закавычены,
- если внутри закавыченной части встречаются двойные кавычки, то используется специфический квотинг кавычек в CSV — их дублирование.
Вот в нотации ABNF описание формата:
Также при реализации формата нужно помнить, что поскольку здесь нет указателей на число и тип колонок, поскольку нет требования обязательно размещать заголовок, здесь есть условности, о которых необходимо не забывать:
- строковое значение из цифр, не заключенное в кавычки может быть воспринято программой как числовое, из-за чего может быть потеряна информация, например, лидирующие нули,
- количество значений в каждой строке может отличаться и необходимо правильно обрабатывать эту ситуацию. В одних ситуациях нужно предупредить пользователя, в других — создавать дополнительные колонки и заполнять их пустыми значениями. Можно определиться, что количество колонок задается заголовком, а можно добавлять их динамически, по мере импорта CSV,
- Квотить кавычки через «слэш» не по стандарту, делать так не надо.
- Поскольку типизации полей нет, нет и требования к ним. Разделители целой и дробной частей в разных странах разные, и это приводит к тому, что один и тот же CSV, сгенрированный приложением, в одном экселе «понимается», в другом — нет. Потому что Microsoft Office ориентируется на региональные настройки Windows, а там может быть что угодно. В России там указано, что разделитель — запятая,
- Если CSV открывать не через меню «Данные», а напрямую, то Excel лишних вопросов не задает, и делает как ему кажется правильным. Например, поле со значением 1.24 он понимает по умолчанию как «24 января»
- Эксель убивает ведующие нули и приводит типы даже тогда, когда значение указано в кавычках. Делать так не надо, это ошибка. Но чтобы обойти эту проблему экселя, можно сделать небольшой «хак» — значение начать со знака «равно», после чего поставить в кавычках то, что необходимо передать без изменения формата.
- У экселя есть спецсимвол «равно», который в CSV рассматривается как идентификатор формулы. То есть, если в CSV встретится =2+3, он сложит два и три и результат впишет в ячейку. По стандарту он это делать не должен.
Пример валидного CSV, который можно использовать для тестов:
точно такой же SCSV:
Первый файлик, который реально COMMA-SEPARATED, будучи сохраненным в .csv, Excel-ом не воспринимается вообще.
Второй файлик, который по логике SCSV, экселом воспринимается и выходит вот что:
- Учлись пробелы, окружающие разделители
- Последний столбец вообще толком не распознался, несмотря на то, что данные в кавычках. Исключение составляет строка с «Петровым» — там корректно распозналось 1,24.
- В поле индекс Excel «опустил» ведущие нули.
- в самом правом поле последней строки пробелы перед кавычками перестали указывать на спецсимвол
Если же воспользоваться функционалом импорта (Данные -> Из файла) и обозвать при импорте все поля текстовыми, то будет следующая картина:
С приведением типов сработало, но зато теперь не обрабатываются нормально переводы строк и осталась проблема с ведущими нулями, кавычками и лишними пробелами. Да и пользователям так открывать CSV крайне неудобно.
Есть эффективный способ, как заставить Excel не приводить типы, когда это нам не нужно. Но это будет CSV «специально для Excel». Делается это помещением знака «=» перед кавычками везде, где потенциально может возникнуть проблема с типами. Заодно убираем лишние пробелы.
И вот что случаеся, если мы открываем этот файлик в экселе:
Для нас это обычное дело и простое преобразование книги в файл CSV. Но как насчет сохранения Excel в виде файла с разделителями по вертикальной линии? В этой статье подробно описан метод сохранения файла Excel в виде файла с разделителями каналов.
Сохранить файл Excel как файл с разделителями каналов
Чтобы сохранить книгу Excel в виде файла с разделителями-вертикальными чертами, сделайте следующее.
1. Откройте Панель управления на твоем компьютере. в Панель управления окно, выберите Категория из Просмотр раскрывающийся список, затем щелкните Часы, язык и регион вариант, как показано ниже.
2. в Часы, язык и регион окна, нажмите Регион (или регион и язык) на правой стороне. Смотрите скриншот:
3. в Регион (или Язык и региональные стандарты), щелкните Дополнительные настройки… как показано на скриншоте ниже.
4. Затем Настроить формат диалоговое окно открывается. Удалите запятую в Разделитель списка на вкладке "Числа", затем нажмите Shift + \ вместе, чтобы ввести | разделитель в Разделитель списка поле и, наконец, щелкните OK кнопка. Смотрите скриншот:
5. Нажмите OK кнопку, когда он вернется в Регион (или Язык и региональные стандарты) диалоговое окно.
6. Откройте книгу и перейдите к листу, который необходимо сохранить как файл с разделителями каналов, затем щелкните Файл > Сохранить как > Приложения. Смотрите скриншот:
7. в Сохранить как окно, вам необходимо:
7.1) Выберите папку для сохранения файла разделителя каналов;
7.2) Назовите файл как вам нужно в Имя файла коробка;
7.3) Выбрать CSV (с разделителями-запятыми) из Сохранить как выпадающий список.
7.4) Нажмите Сохраните кнопка. Смотрите скриншот:
8. Затем появится окно с подсказкой, нажмите OK кнопку.
Теперь текущий рабочий лист преобразован в файл с разделителями каналов. Если вы хотите сохранить все листы как файлы с разделителями, повторите шаги с 6 по 8, описанные выше, чтобы сохранить их по отдельности, используя разные имена файлов для каждого файла.
Чтобы проверить файл с разделителями каналов, щелкните его правой кнопкой мыши и выберите Открыть с помощью > Блокнот как показано на скриншоте ниже:
Затем вы можете увидеть, что значения разделены разделителем вертикальной черты, как показано ниже.
Вы когда-нибудь пробовали преобразовать текстовый файл в файл Excel с разделителями? В этой статье я расскажу о методах решения этой проблемы.
Импортировать текстовый файл на лист с разделителем или без него
Преобразование текстового файла в лист с разделителем, открыв
Чтобы преобразовать текстовый файл в лист, вы можете использовать утилиту Open в Excel.
1. Нажмите Файл > Откройте > Приложения. Смотрите скриншот:
2. Затем в Откройте диалоговое окно, выберите Текстовые файлы в раскрывающемся списке рядом с Имя файла, и выберите используемый текстовый файл. Смотрите скриншот:
3. Нажмите ОткройтеИ в Мастер импорта текста диалог, проверьте разграниченный вариант и нажмите Следующая чтобы проверить разделитель, который вы используете для разделения текста, и продолжайте щелкать Следующая чтобы указать используемый формат. Смотрите скриншот:
 |
 |
 |
4. Нажмите Завершить, и новый лист новой книги был создан с текстами.
Импортировать текстовый файл на лист с разделителем или без него
Если вы хотите импортировать текстовый файл в определенный диапазон листа с разделителем или без разделителя, как вам нужно, вы можете попробовать Вставить файл в курсор полезности Kutools for Excel решить эту задачу.
После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите ячейку, в которую вы поместите содержимое импортированного текстового файла, и нажмите Kutools Plus > Импорт Экспорт > Вставить файл в курсор. Смотрите скриншот:
2. в Вставить файл в курсор диалоговое окно, нажмите Приложения показать Выберите файл для вставки в позицию курсора ячейки диалоговое окно, выберите Текстовые файлы из раскрывающегося списка рядом с Имя файла, выберите нужный файл и нажмите Откройте. Смотрите скриншот:
3. Нажмите OK, то текстовый файл был вставлен в помещенную ячейку без разделителя.
Примечание:
ЕСЛИ вы хотите разделить текст на ячейки с разделителем, выберите текст и нажмите Kutools > Слияние и разделение > Разделить клетки. Смотрите скриншот:
Затем проверьте параметры, которые вам нужны в Тип и Разделить на разделы. Смотрите скриншот:
Нажмите на Ok чтобы выбрать ячейку для размещения текстов.
Нажмите на OK в конце.
Обычно вы можете быстро и легко экспортировать рабочий лист в текстовый файл в Excel с помощью функции «Сохранить как». Но пробовали ли вы когда-нибудь сохранять данные каждого столбца в текстовый файл отдельно с листа Excel? В этой статье я расскажу о том, как решить эту задачу как можно быстрее.
Экспорт нескольких столбцов в отдельные текстовые файлы с кодом VBA
Следующий код VBA может помочь вам сохранить данные каждого столбца в текстовый файл отдельно в Excel, пожалуйста, сделайте следующее:
1. Удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модуль, и вставьте следующий макрос в Модуль Окно.
Код VBA: экспорт нескольких столбцов в отдельные текстовые файлы:
3. После вставки кода нажмите F5 ключ для запуска этого кода и Браузер появится диалоговое окно, затем выберите папку для размещения экспортированных текстовых файлов, см. снимок экрана:
4. Затем нажмите OK кнопку, и данные каждого столбца на активном листе были экспортированы в текстовый файл индивидуально, см. снимок экрана:
Экспорт нескольких листов в отдельные файлы text / pdf / csv с помощью Kutools for Excel
Если вам нужно экспортировать несколько листов в отдельные текстовые, PDF- или CSV-файлы, Kutools for ExcelАвтора Разделить книгу функция может оказать вам услугу.
Внимание : Чтобы применить это Разделить книгу, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.После установки Kutools для Excel, выполните следующие действия:
1. Нажмите Kutools Plus > Workbook > Разделить книгу, см. снимок экрана:
2. В Разделить книгу диалоговом окне, отметьте рабочие листы, которые вы хотите сохранить как файлы txt, pdf, csv, а затем отметьте Сохранить как вариант, затем выберите тип файла, который вы хотите сохранить как, а затем нажмите Трещина кнопку в отображаемом Просмотр папки диалоговом окне выберите папку для размещения экспортируемых файлов, см. снимок экрана:
3. Затем нажмите OK кнопку, и выбранные вами листы были экспортированы в индивидуальный конкретный формат файла, как вам нужно, см. снимок экрана:
Читайте также: