Імпорт текстових даних в 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 .
