Как открыть txt в excel. Импорт данных в Excel из текстового файла
Набирать информацию с клавиатуры дело хлопотное и трудоемкое. И ладно бы в Word, а то в Excel, где нужно перемещаться между ячейками. Поэтому, если информация имеет место быть в текстовом виде, это уже хорошо. Рассмотрим, как быстро выполнить импорт данных в Excel из текстового файла или, говоря по другому, корректно вставить текст в таблицу Excel.
Очень-очень сложного тут ничего нет. Как я уже неоднократно отмечал, информация в таблице Excel должна быть представлена строго структурированной, следовательно, в текстовом файле эта структурированность также должна строго соблюдаться.
Возьмём такой жизненный и несложный пример — перенесем из текстового файла (с расширением.txt) в Excel список сотрудников, в котором перечислены фамилия, имя, отчество, год рождения и должность.
Допустим, вот наши герои:
Иванов Иван Иванович 1978 инспектор
Сидоров Петр Сидорович 1970 старший инспектор
Петров Василий Самуилович 1965 самый старший инспектор
Больше трех лень набивать 🙂 , но и их хватит.
Теперь определимся, какая нам нужна в Excel таблица? Пусть она имеет такие столбцы:
Фамилия, Имя, Отчество, Год рождения, Должность.
Все у нас имеется, но тут есть один нюанс в представлении должности. Для каждого сотрудника должность имеет разный размер – 1, 2 и 3 слова. Как же Excel указать, где начинается и заканчивается информация, заносимая по столбцам?
Для этого удобно использовать символ-разделитель . Таким символом может быть любой символ, который не встречается в Вашем тексте как знак препинания или спецсимвол. То есть, точка и запятая точно старайтесь не использовать, а вот звездочку или наклонную можно взять. В нашем случае, если бы должность у всех состояла из одного слова или так – ст.инспектор, сам.ст.инспектор (без пробелов), то подошел бы и пробел. Подходите творчески, чтобы меньше делать работы. 🙂
Структурируем наш список:
Фамилия/Имя/Отчество/Год рождения/Должность
Иванов/Иван/Иванович/1978/инспектор
Сидоров/Петр/Сидорович/1970/старший инспектор
Петров/Василий/Самуилович/1965/самый старший инспектор
и сохраняем в текстовом документе c расширением .txt . Теперь такой список замечательно перенесется в Excel.
Для этого заходим в Excel на вкладку Данные . В области Получение внешних данных нажимаем кнопку Из текста .
В открывшемся окне указываем на наш текстовый файл, из которого будем импортировать данные. После этого откроется Мастер импорта на первом шаге. Тут нужно указать структуру наших данных – “с разделителями ” (которым у нас выступает наклонная «/»). После этого нажимаем кнопку Далее .
На следующем шаге мастера нужно указать символ-разделитель. Тут приведены некоторые варианты, но мы выбираем “другой ” и в окошке вводим наш символ – наклонную. Сразу же в нижней области (с нашим списком) появляются вертикальные линии (столбцы), по которым можно проверить правильность разделения данных. Нажимаем кнопку Далее .
На третьем шаге можно указать формат данных для каждого или выбранного столбца, если в этом есть необходимость. Как правило, такое актуально для столбцов с датой. Мы же ничего не меняем и жмём кнопку Готово .
После этого нужно указать ячейку, с которой будет начинаться таблица – её левый верхний угол.
Существует два способа импорта данных из текстового файла в Excel: вы можете открыть его в Excel или импортировать как диапазон внешних данных. Чтобы экспортировать данные из Excel в текстовый файл, используйте команду Сохранить как и измените тип файла в раскрывающемся меню.
Чаще всего используются два формата текстовых файлов:
текстовые файлы с разделителями (TXT), в которых для разделения полей текста обычно используется знак табуляции (знак ASCII с кодом 009);
текстовые файлы с разделителями-запятыми (CSV), в которых в качестве разделителя полей текста обычно используется запятая (,).
Вы можете изменить знак разделения, используемый в TXT- и CSV-файлах. Это может потребоваться для обеспечения успешного выполнения импорта и экспорта.
Примечание: Можно импортировать и экспортировать до 1 048 576 строк и 16 384 столбцов.
Импорт текстового файла путем его открытия в Excel
Текстовый файл, созданный в другой программе, можно открыть как книгу Excel с помощью команды Открыть . При открытии текстового файла в Excel его формат не изменяется - это можно заметить по заголовку окна Excel, где имя указывается с расширением текстового файла (например, TXT или CSV).
Перейдите в файл > Открыть и перейдите к расположению, содержащему текстовый файл.
Выберите текстовые файлы в раскрывающемся списке Тип файла в диалоговом окне Открытие .
Найдите нужный файл и дважды щелкните его.
Импорт текстового файла путем подключения к нему (Power Query)
На вкладке " данные " в группе " Получение & данных преобразования " выберите из текста или CSV .
В диалоговом окне Импорт данных найдите и дважды щелкните текстовый файл, который вы хотите импортировать, и нажмите кнопку Импорт .
В диалоговом окне Предварительный просмотр есть несколько вариантов:
Если Excel не преобразует определенный столбец данных в нужный формат, вы можете преобразовать их после импорта. Дополнительные сведения см. в статьях и .
Сохранить как .
Импорт текстового файла путем подключения к нему
Вы можете импортировать данные из текстового файла на существующий лист.
Если Excel не преобразует столбец в необходимый формат, это можно сделать после импорта. Дополнительные сведения см. в статьях Преобразование чисел из текстового формата в числовой и Преобразование дат из текстового формата в формат даты .
Экспорт данных в текстовый файл путем его сохранения
Лист Excel можно преобразовать в текстовый файл, используя команду Сохранить как .
-
Откроется диалоговое окно Сохранение документа .
В поле Тип файла выберите формат текстового файла для листа.
-
Откроется диалоговое окно с напоминанием о том, что в текстовом файле будет сохранен только текущий лист. Если вы хотите сохранить именно текущий лист, нажмите кнопку ОК . Другие листы можно сохранить в отдельных текстовых файлах, повторив эту процедуру для каждого из них.
Откроется второе диалоговое окно с напоминанием о том, что сохраняемый лист может содержать возможности, которые не поддерживаются текстовыми форматами файлов. Если в новом текстовом файле нужно сохранить только данные листа, нажмите кнопку Да . Если вы не уверены и хотите получить дополнительные сведения о том, какие возможности Excel не поддерживаются форматами текстовых файлов, нажмите кнопку Справка .
Дополнительные сведения о сохранении файлов в других форматах см. в статье Сохранение книги в другом формате файла .
Изменение разделителя, используемого в текстовом файле
Если для импорта текстового файла используется мастер импорта текста, то на втором шаге разделитель можно изменить с символа табуляции на другой знак. На этом шаге также можно изменить способ обработки последовательных разделителей (например, идущих подряд кавычек).
Дополнительные сведения о разделителях и дополнительных параметрах можно найти в мастере импорта текста .
Изменение разделителя во всех текстовых CSV-файлах
Нажмите кнопку Пуск и выберите пункт Панель управления .
Откройте диалоговое окно изменения региональных и языковых параметров.
Введите новый разделитель в поле Разделитель элементов списка .
Дважды нажмите кнопку ОК .
Примечание: После изменения разделителя элементов списка все программы на компьютере будут использовать новый символ. Вы можете вернуть стандартный символ, выполнив эти же действия.
24 ДекДобрый день, читатели блога!
Сегодня мы поговорим о выгрузке данных таблицы Excel в текстовый файл.
Можно сразу возразить — такая функция есть в программе! Да, она есть, но выгрузка, иногда происходит не в той кодировке, я не знаю в какой ячейке содержались данные, мне приходится переименовывать и указывать место для нового файла.
Постараемся избавиться от этих сопутствующих шагов и договоримся вот о чём:
- сохранять будем в той же папке, что и таблица;
- имя текстового файла будет таким же.
Откроем таблицу с данными и, как обычно, перейдём на вкладку «Разработчик».
Не отклоняемся от вставки модуля:
- Вкладка «Разработчик», блок кнопок «Код», кнопка «Visual Basic»;
- Далее «Insert» — > «Module».
Как только модуль появится вставим туда следующий макрос.
Sub SaveAsText()
Dim cell As Range
‘ Открываем файл для сохранения (имя файла = имя рабочей книги, но отличается расширением — TXT)
Open ThisWorkbook.Path & «\» & ThisWorkbook.Name & «.txt» For Output As #1
‘ Запись содержимого заполненных ячеек таблицы в файл
For Each cell In ActiveSheet.UsedRange
If Not IsEmpty(cell) Then
Print #1, cell.Address, cell.Formula
End If
Next
‘ Не забыть закрыть файл
Close #1
End Sub
Необходимые пояснения:
- Open ThisWorkbook.Path & «\» & ThisWorkbook.Name & «.txt» For Output As #1 — объявляем наш файл #1, сохраняем его в том же месте где и книга, имя текстового файла будем таким же как и у книги (с добавлением txt);
- For Each cell In ActiveSheet.UsedRange If Not IsEmpty(cell) Then — для каждой ячейки на активном листе (если она не пуста) будет применено следующее действие;
- Print #1, cell.Address, cell.Formula — выводим данные в текстовый файл вместе с адресом ячейки;
- Close #1 — закрываем книгу.
Вызываем макрос (Alt+F8), выполняем его (в появившемся окне жмём кнопку «Выполнить»).
Визуально ничего не происходит, но если закрыть таблицу и вернуться в папку где она сохранена, мы увидим следующую картину.
Всё согласно условиям выгрузки. При открытии текстового файла также будет видна вся информация о данных таблицы — где, что находится.
Как импортировать в Excel файл в формате txt?
Импортировать txt-файлы можно двумя способами, которые в конечном итоге приводят к одному Мастеру текстов (импорт) и одному результату.
1. Находясь в книге Excel, в верхнем левом углу нажмите кнопку Microsoft Office , затем выберите команду Открыть . В появившемся диалоговом окне Открытие документа , в поле Тип файлов , выберите Текстовые файлы . В окне просмотра у вас появятся все текстовые файлы, укажите нужный файл и нажмите кнопку Открыть .
Мастер текстов (импорт) .
2. Находясь в книге Excel, на вкладке Данные в группе Получение внешних данных нажмите кнопку Из текста . У вас появится диалоговое окно Импорт текстового файла , аналогичное окну Открытие документа п.1 . В данном диалоговом окне укажите файл, который необходимо импортировать и нажмите кнопку Импорт .
Программа автоматически запустит Мастер текстов (импорт) .
В Мастере импорта текста на первом этапе вам необходимо указать Формат данных: с разделителями или фиксированной ширины. Вариант с разделителями подходит, если вы знаете, какие знаки используются в виде разделителей или же таблица имеет двух и более уровневую структуру. Формат данных фиксированной ширины подходит в тех случаях, если вы не можете четко определить, что является разделителем в txt-таблице, или таблица имеет границы, которые нужно удалить.
Второй важный момент - Формат файла . Из выпадающего списка вы можете выбрать нужную кодировку текста, если программа не смогла автоматически верно распознать текст.
Начать импорт со строки - укажите с какой строки необходимо начать импорт файла. Так вы можете не импортировать заглавие таблицы, колонтитулы или другой текст, который находится до таблицы и который все равно будет порезан в результате импорта таблицы.
Если на первом этапе вы указали все необходимые данные нажмите кнопку Далее .
На втором этапе Мастера импорта текстов у вас может быть два варианта действий.
1. Если на первом этапе вы указали, что у вас формат данных с разделителями, то здесь вам необходимо выбрать тип разделителя, либо же указать свой.
2. Если на первом этапе вы указали, что у вас формат данных фиксированной ширины, то в окне Образец разбора данных у вас есть возможность проставить все необходимые разделители, т.е. вручную расставить колонки в таблице. Линии-разделители устанавливаются путем одноразового клика по шкале, в верхней части окна, или области просмотра. Удалить линию-разделитель можно путем двойного клика по ней.
После выбора разделителей/установления столбцов вручную нажимаем кнопку Далее .
На третьем этапе Мастер импорта текста предлагает установить формат колонок. По умолчанию стоит Общий .
Нажимаем кнопку Готово .
У меня файл в формате csv, открывается в Эксель в одну строчку, как его преобразовать в таблицу?
Проще сразу его импортировать в виде таблицы, ведь он таковым и является, только с запятыми (или точкой с запятой) в виде разделителя. Для этого, находясь в книге Excel, на вкладке Данные в группе Получение внешних данных нажмите кнопку Из текста .
У вас появится диалоговое окно Импорт текстового файла , в котором укажите файл, который необходимо импортировать и нажмите кнопку Импорт .
Сразу же откроется диалоговое окно Мастер текстов (импорт) . На первом шаге в поле формат данных укажите с разделителями.
На втором шаге в поле Символом разделителем является отметьте - запятая (точка с запятой).
На третьем шаге, если есть необходимость, укажите формат столбцов.
После нажатия кнопки Готово файл csv будет корректно импортирован в Excel и преобразован в таблицу.
У меня есть таблица в формате txt со всеми линиями границ, как мне импортировать ее в Excel, чтобы этих границ не было?
Перед тем, как осуществить импорт таблицы зайдите в txt-файл и скопируйте фрагмент линии границы.
Во время импорта таблицы в Мастер текстов (импорт) на первом шаге в поле Укажите формат данных отметьте с разделителями .
На втором шаге в поле Символом-разделителем является отметьте пункт другой и установив курсор в поле для ввода символа нажмите комбинацию клавиш Ctrl+V. Таким образом, вы выберите в качестве разделителя линию границы таблицы.
Во время импорта txt-файла, числа, в которых разделители точки, Эксель воспринимает как даты и после импорта вернуть им прежнее значение не удается. Как обойти эту проблему?
Для того, что бы дробные числа с разделителем в виде точки отображались корректно в Excel, во время импорта файла на третьем шаге Мастер текстов (импорт) в окне Образец разбора данных выделите столбик, который содержит дробные числа, и в поле Формат данных столбца выберите текстовый .
После импорта данных выделите столбец, который содержит дробные числа с точкой. С помощью комбинации клавиш Ctrl+H откройте диалоговое окно Найти и заменить . В поле Найти укажите точку, в поле Заменить на - запятую . Нажмите кнопку Заменить все - замена будет произведена только в рамках выделенного столбца.
Теперь, с помощью меню Формат ячеек группы Ячейки вкладки Главная , измените формат столбца с Текстовый на Числовой .
Можно ли в Excel подключиться к данным базы Access что бы они автоматически обновлялись?
Да, для этого находясь в книге Excel через вкладку Данные в группе Получить внешние данные нажать на кнопку Из Access .
В открывшемся диалоговом окне Выбор источника данных укажите файл нужной вам базы данных.
В следующем диалоговом окне Выбор таблицы укажите таблицу или запрос, который необходимо импортировать в Excel .
У вас откроется еще одно диалоговое окно - Импорт данных . Здесь вы можете выбрать способ отображения данных в виде:
- Таблицы
- Отчета сводной таблицы
- Сводной диаграммы и отчета сводной таблицы
Здесь же можете указать Куда следует поместить данные?
В правом нижнем углу данного диалогового окна имеется кнопка Свойства … кликнув по которой вы переходите в диалоговое окно Свойства подключения .
На вкладке Использование выберите, с какой частотой должны обновляться данные:
- Фоновое обновление - позволяет продолжить работу в Excel пока выполняется запрос обновления данных. Снятие флажка приведет к выполнению запроса во время ожидания.
- Обновлять каждые … - включает автоматическое обновление данных через указанные промежутки времени.
- Обновление при открытии файла - при установлении флажка, данные будут автоматически обновляться только при открытии книги.
- Удалить данные из внешнего диапазона перед сохранением книги - эта опция доступна только при установленном флажка Обновить при открытии файла и в случае импорта данных в виде запроса. Установление флажка позволяет сохранять книгу с определением запроса, но без внешних данных.
Нажав во всех диалоговых окнах кнопку ОК, вы получаете в Excel данные из базы данных Access, которые автоматически обновляются в соответствии с изменениями в исходной базе данных.
Существует два способа импорта данных из текстового файла в Excel: вы можете открыть его в Excel или импортировать как диапазон внешних данных. Чтобы экспортировать данные из Excel в текстовый файл, используйте команду Сохранить как и измените тип файла в раскрывающемся меню.
Чаще всего используются два формата текстовых файлов:
текстовые файлы с разделителями (TXT), в которых для разделения полей текста обычно используется знак табуляции (знак ASCII с кодом 009);
текстовые файлы с разделителями-запятыми (CSV), в которых в качестве разделителя полей текста обычно используется запятая (,).
Вы можете изменить знак разделения, используемый в TXT- и CSV-файлах. Это может потребоваться для обеспечения успешного выполнения импорта и экспорта.
Примечание: Можно импортировать и экспортировать до 1 048 576 строк и 16 384 столбцов.
Импорт текстового файла путем его открытия в Excel
Текстовый файл, созданный в другой программе, можно открыть как книгу Excel с помощью команды Открыть . При открытии текстового файла в Excel его формат не изменяется - это можно заметить по заголовку окна Excel, где имя указывается с расширением текстового файла (например, TXT или CSV).
Перейдите в файл > Открыть и перейдите к расположению, содержащему текстовый файл.
Выберите текстовые файлы в раскрывающемся списке Тип файла в диалоговом окне Открытие .
Найдите нужный файл и дважды щелкните его.
Импорт текстового файла путем подключения к нему (Power Query)
На вкладке " данные " в группе " Получение & данных преобразования " выберите из текста или CSV .
В диалоговом окне Импорт данных найдите и дважды щелкните текстовый файл, который вы хотите импортировать, и нажмите кнопку Импорт .
В диалоговом окне Предварительный просмотр есть несколько вариантов:
Если Excel не преобразует определенный столбец данных в нужный формат, вы можете преобразовать их после импорта. Дополнительные сведения см. в статьях и .
Сохранить как .
Импорт текстового файла путем подключения к нему
Вы можете импортировать данные из текстового файла на существующий лист.
Если Excel не преобразует столбец в необходимый формат, это можно сделать после импорта. Дополнительные сведения см. в статьях Преобразование чисел из текстового формата в числовой и Преобразование дат из текстового формата в формат даты .
Экспорт данных в текстовый файл путем его сохранения
Лист Excel можно преобразовать в текстовый файл, используя команду Сохранить как .
-
Откроется диалоговое окно Сохранение документа .
В поле Тип файла выберите формат текстового файла для листа.
-
Откроется диалоговое окно с напоминанием о том, что в текстовом файле будет сохранен только текущий лист. Если вы хотите сохранить именно текущий лист, нажмите кнопку ОК . Другие листы можно сохранить в отдельных текстовых файлах, повторив эту процедуру для каждого из них.
Откроется второе диалоговое окно с напоминанием о том, что сохраняемый лист может содержать возможности, которые не поддерживаются текстовыми форматами файлов. Если в новом текстовом файле нужно сохранить только данные листа, нажмите кнопку Да . Если вы не уверены и хотите получить дополнительные сведения о том, какие возможности Excel не поддерживаются форматами текстовых файлов, нажмите кнопку Справка .
Дополнительные сведения о сохранении файлов в других форматах см. в статье Сохранение книги в другом формате файла .
Изменение разделителя, используемого в текстовом файле
Если для импорта текстового файла используется мастер импорта текста, то на втором шаге разделитель можно изменить с символа табуляции на другой знак. На этом шаге также можно изменить способ обработки последовательных разделителей (например, идущих подряд кавычек).
Дополнительные сведения о разделителях и дополнительных параметрах можно найти в мастере импорта текста .
Изменение разделителя во всех текстовых CSV-файлах
Нажмите кнопку Пуск и выберите пункт Панель управления .
Откройте диалоговое окно изменения региональных и языковых параметров.
Введите новый разделитель в поле Разделитель элементов списка .
Дважды нажмите кнопку ОК .
Примечание: После изменения разделителя элементов списка все программы на компьютере будут использовать новый символ. Вы можете вернуть стандартный символ, выполнив эти же действия.