Как преобразовать разделенные текстовые файлы в электронные таблицы Excel
Продуктивность

Как преобразовать разделенные текстовые файлы в электронные таблицы Excel

Преобразовать текстовый файл с разделителями в таблицу Excel очень просто. Вот как это сделать, используя три различных метода

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

Вот несколько повседневных примеров:

  • Анализ в Excel информации о продажах или продукции, хранящейся в текстовом файле.
  • Обмен данными между двумя различными программами (возможно, из базы данных в электронную таблицу).
  • Имена, адреса и идентификаторы электронной почты, хранящиеся в программе электронной почты (например.экспорт из Microsoft Outlook в Excel).

.

Microsoft Excel предоставляет вам все инструменты для подключения к внешним источникам данных. Давайте поговорим о разделенных текстовых файлах

Это разделенный текстовый файл

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

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

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

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

Давайте рассмотрим разделенный текстовый файл более подробно

3 различных вида разделенных текстовых файлов

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

Например: труба (|) или простой пробел. Эти три символа являются наиболее распространенными видами разделителей между каждой текстовой записью

  1. Значения, разделенные запятой.
  2. Значения, разделенные табуляцией.
  3. Значения, разделенные двоеточием.

Любое значение, которое следует за разделителем и предшествует следующему появлению разделителя, импортируется как одно значение Разделитель текста отделяет каждое значение от следующего. Помните, что значение между назначенными разделителями может иметь другой символ разделителя, но для этого необходимо использовать кавычки (‘) или апостроф (‘)

Сбивает с толку? Не очень. Давайте посмотрим на примере:

В текстовом файле с названиями городов и штатов могут быть значения типа ‘Albany, NY’

Microsoft Excel может считать запятую (,) между двумя словами разделителем. Чтобы рассматривать названия города и страны как одно значение и импортировать их в одну ячейку Excel, мы должны использовать двойные кавычки или апостроф в качестве текстового классификатора. Если в качестве текстового классификатора не указан символ, ‘Albany, NY’ импортируется в две соседние ячейки как Albany и NY

Чтобы сохранить любое значение в столбце точно таким, какое оно есть, можно заключить его в кавычки или апостроф

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

Преобразование из разделенного текстового файла в электронную таблицу

Существует множество онлайн-конвертеров, которые могут взять необработанный текстовый файл CSV и выплюнуть его в электронную таблицу XLS.Zamzar и Convertio – два отличных инструмента

Но вам не нужно искать онлайн-конвертер, потому что в Microsoft Excel есть встроенная функция, которая делает эту работу намного лучше

Давайте возьмем пример файла CSV и рассмотрим шаги по преобразованию разделенных текстовых файлов в электронные таблицы. Приведенный выше снимок экрана с нагромождением значений, разделенных запятыми, в файле Блокнота является хорошим примером

Microsoft Excel поможет превратить это беспорядочное нагромождение в аккуратные строки и столбцы. Затем вы можете приступить к работе над ним и превратить его в красиво оформленный отчет или подготовить его к печати

Существует три способа внести данные в таблицу Excel из файла CSV. Начнем с самого простого

Способ 1: Автоматический импорт

1. Перейдите на вкладку Файл , затем нажмите Открыть

2. Выберите файл CSV, который необходимо открыть.Microsoft Excel автоматически открывает текстовый файл и отображает данные в новой рабочей книге

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

Итак, рассмотрим второй способ, в котором используется мастер

Способ 2: Восстановление мастера импорта текста

Мастер импорта текста позволяет вам управлять структурой данных, которые вы хотите импортировать. Он запускается автоматически при импорте текстовых файлов (т.е.файл с расширением TXT)

Откройте Microsoft Excel и перейдите к текстовому файлу (или измените расширение файла CSV на TXT)

Microsoft скрыла старый мастер импорта текста в Excel 365 и 2016 (версия 1704 и далее). Но вы можете вернуть мастер импорта текста из ‘Параметры’ Excel

1. Перейдите в меню Файл > Параметры > Данные

2. Прокрутите вниз до раздела Show legacy data import wizards

3. Для импорта текстовых или CSV-файлов выберите Из текста (Legacy). Нажмите OK , чтобы закрыть Параметры

4. Теперь вы можете использовать мастер из ленты. Перейдите в раздел Данные > Получить данные > Наследие мастера > Из текста (наследие). Найдите и откройте CSV-файл, который вы хотите импортировать

Используйте этот трехэтапный процесс для управления форматом данных

Шаг 1

Вот как выглядит мастер импорта текста на первом шаге

Выберите Delimited — Когда элементы в текстовом файле разделяются табуляцией, двоеточием, точкой с запятой, пробелами или другими символами

Выбрать Fixed Width — Когда все элементы одинаковой длины и аккуратно структурированы в столбцах, разделенных пробелами

Иногда исходные данные могут иметь строку заголовка. Например:

'first_name','last_name','company_name','address','city','county'

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

Для большинства случаев параметр File Origin можно оставить по умолчанию

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

Нажмите Далее

Шаг 2

Выберите Разделители для вашего файла (в нашем случае это запятая). Для какого-либо другого символа отметьте Другой и введите символ в маленькое поле. В окне Предварительный просмотр данных можно увидеть столбчатые данные

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

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

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

Используйте окно Предварительный просмотр данных для проверки внешнего вида

Нажмите на Далее

Примечание: При импорте данных фиксированной ширины экран мастера изменяется

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

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

Шаг 3

Окна предварительного просмотра становятся более важными на этом экране, потому что вы можете точно настроить формат данных, которые попадают в каждое поле, с помощью параметра Формат данных столбцов. По умолчанию Microsoft Excel импортирует данные в формате ‘Общий’. Выберите столбец в окне предварительного просмотра и установите соответствующий формат

Например, вы можете выбрать

  • Текст для текстовых полей.
  • Дата и формат даты для любого столбца, содержащего даты.
  • Общие для преобразования валют в формат Excel Currency.

.

Используйте кнопку Дополнительно для указания типа десятичного и тысячного разделителей для числовых данных

Например, если вы хотите отобразить 100 000 как 1 00 000.Excel отображает числа в соответствии с форматом, установленным в региональных настройках компьютера

Нажмите Готово. Появится последнее диалоговое окно Импорт данных

Теперь не беспокойтесь об этом слишком сильно. Он дает вам несколько вариантов вставки данных в электронную таблицу или создания соединения с внешней базой данных. Вставка значений с разделителями текста в виде таблицы в данный рабочий лист является настройкой по умолчанию

Старый’ унаследованный метод все еще является лучшим способом, если ваш CSV-файл прост. Если же нет, то теперь есть новый метод, который может подойти для любых задач импорта текста

Метод 3: Используйте Get & Transform Data

Вкладка Данные содержит все инструменты, которые вам понадобятся для сбора внешних данных и приведения их в нужное вам состояние. Например, вы можете создать отчет PivotTable и обновлять его при каждом изменении внешних данных

Это излишество для простых CSV-файлов, но давайте посмотрим, как внести столбцы разделенных данных в Excel

1. Откройте пустую рабочую книгу

2. Перейдите на вкладку Данные на ленте. Затем нажмите на маленькую стрелку под кнопкой Get Data (в группе Get & Transform Data ). Выберите Из файла > Из текста/CSV

3. В диалоговом окне Импорт текстового файла перейдите к местоположению на рабочем столе и выберите текстовый файл CSV, который вы хотите импортировать. Мастер импорта текста отобразится с опциями, которые вы теперь можете выбрать

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

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

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

5. При импорте данных в Excel создается соединение с данными. Вы можете выполнять запросы и подключать рабочий лист к внешнему источнику. Любые изменения в исходных данных источника будут автоматически обновляться в листе Excel

6. Вы можете отменить это соединение, перейдя в раздел Data > Queries and Connections , чтобы открыть панель сбоку

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

Преобразование данных выходит за рамки данного руководства. Поэтому я направлю вас на страницу поддержки Microsoft ‘Начало работы с Get & Transform в Excel’ для получения дополнительной помощи

Многочисленные возможности использования разделенных файлов в Excel

Нужно разделить список имен и фамилий и подготовить его к печати? Преобразуйте разделенный текстовый файл в Excel. Вы можете импортировать или экспортировать до 1 048 576 строк и 16 384 столбцов. А разделенные файлы поддерживаются практически везде. Используйте Excel как средство экономии времени с помощью описанных выше приемов и советов ниже или узнайте, как извлечь число или текст из Excel

Об авторе

Алексей Белоусов

Привет, меня зовут Филипп. Я фрилансер энтузиаст . В свободное время занимаюсь переводом статей и пишу о потребительских технологиях для широкого круга изданий , не переставая питать большую страсть ко всему мобильному =)

Комментировать

Оставить комментарий