Импорт данных в Excel из текстового файла. Импорт данных в Excel из текстового файла Импорт текстового файла путем подключения к нему
Добрый день, читатели блога!
Сегодня мы поговорим о выгрузке данных таблицы 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: вы можете открыть его в 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-файлах
Нажмите кнопку Пуск и выберите пункт Панель управления .
Откройте диалоговое окно изменения региональных и языковых параметров.
Введите новый разделитель в поле Разделитель элементов списка .
Дважды нажмите кнопку ОК .
Примечание: После изменения разделителя элементов списка все программы на компьютере будут использовать новый символ. Вы можете вернуть стандартный символ, выполнив эти же действия.
Набирать информацию с клавиатуры дело хлопотное и трудоемкое. И ладно бы в 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-файлах
Нажмите кнопку Пуск и выберите пункт Панель управления .
Откройте диалоговое окно изменения региональных и языковых параметров.
Введите новый разделитель в поле Разделитель элементов списка .
Дважды нажмите кнопку ОК .
Примечание: После изменения разделителя элементов списка все программы на компьютере будут использовать новый символ. Вы можете вернуть стандартный символ, выполнив эти же действия.
Преобразовать файлы формата csv и txt в формат xls можно двумя способами:
Способ 1
Этот способ подходит как для пользователей программы Microsoft Excel так и для тех, кто пользуется Openoffice.
Текстовый файл или файл csv можно открыть как книгу Microsoft Excel при помощи команды Открыть. При открытии текстового файла его формат не изменяется. Если открыть файл формата csv, приложение Excel откроет его и отобразит данные в новой книге.
В Excel нажмите кнопку Открыть - появится диалогое окно Открыть .
Для операционной системы Windows Vista выбираете пункт Текстовые файлы. Далее найдите в адресной строке нужный текстовый файл и дважды щелкните по нему.
Для Microsoft Windows XP выберите в списке Тип файлов - Текстовые файлы . В списке Папка найдите нужный текстовый файл и дважды щелкните по нему.
Пример как это выглядит Openoffice
Открывается окно Импорт текста
После настройки всех значений нажимаете Ok, и появляется таблица, которую нужно сохранить. Заходите в Файл - Сохранить как - и в диалоговом окне, выбираете формат Microsoft Excel 97/2000/XP (.xls) и файл сохраняется уже в формате xls.
Способ 2.
Подходит для пользователей Microsoft Excel
В имеющийся лист можно импортировать данные из текстового файла путем открытия внешних данных. Выберите ячейку в таблице для импорта внешних данных. Далее на вкладке Данные в группе Внешние данные нажмите кнопку Из текста.
Для операционной системы Windows Vista в адресной строке найдите нужный для импорта файл, и щелкните по нему два раза.
Для Microsoft Windows XP в списке Папка найдите нужный для импорта файл и щелкните по нему два раза. Откроется далоговое окно Импорт текста , в котором вы сможете изменить кодировку (как правило ставится кодировка Юникод (UTF-8)), также можете выбрать разделитель, который разделяет значения в файле csv.
1)
2)
