Импорт текстовых данных в Excel
 | В почту редакции периодически поступают вопросы о том, как обработать текстовый файл в программе Excel. И это неудивительно. Информацию для работы бухгалтер получает из разных источников. Это и сведения из Интернета, и данные для сверки от внешних контрагентов — вариантов масса. Соответственно и способы представления информации могут быть самыми разными. Причем текстовый формат среди этого разнообразия встречается довольно часто. Ведь текст — самый универсальный способ представления данных. Экспорт в текстовый файл поддерживает большинство бухгалтерских программ. Потому и потребность работы с текстовыми документами на практике возникает часто. К сожалению, для обработки и организации вычислений текст малопригоден. Чтобы выполнить операции над данными из текстового файла, его нужно правильно импортировать в электронную таблицу Excel. Какие возможности по импорту предлагает эта программа и как ими воспользоваться, я расскажу в этой статье. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Прежде чем приступить к решению проблемы, нужно вначале разобраться, в чем она состоит. В нашем случае мы должны четко понять, какова специфика импортирования текстовых документов в MS Excel и почему для решения этой задачи нужны специальные инструменты. А поскольку бухгалтер работает преимущественно с табличными данными, речь пойдет об особенностях импортирования в Excel именно таблиц.
Особенности импортирования текстовых документов в Excel
Как известно, у каждого приложения Windows есть свой, характерный для него формат. Будь то документ Word, электронная таблица Excel или база Access — все они имеют определенную структуру данных. Благодаря наличию такой внутренней структуры при переносе данных из одного приложения в другое можно с успехом пользоваться буфером обмена. При этом Windows сам определит формат источника данных, формат данных получателя и выполнит все необходимые преобразования. Вот пример. Мы можем в программе Word открыть файл с таблицей, выделить ее и скопировать в буфер обмена. Затем вызвать программу Excel и вставить информацию из буфера. В большинстве случаев Windows все сделает правильно. Он определит, что в буфере находится таблица в формате Word. Зная эту информацию, Windows извлечет содержимое буфера обмена и правильно преобразует его в формат программы Excel. Почему такое преобразование в принципе возможно? По той простой причине, что при создании документа в программе Word вы использовали специальную команду для вставки таблиц. И при этом вы однозначно определили структуру таблицы: количество строк, колонок, содержимое ячеек, параметры объединения и т. п. Разумеется, все эти сведения Word сохранил в своем файле. А Windows, зная эту информацию, преобразовал данные из формата Word в документ Excel. Конечно, определенные погрешности при этом могут появиться, но структура таблицы сохранится, т. е. каждый элемент исходной таблицы будет занимать отдельную ячейку на листе MS Excel.
С текстовым документом ситуация другая. Здесь никакой информации о структуре данных нет. Если в текстовом файле записана таблица, то каждая ее строка — это обычная последовательность символов, с помощью которых нарисованы отдельные ячейки, и не более того. В текстовом файле нет информации о том, где начинается ячейка таблицы и где она заканчивается. Посмотрим, как это выглядит на конкретном примере.
В качестве исходного файла я выбрал документ, представленный на рис. 1. Это небольшой фрагмент отчета о проводках. Он был сформирован в программе автоматизации бухгалтерского учета и оттуда экспортирован в текстовый файл с именем «
Prov.txt». Делаем так: 
1) открываем MS Word, вызываем меню «Файл → Открыть»;
2) в окне Проводника находим и выбираем файл «Prov.txt» и нажимаем кнопку «Открыть» (или клавишу «Enter»);
3) нажимаем «Ctrl+A» (выделить весь документ);
4) нажимаем «Ctrl+C» (копировать в буфер);
5) открываем программу Excel, появится пустой документ;
6) нажимаем «Ctrl+V» (вставить из буфера). Результат наших действий показан на рис. 2.
Внешне документ на листе Excel похож на таблицу. Но если присмотреться внимательнее, то это не так. Щелкаем левой кнопкой мыши на ячейке «A4». В строке формул на рис. 2 показано содержимое этой ячейки. Как видим, это текстовая строка. В ней подряд идут и символы-разделители, и числа, и поясняющий текст. Допустим, мы решили посчитать по данным таблицы суммарный оборот по всем счетам. Сделать это не удастся — все числа колонки «Сумма» являются частью текстовой строки, а не самостоятельными ячейками. Вот поэтому для импортирования данных из текстового файла в программу Excel нам нужно воспользоваться специальным средством для импортирования таблиц.
Но прежде чем приступить собственно к импортированию данных, нужно немного видоизменить текстовую таблицу.
Подготовка данных для импортирования в Excel
Первым делом внимательно смотрим на исходный документ. Сейчас наша задача — используя текстовый процессор, сделать исходные данные максимально удобными для последующего импортирования в Excel. Вернемся к документу на рис. 1 и начнем с шапки таблицы. Процесс импортирования данных в Excel очень похож на инструмент «
Текст по столбцам». Это означает, что импортировать данные удобнее, когда они представлены в виде регулярной таблицы.Совет Прежде чем импортировать данные, сделайте так, чтобы они представляли собой базу данных MS Excel. Удалите сложную шапку таблицы, если она есть. Удалите все строки из горизонтальных разделителей, если таковые присутствуют в исходном тексте. После этого импортируйте документ и затем восстановите шапку непосредственно в программе Excel. Времени это займет немного, а данные будут перенесены без лишних проблем.
Применительно к файлу на рис. 1 делаем так:
1) открываем текстовый редактор, это может быть, например, Word, Блокнот или Wordpad;
2) через команду «Открыть» загружаем документ «Prov.txt»;
3) выделяем первые три строки текста и удаляем их (клавиша «Del»);
4) удаляем последнюю строку в файле, которая играет роль горизонтального разделителя. Теперь документ приобрел форму регулярной таблицы (рис. 3).
Переходим к содержимому таблицы. Мы видим, что в исходном текстовом файле числа представлены в непонятном для Excel формате. Напомню, что в Excel целая и дробная части числа разделены символом «,» (запятая). А у нас разделителем целой и дробной части выступает «.» (точка). На первый взгляд исправить ситуацию проще простого: вызвать инструмент контекстной замены и откорректировать с его помощью весь документ. Однако в нашем примере этого сделать нельзя. Проблема в том, что символ «,» встречается не только в числах, но и поясняющем тексте (колонки «Комм» и «Документ», рис. 1). Выделить отдельную колонку в текстовой таблице не получится. Так что контекстная замена, если ее применить, откорректирует весь документ, а этого нам не нужно. Поэтому мы оставим числа в таблице, как они есть, и все преобразования с ними произведем на этапе импортирования данных. Приступим.
Импортирование данных с разделителями
В файле на рис. 3 отдельные элементы таблицы разделены символом «
:» (двоеточие). Этот факт существенно упрощает задачу. Делаем так:1) вызываем программу Excel, заходим в меню «
Файл → Открыть…»;2) в окне Проводника (рис. 4) в списке «
Тип файлов:» выбираем вариант «Текстовые файлы (*.prn; *.txt; *.csv)»; 
3) указываем путь к папке с текстовым документом;
4) находим нужный файл (у нас это «Prov.txt»), выделяем его и щелкаем на кнопке «Открыть». Появится окно «Мастер текстов (импорт)», как на рис. 5;
5) переключатель «Укажите формат данных:» ставим в положение «с разделителями»;
6) щелкаем на списке «Формат файла:». Здесь нужно выбрать такой вариант, когда в окошке «Предварительный просмотр файла…» (рис. 5) будут правильно отображаться все символы русского текста. В большинстве случаев — это варианты «Windows (ANSI)» или «866: Кириллица (DOS)»;
7) внимательно смотрим на содержимое окна «Предварительный просмотр файла…». Если в верхней части таблицы сохранились лишние данные (например, вы забыли удалить шапку таблицы), укажите параметр «Начать импорт со строки:». Этот параметр позволяет пропустить несколько начальных строк при импортировании текстового файла;
8) закончив ввод параметров, нажмите «Далее». Появится окно, как на рис. 6. В нем Excel предложит указать символ-разделитель между отдельными колонками текста в исходной таблице. Можно выбрать один из стандартных вариантов (точка с запятой, знак табуляции, запятая, пробел) или указать любой другой символ;
9) щелкаем левой кнопкой на флажке «другой:», в соседнем поле (справа) печатаем символ «:». В области предварительного просмотра Excel покажет ожидаемый результат разбиения текстовой таблицы на колонки при ее импортировании в Excel (рис. 6). Теперь мы должны определить, какие колонки нужно переносить в будущую таблицу, какие можно пропустить, как их преобразовать и каким образом нужно представить числа;
Совет Обратите внимание на флажок «
Считать последовательные разделители одним». Если этот флажок включен, то последовательность из нескольких подряд идущих разделителей при импортировании Excel обработает как один разделитель. Чаще всего такая возможность нужна при импортировании таблиц, где единственным разделителем между колонками является символ «пробел». Причем количество пробелов между разными элементами таблицы неодинаковое.10) щелкаем на кнопке «Далее». Появится окно, как на рис. 7. В этом окне мы можем последовательно выбирать колонки таблицы в области «Образец разбора данных» и для каждой колонки устанавливать в соответствующее положение переключатель «Формат данных столбца»;
11) выделяем первую колонку (слева), переключатель «Формат данных столбца» переводим в положение «пропустить столбец»;
12) щелкаем на второй колонке (в ней записаны даты проводок). Для этой колонки переключатель «Формат данных столбца» ставим в положение «Дата». Формат представления даты оставляем по умолчанию;
13) удерживая клавишу «Shift», последовательно щелкаем на третьей и четвертой колонках. Для них выбираем вариант преобразования «Текстовый»;
14) удерживая клавишу «Shift», выделяем седьмую и восьмую колонки. Здесь тоже выбираем вариант «Текстовый»;
15) выделяем пятую и шестую колонки, в которых записаны числа (количество и сумма проводки);
16) переключатель «Формат данных столбца» для этих колонок ставим в положение «Общий»;
17) щелкаем на кнопке «Подробнее…». Откроется окно «Дополнительная настройка импорта текста», как на рис. 8;
18) в поле «Разделитель целой и дробной части:» вводим символ «.» (или выбираем его из списка). Тем самым мы сообщили Excel, что числа в исходном документе используют в качестве разделителя целой и дробной части символ «.» (точка);
19) в окне «Дополнительная настройка импорта текста» нажимаем «ОК»;
20) в окне «Мастер текстов (импорт)» нажимаем «Готово». Результат загрузки документа в таблицу в MS Excel показан на рис. 9. Остается напечатать шапку таблицы, установить формат данных, ширину колонок — и задача решена.
Импортирование данных без разделителей
Бывают ситуации, когда в текстовом файле нет разделителей между колонками таблицы, но эти колонки можно определить визуально. Такой документ тоже можно загрузить в Excel. Но для этого нужно воспользоваться специальным режимом Мастера текстов. Делаем так:
1) вызываем Excel, открываем документ «
Prov.txt». Появится окно «Мастер текстов (импорт)», как на рис. 5;2) переключатель «
Укажите формат данных:» ставим в положение «фиксированной ширины»;3) нажимаем кнопку «
Далее». Окно примет форму, как на рис. 10. В этом окне всю работу по определению колонок таблицы придется сделать вручную, прямо в области «Образец разбора данных»; 
4) в области «Образец разбора данных» последовательно щелкаем левой кнопкой мыши там, где Excel должен начать новую колонку таблицы. На месте щелчка появится тонкая вертикальная стрелка. В нашем примере я установил границы колонок, как показано на рис. 10;
Важно! Чтобы удалить вертикальный разделитель, нужно дважды щелкнуть на нем левой кнопкой мыши. Удерживая левую кнопку мыши, можно перемещать разделитель по горизонтали.
5) после того как все разделители расставлены, нажимаем кнопку «Далее» и переходим к третьему шагу работы с Мастером текстов. Здесь работа уже ничем не отличается от импортирования таблиц с разделителями — последовательно выбираем колонки для импорта, указываем их формат и правило преобразования;
6) завершив работу на третьем шаге, нажимаем «ОК». Импортированная таблица появится на листе Excel.
Вот, собственно, и все, что нужно знать о приемах импортирования текстовых данных в программу Excel.
Успешной работы! Жду ваших писем, предложений и замечаний на
bk@id.factor.ua , nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum .
