Работа с внешними данными

Microsoft Excel является замечательным средством, позволяющим анализировать данные, однако, для того чтобы провести любой подобный анализ, сначала необходимо поместить данные в книгу Excel. Во многих случаях информация, которую необходимо обработать, находится  вне  программы:  в  текстовом  файле,  в  файле  XML  на  веб-узле  или  в  базе данных,   созданной   такими   средствами,   как   Oracle   или   Microsoft    Access.   Excel предоставляет удобный инструментарий для импорта такой информации в рабочие листы. Именно об этом пойдет речь в данной главе.

Импорт внешних данных с помощью команды Открыть

Знакомая вам команда Файл ? Открыть (File ? Open) способна  открывать внешние файлы различных форматов, данные из которых затем можно распределять по столбцам электронной таблицы. Поведение  этой таблицы в Excel в какой-то степени зависит от внешнего  источника  данных. В некоторых случаях, например при импорте  текстовых файлов,  внешние  данные  приобретают  стандартный  табличный  вид.  При  желании  их нетрудно преобразовать в объект типа «список». Этот объект можно опубликовать на узле SharePoint,  правда,   опубликованный  список  не  связывается  с  исходным  текстовым файлом   и   потому   не   способен   обновляться   первоначальным   источником   данных. Некоторые форматы внешних данных, такие как таблицы Microsoft Access, становятся в Excel так называемыми диапазонами внешних данных. Они поддерживают обновление с целью соответствовать текущим изменениям данных во внешнем источнике, но их нельзя преобразовывать в объект «список» и публиковать на узле SharePoint.

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

Открытие текстовых файлов

Чтобы   импортировать   данные   текстового   файла   с   разделителями   или   с   полями фиксированной ширины, выберите команду Файл ? Открыть, а затем в диалоговом окне Открытие  документа  (Open)  выберите  тип  Текстовые  файлы  (Text  Files)  в  раскрывающемся списке типов файлов. (В файле с разделителями поля отделяются друг от друга  предопределенными  символами,  обычно  запятыми  или  пробелами.  В  файле  с полями   фиксированной   ширины   каждое   поле   содержит   определенное   количество символов,  и  это  достигается  за  счет  дополнительных  пробелов.)  Вы  увидите  список файлов с  расширениями PRN, ТХТ и CSV. Если интересующий вас файл имеет  другое расширение,  например  LOG  или  AS  С,  выберите  пункт  Все  файлы  (All  Files)  в раскрывающемся списке типов файлов. Все это нужно, чтобы увидеть файл в списке, а на самом деле Excel определяет тип открываемого файла по его содержимому, ^поэтому его расширение не играет никакой роли. Если программа не сумеет распознать формат файла и прочитать данные, она сообщит вам об этом.

Текстовые  файлы,  в  которых  поля  разделены  запятыми  (обычно  такие  файлы  имеют расширение  CSV),  сразу  же  импортируются  в  Excel.  Загрузка  файлов,  в  которых  в качестве разделителей используются другие символы или комбинации символов, а также файлов с полями фиксированной ширины осуществляется при помощи Мастера импорта текста, первое окно которого показано на рис. 29.1.

Рис.  29.1.  Мастер  импорта  текста  позволяет  задать  некоторые  параметры,   которые  Excel

задействует при анализе текста

Использование Мастера импорта текста

Мастер  импорта  текста  направляет  Excel,  указывая,  каким  образом   следует  интерпретировать содержимое текстового файла. Но многое здесь зависит от пользователя. Вы можете   указать   символ   или    комбинацию   символов,   служащие   разделителями   и ограничителями  полей (столбцов), тип данных для каждого столбца, а также кодировку исходного файла. Кроме того, мастер позволяет исключить одну или более строк в начале импортируемого файла, содержащих описательную  информацию, что часто встречается на практике.

В   первом   окне   мастера,   представленном   на   рис.   29.1,   отображен   фрагмент   импортируемого файла, а также результат автоматического определения формата данных (с разделителями или фиксированной ширины). Как правило, мастер не ошибается, но если что-то пошло не  так,  вы всегда можете его поправить. (Если вы сами точно не знаете формата исходного файла, нажмите кнопку Далее (Next), и уже на следующем шаге вам станет ясно, правильно ли был сделан выбор.) Перед тем как перейти к следующему шагу, следует   убедиться   в   том,   что   программа   верно   распознала   кодировку   символов, назначенную   текстовому   файлу.   Если   он   был   создан   приложением   Windows,   в

раскрывающемся   списке   Формат  файла  (File  Origin)   нужно   выбрать   кодировку Windows (ANSI). Если файл был создан программой под управлением MS-DOS, выберите вариант DOS (PC-8). Если же в файле используется какая-либо другая таблица символов, попробуйте  найти  ее  в  списке  Формат  файла.  Для  файлов,  происходящих  из  других операционных систем, наиболее вероятной является кодировка Windows (ANSI).

Пропустить несколько первых строк файла можно с помощью счетчика Начать импорт со строки (Start Import At Row).  Строки  с  заголовками  полей  зачастую приводят  к ошибкам   при   распределении   исходных   данных   по   столбцам,   поэтому   их   лучше исключать.

Вид следующего окна мастера зависит от формата файла, поэтому мы  представили два рисунка — 29.2 и 29.3. В обоих случаях вертикальные линии в области предварительного просмотра Образец разбора данных (Data Preview) показывают, каким образом Excel собирается разобрать исходный текст по столбцам. Область просмотра позволяет видеть одновременно  лишь  5  строк  и  65  символов  в  каждой  строке,  но  просмотреть  любой фрагмент файла можно с помощью полос прокрутки.

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

распознает в качестве разделителя и как данные будут распределены по столбцам

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

Флажок   Считать   последовательные   разделители   одним   (Treat   Consecutive Delimeters As One) позволяет задавать  последовательности  разделительных символов. Например,  если  исходно   разделителем  являлась  группа  из  двух  или  более  знаков табуляции, а вы посчитаете таковым каждый отдельный знак табуляции, это приведет к неправильному   разбиению   данных   на   столбцы.   Поэтому   в   некоторых   ситуациях использование данного флажка является необходимым.

При  импорте  файла  с  фиксированной  шириной  полей  первоначальная  попытка  Excel

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

26.3.  К  счастью,  эта  проблема  относительно  легко  решается.  (Если  файл  длинный  и ошибок  интерпретации  много,  поиск  всех  их  может   оказаться  не  очень  приятным занятием; и если у вас есть выбор  между  импортом файла с фиксированной шириной полей  и   эквивалентным  файлом  с  разделителями,  вы  сбережете  время  и   избежите проблем,  открывая  файл  с  разделителями.)  Поправить  Excel  совсем  нетрудно:  просто перетащите  вертикальные  линии  в  другие   позиции.  Чтобы  вставить  новую  линию, разделяющую столбцы, достаточно щелкнуть в нужной позиции. Чтобы удалить линию, дважды щелкните на ней.

Рис. 29.3. Импортируя файл с полями фиксированной ширины, доверяйте Excel, но проверяйте.

При наличии ошибок (что вполне возможно) внесите необходимые исправления

Третье  окно  Мастера  текстов  (рис.  29.4)  позволяет  задать  тип  данных  для  каждого столбца.  Ваш  выбор  ограничен  следующими  вариантами:  Общий  (General),  когда  к тексту применяется текстовой формат, к числам — числовой, а к датам — формат даты; Текстовой (Text), где все данные, включая числа, форматируются как текст; Дата (Date) и Пропустить столбец (Skip). По умолчанию Excel назначает  для всех столбцов тип Общий, и, разумеется, в некоторых ситуациях вам придется внести изменения. Например, если  значение  в  одном  из   текстовых  полей  начинается  с  дефиса,  Excel  пытается интерпретировать  дальнейший  текст  как  формулу,  так  как  по  умолчанию  для  типа

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

Рис. 29.4. В этом окне мастера задается формат для каждого столбца. Здесь же можно отказаться от импорта некоторых столбцов

ВНИМАНИЕ

Числа, содержащие 16 и более цифр (такие, как номера кредитных карточек), искажаются Excel, если они не представлены в текстовом  формате. Это объясняется ограничением точности представления чисел в Excel, которая составляет лишь 15 цифр.

Диалоговое окно третьего шага мастера также содержит кнопку Подробнее (Advanced), открывающую  окно  Дополнительная  настройка  импорта  текста  (Advanced  Text Import  Settings).  Это   диалоговое  окно  позволяет  изменить  параметры  определения числовых    данных,    в    том    числе    символы-разделители    десятичных    разрядов    и целой/дробной  части  числа.  По  умолчанию  действуют   установки  раздела  Язык  и региональные  стандарты  (Regional  Settings) Панели  управления  Windows.  Если  в файле используются другие параметры, необходимо внести соответствующие коррективы в окне Дополнительная настройка импорта текста.

Разбор текста, вставляемого из буфера обмена

Иногда при работе с внешними текстовыми данными встречаются  длинные текстовые строки, которые нужно разделить на части и  вставить  последние в отдельные столбцы. Такое бывает, например, при  вставке данных в книгу Excel через буфер обмена. Чтобы распределить  такие данные по столбцам, выделите их, а затем в меню Данные  (Data) выберите команду Текст по столбцам (Text To Columns). Вы  вновь окажетесь под опекой Мастера текстов, но сменившего название.

Открытие таблиц Microsoft Access

С помощью команды Файл ? Открыть (File ? Open) вы можете  импортировать таблицы  из  баз  данных  Microsoft  Access.  После  открытия  диалогового  окна  Открытие документа  (Open)  выберите   тип   Базы  данных  Access  (Access  Databases)  в раскрывающемся списке типов файлов. Укажите нужный файл .MDB и нажмите кнопку Открыть  (Open).  На  экране  появится  диалоговое  окно  Выделить  таблицу  (Select Table), показанное на рис. 29.5.

Рис.   29.5.   Диалоговое   окно   Выделить   таблицу   содержит   список   таблиц   и представлений, доступных для импорта в Excel

Список в диалоговом окне Выделить таблицу на самом деле содержит  наряду с таблицами  также  и  представления.  (Например,  все   элементы  на  рис.  29.5  являются представлениями  из  базы  данных  «Борей»,  входящей  в  комплект  поставки  Microsoft. Access.)  Если  вы   откроете  представление  Access  в  Excel,  то  получите  все  записи, отображаемые этим представлением в Access. При открытии таблицы импортируются все ее записи.

В любом случае список в Excel будет связан с источником данных в Access, поэтому вы можете  обновлять  его.  Чтобы  список  соответствовал  текущему  состоянию  источника данных  в  Access,  выделите  любую  ячейку  в  списке  и  выберите  команду  Обновить данные (Refresh External Data) в меню Дата (Data) или нажмите одноименную кнопку на панели инструментов Внешние данные (External Data). При потребности обновлять список  по  расписанию  выберите  команду  Данные  ?  Импорт  внешних  данных ? Свойства  диапазона  данных  (Data  ?  Import  External  Data  ?   Data  Range Properties)   или   воспользуйтесь   соответствующей    кнопкой   панели   инструментов Внешние данные и затем укажите временной интервал в минутах.

Открытие файлов dBase

Чтобы открыть файл dBase в Excel, выберите команду Файл ? Открыть и затем в диалоговом окне Открытие документа выберите тип  Файлы  dBase (dBase Files) в раскрывающемся списке типов файлов. После этого в диалоговом окне будут отображаться только файлы с  расширением DBF. Если по каким-то причинам ваш файл dBase имеет

другое расширение, выберите в списке типов файлов пункт Все файлы (Alt Files). Excel

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

Команда Файл ? Открыть импортирует файл dBase целиком в  необновляемый список Excel.   Чтобы   импортировать   только   определенные   записи   и   иметь   возможность синхронизировать их со  внешним источником, вы должны создать запрос с помощью команды  Данные ? Импорт внешних данных ? Создать запрос (Data ?  Import External Data ? New Database Query).

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий