Придет время, когда вам придется иметь дело со всеми видами информации, хранящейся в других типах файлов, и перенести ее в Microsoft Excel . Вы не можете убежать от вездесущего текстового файла. Могу поспорить, вы найдете некоторые из них каждый день.
Вот несколько повседневных примеров:
- Анализ Excel продаж или информации о продукте, хранящейся в текстовом файле.
- Обмен данными между двумя различными программами (возможно, из базы данных в электронную таблицу).
- Имена, адреса и идентификатор электронной почты, хранящиеся в почтовой программе (например, экспорт из Microsoft Outlook в Excel. ).
Microsoft Excel предоставляет вам все инструменты для подключения к внешним источникам данных. Давайте поговорим о текстовых файлах с разделителями.
Это текстовый файл с разделителями
Как видите, имена и фамилии, компании, в которых они работают, и другие данные разделяются запятыми. Этот текстовый файл с разделителями-запятыми легко создать в любом текстовом редакторе.
Прямо сейчас это не полезно. Принесите это в электронную таблицу, и вы можете сделать более профессиональный документ.
Например, вы можете легко найти дубликаты данных и удалить их. Затем вы можете использовать электронную таблицу для создания меток и слияния почты. если адреса также являются частью данных.
Основная идея здесь состоит в том, чтобы импортировать информацию из текстового файла и разделить ваши различные фрагменты информации на отдельные столбцы и дать каждому столбцу соответствующий заголовок.
Давайте посмотрим на текстовый файл с разделителями более подробно …
3 различных вида текстовых файлов с разделителями
Существует три общих типа файлов с разделителями в зависимости от способа разделения (разделения) каждого значения. Любой символ может использоваться для разделения отдельных записей в файле.
Например: труба (|) или простое пространство. Вы найдете эти три наиболее распространенными видами разделителей между текстовыми записями.
- Значения, разделенные запятыми.
- Значки разделенные табуляцией.
- Значения, разделенные двоеточием
Разделитель текста сохраняет каждое значение отдельно от следующего. Любое значение, которое следует за разделителем и предшествует следующему вхождению разделителя, импортируется как одно значение. Помните, что значение между назначенным разделителем может иметь другой символ разделителя, но для этого требуется кавычка («) или апостроф (‘).
Смешение? Не так много. Давайте посмотрим, как на примере:
В текстовом файле с названиями городов и штатов могут быть некоторые значения, например «Олбани, штат Нью-Йорк».
Microsoft Excel может читать запятую (,) между двумя словами как разделитель. Чтобы обрабатывать названия городов и стран как одно значение и импортировать их в одну ячейку Excel, мы должны использовать двойные кавычки или апостроф в качестве квалификатора текста. Если в качестве квалификатора текста не указан символ, «Олбани, штат Нью-Йорк» импортируется в две соседние ячейки как «Олбани» и «Нью-Йорк».
Вкратце, чтобы сохранить любое значение в столбце в точности таким, как оно есть, вы можете заключить это значение в кавычки или апостроф.
Как мы увидим ниже, 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. Прокрутите вниз до раздела Показать мастера импорта устаревших данных .
3. Для импорта текстовых или CSV-файлов выберите From Text (Legacy) . Нажмите кнопку ОК, чтобы закрыть параметры.
4. Теперь вы можете использовать мастера с ленты. Перейдите в Данные> Получить данные> Старые мастера> Из текста (Legacy) . Найдите и откройте файл CSV, который вы хотите импортировать.
Используйте этот трехэтапный процесс для управления форматом данных.
Шаг 1
Вот как выглядит мастер импорта текста на первом этапе.
Выберите «С разделителями» — когда элементы в текстовом файле разделены табуляцией, двоеточием, точкой с запятой, пробелами или другими символами.
Выберите « Фиксированная ширина» — когда все элементы имеют одинаковую длину и аккуратно структурированы в разделенных пробелами столбцах.
Иногда необработанные данные могут иметь строку заголовка. Например: [ "first_name","last_name","company_name","address","city","county" ].
Используйте Начать импорт в строке, чтобы выбрать строку, с которой начнется импорт.
Исходный файл может быть оставлен по умолчанию для большинства случаев.
Предварительный просмотр отображает значения в том виде, в котором они отображаются, когда они разделены на столбцы на рабочем листе.
Нажмите Далее .
Шаг 2
Выберите разделители для вашего файла (в нашем случае запятую). Для какого-либо другого персонажа установите флажок « Другой» и введите его в маленькое поле. Окно предварительного просмотра данных дает представление о столбчатых данных.
Установите флажок Обрабатывать последовательные разделители как один, если ваши данные содержат разделитель из нескольких символов между полями данных или если ваши данные содержат несколько пользовательских разделителей.
Например, это помогает вам обрабатывать файлы, которые могут иметь дополнительный пробел до или после другого разделителя. Вы можете помочь определить пространство как еще один разделитель и поставить галочку в этом поле.
Используйте раскрывающийся список « Текст», чтобы выбрать символ, содержащий значения в текстовом файле. Ранее мы говорили о том, как классификатор текста может помочь вам импортировать некоторые значения в одну ячейку вместо отдельных.
Используйте окно предварительного просмотра данных, чтобы проверить внешний вид.
Нажмите на Далее .
Примечание. Экран мастера изменяется при импорте данных фиксированной ширины.
Окно предварительного просмотра данных может помочь вам установить ширину столбцов. Используйте верхнюю панель в окне, чтобы установить разрыв столбца, представленный вертикальной линией. Перетащите разрыв столбца, чтобы увеличить или уменьшить ширину. Дважды щелкните разрыв столбца, чтобы удалить его.
В файле с фиксированной шириной разделители не используются для разделения значений в файле. Данные организованы в строки и столбцы, по одной записи на строку. Каждый столбец имеет фиксированную ширину, указанную в символах, которая определяет максимальный объем данных, который он может содержать.
Шаг 3
Окна предварительного просмотра становятся более важными на этом экране, потому что вы можете точно настроить формат данных, которые входят в каждое поле, с форматом данных столбца . По умолчанию Microsoft Excel импортирует данные в общем формате. Выберите столбец в окне предварительного просмотра и установите соответствующий формат.
Например, вы можете выбрать …
- Текст для текстовых полей.
- Дата и формат даты для любого столбца, который содержит даты.
- Общие для преобразования валют в формат валюты Excel.
Используйте кнопку « Дополнительно» , чтобы указать тип десятичного разделителя и разделитель тысяч разрядов для числовых данных.
Например, если вы хотите отобразить 100 000 как 1 000 000. Excel отображает числа в соответствии с форматом, установленным в региональных настройках вашего компьютера.
Нажмите Готово . Появится последнее диалоговое окно « Импорт данных ».
Не беспокойся об этом сейчас. Это дает вам несколько вариантов для вставки данных в электронную таблицу или создания соединения с внешней базой данных. Вставка значений с разделителями текста в виде таблицы на текущем рабочем листе является настройкой по умолчанию.
«Старый» унаследованный метод все еще остается лучшим способом, если ваш CSV-файл прост. Если нет, то теперь есть новый метод, который может удовлетворить любые потребности импорта текста.
Метод 3: Используйте Get & Transform Data
Вкладка «Данные» включает в себя все инструменты, которые вам понадобятся для сбора внешних данных и настройки их работы так, как вы этого хотите. Например, вы можете создать отчет сводной таблицы и обновлять его при изменении внешних данных.
Для простых файлов CSV это излишне, но давайте продолжим и посмотрим, как вводить столбцы с разделителями в Excel.
1. Откройте пустую книгу.
2. Перейдите на вкладку « Данные » на ленте. Затем нажмите маленькую стрелку раскрывающегося списка под кнопкой « Получить данные» (в группе « Получить и преобразовать данные »). Выберите « Из файла»> «Из текста / CSV» .
3. В диалоговом окне « Импорт текстового файла » перейдите в папку на рабочем столе и выберите текстовый файл CSV, который вы хотите импортировать. Мастер импорта текста аккуратно отображается с параметрами, которые вы можете выбрать.
Как вы можете видеть, вы можете изменить кодировку и выбор символа с разделителями или ввести пользовательский разделитель.
Excel вычисляет разделитель, анализируя первые несколько сотен строк набора данных по умолчанию. Вы можете изменить это и позволить Excel работать со всем набором данных. Не рекомендуется, если у вас есть миллионы записей для импорта.
4. Нажмите кнопку « Загрузить» , чтобы импортировать данные в новый красивый лист.
5. При импорте данных в Excel создается соединение для передачи данных. Вы можете выполнять запросы и подключать лист к внешнему источнику. Любые изменения в исходных данных будут автоматически обновляться в листе Excel.
6. Вы можете отменить это соединение, перейдя в Данные> Запросы и соединения, чтобы открыть панель сбоку.
Щелкните правой кнопкой мыши и выберите « Удалить» . Или наведите курсор на имя файла источника данных и нажмите « Удалить» в появившемся окне. Если вы уверены, нажмите « Удалить» еще раз.
Преобразование данных выходит за рамки этого руководства. Поэтому я направлю вас на страницу поддержки Microsoft « Начало работы с Get & Transform в Excel» для получения дополнительной помощи.
Многократное использование файлов с разделителями в Excel
Нужно разделить список имен и фамилий и подготовить их к печати? Преобразовать текстовый файл с разделителями в Excel. Вы можете импортировать или экспортировать до 1 048 576 строк и 16 384 столбцов. И файлы с разделителями поддерживаются практически везде. Использование Excel в качестве экономии времени экономии экономии с вышеупомянутыми методами и советами ниже.