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

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

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





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



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

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





Это текстовый файл с разделителями

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

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



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

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



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

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

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



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

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

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

Сбивает с толку? Не так много. Посмотрим, как это сделать на примере:

В текстовом файле с названиями городов и штатов могут быть такие значения, как «Олбани, Нью-Йорк».

Microsoft Excel может считывать запятую (,) между двумя словами в качестве разделителя. Чтобы рассматривать названия города и страны как одно значение и импортировать их в одна ячейка Excel мы должны использовать двойные кавычки или апостроф в качестве квалификатора текста. Если в качестве квалификатора текста не указан символ, «Олбани, Нью-Йорк» импортируется в две соседние ячейки как Олбани и Нью-Йорк.

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

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

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

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

Но вам не нужно искать онлайн-конвертер, потому что в 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-файлов выберите Из текста (устаревшая версия) . Нажмите Ok , чтобы закрыть Параметры.

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

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

Шаг 1

Так выглядит Мастер импорта текста на первом этапе.

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

Выбирать Фиксированная ширина - Когда все элементы одинаковой длины и аккуратно структурированы в столбцы, разделенные пробелами.

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

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

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

В Источник файла в большинстве случаев можно оставить значение по умолчанию.

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

Нажмите Следующий .

Шаг 2

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

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

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

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

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

Нажмите на Следующий .

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

я не могу вспомнить название книги

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

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

Шаг 3

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

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

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

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

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

Нажмите Заканчивать . В конце Импортировать данные всплывает диалоговое окно.

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

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

Метод 3: использование получения и преобразования данных

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

Это перебор для простых файлов 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 как средство экономии времени с помощью вышеперечисленных методов и приведенных ниже советов или изучите как извлечь число или текст из Excel .

Делиться Делиться Твитнуть Эл. адрес 6 звуковых альтернатив: лучшие бесплатные или дешевые аудиокниги

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

Читать далее Похожие темы
  • Продуктивность
  • Таблица
  • Майкрософт Эксель
  • Microsoft Office 2016
  • Microsoft Office 2019
Об авторе Сайкат Басу(Опубликовано 1542 статей)

Сайкат Басу - заместитель редактора по Интернету, Windows и производительности. После того, как он избавился от грязи MBA и десятилетней маркетинговой карьеры, он теперь увлечен тем, чтобы помогать другим улучшать свои навыки рассказывания историй. Он следит за пропавшей оксфордской запятой и ненавидит плохие скриншоты. Но идеи фотографии, фотошопа и производительности успокаивают его душу.

Ещё от Saikat Basu

Подписывайтесь на нашу новостную рассылку

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

Нажмите здесь, чтобы подписаться