Excel изменить разделитель csv

Обновлено: 21.11.2024

Последнее обновление этого сообщения — 30 ноября 2021 г.

Недавно мне нужно было найти способ импортировать данные из старого файла Excel в систему с пользовательским кодом, которая могла бы импортировать файлы CSV. Однако данные были довольно недезинфицированы — содержали запятые, точки с запятой и другие странные символы. Я мог бы определить разделитель, используемый в импортированных данных, однако это было бы бесполезно, если бы я не мог определить его и в экспорте.

Однако в Excel все не так просто. Excel, как и другие приложения Microsoft Office, старается изо всех сил соблюдать региональные и языковые настройки компьютера, на котором он работает, — чаще всего с разочаровывающими результатами.

Однако, как и во многих других случаях, вы можете изменить значение разделителя, используемое при экспорте CSV, изменив эти настройки. И эта статья описывает, как это сделать!

Фон

Оглавление

Еще один день, еще один обходной путь. Если вас не интересуют предыстория и ПОЧЕМУ я столкнулся с этой проблемой, просто нажмите, чтобы сразу перейти к фактическому решению:
Как экспортировать CSV-файл с настраиваемым разделителем с помощью Microsoft Excel

Если вы все еще здесь, давайте углубимся в предысторию проблемы.

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

Что такое «CSV-файл»?
Обозначает «значения, разделенные запятыми» — текстовый файл, в котором для разделения значений используется запятая.

Каждая строка файла представляет собой запись данных. Каждая запись состоит из одного или нескольких полей, разделенных запятыми. Использование запятой в качестве разделителя полей (разделителя) является источником имени для этого формата файла.

Обычно это просто — просто «сохранить как» с другим форматом файла. Однако на этот раз я столкнулся с проблемой: данные включали как запятые («,»), так и точки с запятой («;»), которые являются типичными разделителями в файлах CSV. Кроме того, я не мог заключить значения в кавычки из-за ограничений функций экспорта и импорта. Поэтому мне нужно было использовать другой символ, например, вертикальную черту («|»).

Вместо того, чтобы настраивать разделитель в экспорте (как в любом разумном редакторе электронных таблиц, если вы спросите меня!), Excel просто использует ваши региональные настройки и берет оттуда разделитель списка. Это означает, что в английском языке значения CSV будут ограничены запятыми, например:

Но в финском мы будем использовать точку с запятой, вот так:

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

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

Однако, к счастью, мы можем переопределить разделитель практически любым способом — и все это делается с помощью региональных настроек!

Решение

Я нашел несколько примеров того, как это сделать в Windows 7, и, очевидно, они помогут вам в конечном итоге найти свой путь и в Windows 10. Но всегда полезно также задокументировать точные шаги — приведенное ниже руководство должно быть точным для Windows 10 и «достаточно близким» для Windows 7. :)

Вот шаги в письменной форме – смотрите несколько полезных скриншотов ниже!

Необходимое время: 10 минут.

Появится несколько дополнительных опций!

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

Интересно, что в качестве разделителей можно использовать совершенно бессмысленные символы! В качестве примера возьмем картинку ниже — однажды это действительно потребовалось для рабочего проекта. Проект, который, вероятно, все еще находится в разработке… Использование ужасно странных символов в качестве разделителей списка возможно, но, вероятно, не рекомендуется.

Ссылки

Большое спасибо Барри Стивенсу за его запись в блоге на эту тему и Сами Лайхо (или, как он более известен, «Järjestelmänvalvoja») за то, что он заметил, что вы не можете использовать один и тот же символ в качестве десятичного разделителя и разделителя списка 😬

Антти Коскела — гордый кочевой разработчик полного стека из числа миллениалов и аборигенов цифровых технологий (достаточно забавных модных словечек?Достаточно забавных модных словечек!), который работает архитектором облачных решений в Etteplan Oyj, инженерной компании, в которой работает около 700 разработчиков, которые строят и чинят даже полуцифровые устройства.

Excel усерден. Эксель умный. Он тщательно изучает системные настройки машины, на которой работает, и делает все возможное, чтобы предугадать потребности пользователя… довольно часто с неутешительными результатами.

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

Какой разделитель Excel использует для CSV-файлов

Для обработки CSV-файлов Microsoft Excel использует разделитель списка, заданный в региональных настройках Windows.

В Северной Америке и некоторых других странах разделителем списка по умолчанию является запятая, поэтому вы получаете CSV с разделителями-запятыми.

В европейских странах запятая зарезервирована для десятичного символа, а в качестве разделителя списка обычно используется точка с запятой. Вот почему результат CSV разделен точкой с запятой.

Чтобы получить файл CSV с другим разделителем полей, примените один из подходов, описанных ниже.

Изменить разделитель при сохранении файла Excel в формате CSV

При сохранении книги в виде CSV-файла Excel разделяет значения с помощью разделителя списка по умолчанию. Чтобы заставить его использовать другой разделитель, выполните следующие действия:

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

В зависимости от того, какой разделитель вы хотите использовать, настройте параметры одним из следующих способов.

Чтобы преобразовать файл Excel в файл CSV, разделенный точкой с запятой, установите десятичный разделитель по умолчанию на запятую. Это заставит Excel использовать точку с запятой для разделителя списка (разделитель CSV):

Чтобы сохранить файл Excel в формате CSV с разделителями-запятыми, установите в качестве десятичного разделителя точку (точку). Это заставит Excel использовать запятую для разделителя списка (разделитель CSV):

Если вы хотите изменить разделитель CSV только для определенного файла, снова установите флажок Использовать системные настройки после экспорта книги Excel в CSV.

Примечание. Очевидно, что изменения, которые вы сделали в параметрах Excel, ограничены Excel. Другие приложения будут продолжать использовать разделитель списка по умолчанию, определенный в региональных настройках Windows.

Изменить разделитель при импорте CSV в Excel

Существует несколько способов импорта CSV-файла в Excel. Способ изменения разделителя зависит от выбранного вами метода импорта.

Указать разделитель прямо в CSV-файле

Чтобы Excel мог читать CSV-файл с разделителем полей, используемым в данном CSV-файле, вы можете указать разделитель непосредственно в этом файле. Для этого откройте файл в любом текстовом редакторе, например Блокноте, и введите следующую строку перед любыми другими данными:

Чтобы разделить значения запятой: sep=,
Чтобы разделить значения точкой с запятой: sep=;
Чтобы разделить значения вертикальной чертой: sep=|

Подобным образом вы можете использовать любой другой символ в качестве разделителя — просто введите символ после знака равенства.

После определения разделителя вы можете открыть текстовый файл в Excel, как обычно, из самого Excel или из проводника Windows.

Например, чтобы правильно открыть CSV-файл, разделенный точкой с запятой, в Excel, мы явно указываем, что разделителем полей является точка с запятой:

Выберите разделитель в мастере импорта текста

Еще один способ обработки CSV-файла с разделителем, отличным от разделителя по умолчанию, — импортировать файл, а не открывать его. В более ранней версии Excel 2013 это было довольно легко сделать с помощью Мастера импорта текста, расположенного на вкладке Данные в группе Получить внешние данные. . Начиная с Excel 2016, мастер удален с ленты как устаревшая функция. Однако вы все равно можете им воспользоваться:

Включить функцию "Из текста" (устаревшая версия).
Измените расширение файла с .csv на .txt, а затем откройте файл txt в Excel. Это автоматически запустит Мастер импорта текста.

На шаге 2 мастера вам предлагается выбрать один из предопределенных разделителей (табуляция, запятая, точка с запятой или пробел) или указать собственный:

Укажите разделитель при создании подключения Power Query

Microsoft Excel 2016 и выше предоставляет еще один простой способ импорта CSV-файла — подключение к нему с помощью Power Query. При создании подключения Power Query вы можете выбрать разделитель в диалоговом окне предварительного просмотра:

Глобально изменить разделитель CSV по умолчанию

Чтобы изменить разделитель списка по умолчанию не только для Excel, но и для всех программ, установленных на вашем компьютере, вам нужно сделать следующее:

По завершении перезапустите Excel, чтобы изменения вступили в силу.

Изменение системных настроек приведет к глобальным изменениям на вашем компьютере, которые повлияют на все приложения и все выходные данные системы. Не делайте этого, если вы не уверены на 100 % в результатах.
Если изменение разделителя негативно повлияло на работу какого-либо приложения или вызвало другие проблемы на вашем компьютере, отмените изменения. Для этого нажмите кнопку Сбросить в диалоговом окне Настроить формат (шаг 5 выше). Это удалит все сделанные вами настройки и восстановит системные настройки по умолчанию.

Изменение разделителя списка: предыстория и последствия

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

Во-первых, следует отметить, что в зависимости от страны Windows использует разные разделители по умолчанию. Это связано с тем, что в разных странах мира большие числа и десятичные дроби записываются по-разному.

В США, Великобритании и некоторых других англоязычных странах, включая Австралию и Новую Зеландию, используются следующие разделители:

Десятичный символ: точка (.)

Символ группировки цифр: запятая (,)

Разделитель списка: запятая (,)

В большинстве европейских стран разделителем списка по умолчанию является точка с запятой (;), поскольку в качестве десятичной точки используется запятая:

Десятичный символ: запятая (,)

Символ группировки цифр: точка (.)

Разделитель списка: точка с запятой (;)

Например, вот как пишется две тысячи долларов и пятьдесят центов в разных странах:

США и Великобритания: 2000,50 долларов США

Как все это связано с разделителем CSV? Дело в том, что разделитель списка (разделитель CSV) и десятичный символ должны быть двумя разными символами. Это означает, что установка запятой в качестве разделителя списка потребует изменения десятичного символа по умолчанию (если установлена запятая). В результате числа будут отображаться по-разному во всех ваших приложениях.

Кроме того, разделитель списка используется для разделения аргументов в формулах Excel. Как только вы измените его, скажем, с запятой на точку с запятой, разделители во всех ваших формулах также изменятся на точки с запятой.

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

Вот как вы можете открывать или сохранять файлы CSV с разными разделителями в Excel. Спасибо за внимание и увидимся на следующей неделе!

Вас также может заинтересовать

5 комментариев к "Как изменить разделитель Excel CSV на запятую или точку с запятой"

Эта инструкция не работает:
"Чтобы сохранить файл Excel как файл CSV с разделителями-запятыми, установите десятичный разделитель на точку (точку). Это заставит Excel использовать запятую для разделителя списка (разделитель CSV) :"

Я только что повторно протестировал его в своем Excel 365 — он работает так, как описано. Какую версию Excel вы используете?

У меня была та же проблема, я не мог изменить разделитель списка, изменив его в Excel 2016 Professional. Файл, который я пытался преобразовать, должен был использоваться в другой программе, которая использует запятую в качестве разделителя списка (forScore).
Подтверждаю второй вариант, глобальное изменение разделителя списка. У этого есть недостаток, заключающийся в том, что он мешает другим программам моделирования. Итак, после преобразования файла мне пришлось восстановить конфигурацию, чтобы другие мои симуляторы снова работали правильно.

Как вы тестировали первый подход?

Вот что я сделал:

- Выполнены действия, описанные в разделе «Изменение разделителя при сохранении файла Excel в формате CSV». В частности, установите для десятичного разделителя точку (.), а для разделителя тысяч — запятую (,).
- Книга сохранена в виде CSV-файла.
- Открыл файл CSV в Блокноте, чтобы проверить, какой разделитель используется на самом деле. В моем случае результатом всегда являются значения, разделенные запятыми.

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

Больше никакой ЗАПЯТОЙ ДРАМА! В этом видео я покажу вам, как изменить разделитель-запятую по умолчанию в Excel при создании файлов .csv (значения, разделенные запятыми). Иногда текстовые поля в файле содержат комментарии, которые необходимо сохранить в плоском файле. Это означает, что вы должны изменить разделитель. Посмотрите демонстрационное видео и продолжайте читать, чтобы получить справку.

Должен отметить, что на момент написания этого поста я использую Windows 10 Home.

Шаг 1

Найдите «Установить региональный формат»

Шаг 2

Выберите Дополнительные дату, время и региональные настройки

Шаг 3

Шаг 4

Выберите дополнительные настройки

Затем в поле "Разделитель списка" замените запятую вертикальной чертой.

Когда вы сохраняете файл .csv в Excel, он теперь будет разделен вертикальной чертой, а не запятой.

Ваш плоский файл будет переходным от этого:

Совершенно эффективно использовать свои данные
Энтони Б. Смоук

Поставьте лайк и подпишитесь на канал Энтони Б. Смоака на YouTube!
Обязательно приобретите товары, если вам нравился этот блог и канал на YouTube на протяжении многих лет.

Все взгляды и мнения являются исключительно моими собственными и не обязательно отражают точку зрения моего работодателя.

В этом сообщении блога мы покажем вам, как изменить разделитель в CSV-файле с помощью Python. Файл CSV (значения, разделенные запятыми) – это текстовый файл, в котором для разделения значений используются запятые. Его можно открыть в Microsoft Excel, Google Таблицах, текстовом редакторе и других программах.

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

Прежде чем мы начнем

Если на вашем компьютере не установлен Python, выполните шаги 1 и 2 статьи блога Преобразование PDF в Excel, CSV или XML с помощью Python. Теперь у вас установлены Python, Anaconda и библиотека PDFTables Python.

Шаг 1

Создайте новый файл Python в папке, где сохранен ваш CSV-файл. Убедитесь, что файл сохранен в формате .py, и используйте имя файла по вашему выбору. Добавьте следующий код в новый файл:

Вам потребуется внести некоторые или все следующие изменения в сценарий:

Замените truck_invoice.csv именем входного файла.
Замените output.txt на имя, которое вы хотите присвоить выходному файлу.
Замените точку с запятой в delimiter=';' с новым разделителем по вашему выбору

Шаг 2

Откройте экземпляр Anaconda Prompt. Вы можете найти это, выполнив поиск в окне поиска компьютеров. Нажмите Enter, чтобы открыть экземпляр.

Найдите папку, в которой сохранен скрипт Python, используя cd .. или cd *имя_папки* для перемещения по каталогам.

Шаг 3

Введите python change_delimiter.py (заменив change_delimiter.py именем вашего файла Python), затем нажмите Enter.

Теперь будет прочитан файл, разделенный запятыми, после чего будет выведен новый файл в формате .txt с новым разделителем.Вы увидите сообщение о том, что разделитель успешно изменен после завершения работы скрипта.

Шаг 4

Теперь вы можете открыть новый файл в текстовом редакторе или импортировать его в приложение.

Сначала конвертировать PDF в CSV

Вам потребуется внести следующие изменения в скрипт:

Замените my-api-key своим ключом API, который вы найдете здесь.

У вас есть еще вопросы?

Ознакомьтесь с другими сообщениями в нашем блоге здесь или на странице часто задаваемых вопросов. Кроме того, не стесняйтесь обращаться к нам.

Читайте также: