Преобразование CSV в Excel: решения распространенных проблем
CSV не открывается правильно в Excel? В учебнике исследуются типичные проблемы и предлагаются наиболее эффективные решения.
Формат CSV обычно используется для импорта/экспорта данных между различными программами для работы с электронными таблицами. Название CSV (значения, разделенные запятыми) подразумевает использование запятой для разделения полей данных. Но это в теории. На практике многие так называемые файлы CSV разделяют данные с помощью других символов, таких как точка с запятой или табуляция. В некоторых реализациях поля данных заключаются в одинарные или двойные кавычки, а в других для правильной интерпретации Unicode требуется знак порядка байтов (BOM) Unicode, например UTF-8. Отсутствие стандарта порождает различные проблемы с преобразованием CSV в Excel.
Файл 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 CSV
Симптомы. Некоторые значения в вашем CSV-файле содержат ведущие нули. Когда файл открывается в Excel, предыдущие нули теряются.
Причина. По умолчанию Microsoft Excel преобразует CSV-файлы в Общий формат, который удаляет начальные нули.
Решение. Вместо открытия импортируйте CSV в Excel и выберите Текст формат для проблемных столбцов.
Использование мастера импорта текста
Начать Мастер импорта текста автоматически измените расширение файла с .csv на .txt, а затем откройте текстовый файл из Excel. Или включите функцию «Из текста (прежняя версия)» и начните импорт CSV в Excel.
На шаге 3 мастера выберите столбец, содержащий значения с ведущими нулями, и измените его формат на Текст. Это позволит импортировать значения в виде текстовых строк, сохраняя все ведущие нули на месте.
Использование Power Query
Если вы предпочитаете импортировать CSV-файл в Excel, подключившись к нему, есть два способа сохранить начальные нули.
Способ 1: импортировать все данные в текстовом формате
В диалоговом окне предварительного просмотра в разделе Определение типа данных, выберите Не определять типы данных. Содержимое вашего CSV-файла будет загружено в Excel в виде текста, а все ведущие нули будут сохранены.
Примечание. Этот метод отлично работает, если ваш файл содержит только текстовые данные. Если существуют разные типы значений, используйте метод 2, чтобы определить соответствующий формат для каждого столбца отдельно.
Способ 2: установить формат для каждого столбца
В ситуации, когда ваш CSV-файл содержит различные типы данных, такие как текст, числа, валюты, даты и время, вы можете явно указать, какой формат следует использовать для каждого конкретного столбца.
- Под предварительным просмотром данных щелкните Преобразовать данные.
- В редакторе Power Query выберите столбец, в котором вы хотите сохранить предшествующие нули, и щелкните Тип данных > Текст.
- При необходимости определите типы данных для других столбцов.
- Когда закончите редактирование, на Дом вкладка, в Закрывать выберите один из следующих вариантов:
- Закрыть и загрузить – это загрузит результаты на новый лист в текущей книге.
- Закрыть и загрузить To… — это позволит вам решить, куда загружать результаты.
Кончик. Эти методы также могут предотвратить другие манипуляции с вашими данными, которые Excel пытается выполнить автоматически. Например, если импортированные данные начинаются с «=», Excel попытается их вычислить. Применяя Текст формат, вы указываете, что значения являются строками, а не формулами.
Как исправить проблемы с форматом даты CSV в Excel
Симптомы. После преобразования CSV в Excel даты форматируются неправильно, дни и месяцы меняются местами, некоторые даты заменяются текстом, а некоторые текстовые значения автоматически форматируются как даты.
Причина. В вашем CSV-файле даты записываются в формате, отличном от формата даты по умолчанию, установленного в вашей операционной системе, из-за чего Excel не может правильно интерпретировать даты.
Решение. В зависимости от того, с какой именно проблемой вы столкнулись, попробуйте одно из следующих решений.
Дни и месяцы перепутаны
Если форматы даты в региональных настройках Windows и файле csv различаются, Excel не может определить, что мм/дд/гг даты, которые он ищет, хранятся в дд/мм/гг формат в этом конкретном файле. В результате единицы дня и месяца меняются местами: 3 января становится Мар-1, 10 января становится 1 октября, и так далее. Кроме того, даты после 12 января преобразуются в текстовые строки, потому что не существует 13-го, 14-го и т. д. месяцев.
Чтобы даты импортировались правильно, запустите Мастер импорта текста и выберите соответствующий формат даты на шаге 3:
Даты имеют неправильный формат
Когда CSV-файл открывается в Excel, даты обычно отображаются в формате по умолчанию. Например, в исходном файле у вас может быть 7 мая 21 или же 07.05.21а в Excel это выглядит как 07.05.2021.
Чтобы отобразить даты в нужном формате, используйте Формат ячеек особенность:
- Выберите столбец дат.
- Нажмите Ctrl + 1, чтобы открыть Формат ячеек диалоговое окно.
- На Число вкладку, выберите Дата под Категория.
- Под Типвыберите нужное форматирование.
- Нажмите «ОК».
Если ни один из предустановленных форматов вам не подходит, вы можете создать свой собственный, как описано в разделе Как создать собственный формат даты в Excel.
Запретить Excel преобразовывать числа в экспоненциальное представление
Симптомы. После преобразования CSV в Excel длинные числа форматируются в экспоненциальном формате, например, 1234578900 отображается как 1.23E+09.
Причина. В Microsoft Excel точность чисел ограничена 15 цифрами. Если числа в вашем CSV-файле превышают это ограничение, Excel автоматически преобразует их в экспоненциальное представление, чтобы соответствовать этому ограничению. Если число содержит более 15 значащих цифр, все «лишние» цифры в конце заменяются нулями.
Решение. Импортируйте длинные числа в виде текста или измените числовой формат непосредственно в Excel.
Импорт длинных чисел в виде текста
Чтобы точно перенести большие числа из CSV в Excel, запустите Мастер импорта текста и установите формат целевого столбца (столбцов) в Текст.
Это единственное реальное решение для точного импорта числовых строк без потери данных, то есть без замены 16-й и последующих цифр на 0 или удаления ведущих нулей. Он отлично работает для таких записей, как идентификаторы продуктов, номера счетов, штрих-коды и тому подобное.
Однако, если ваши значения являются числами, а не строками, это не лучший метод, так как вы не сможете выполнять какие-либо математические операции с результирующими текстовыми значениями.
Изменить числовой формат в Excel
Если ваши данные уже находятся в Excel, вы можете изменить формат с Общий либо Текст или же Число как показано ниже:
Примечание. Этот метод не восстанавливает удаленные предшествующие нули или цифры после 15-й позиции, которые были заменены нулями.
Дополнительные сведения см. в разделе Как форматировать ячейки в Excel.
Сделать столбец шире
В простейшем случае, когда число содержит менее 15 цифр, достаточно сделать столбец немного шире, чтобы числа отображались нормально.
Дополнительные сведения см. в разделе Как изменить размер и автоматически подогнать столбцы в Excel.
Вот как можно исправить наиболее распространенные проблемы, которые могут возникнуть при преобразовании CSV в Excel. Спасибо за чтение и увидимся на следующей неделе!
