Импорт данных посредством запросов

Команда Файл ? Открыть удобна в применении, но она передает в Excel внешний файл только  целиком.  Если  вам  требуется  импортировать  данные,  которые  находятся  на сервере баз данных, например SQL Server или Oracle, или вам нужна только определенная часть данных из большого внешнего файла, в таких случаях не обойтись без запросов.

При импорте данных с помощью запроса вы получаете возможность ограничить импорт только  теми  записями,  которые  удовлетворяют  определенным  условиям.  Более  того, импортируемые   данные   становятся   так   называемым   диапазоном   внешних   данных текущего листа, что позволяет обновлять запрос периодически или по требованию.

Запросы к базам данных в Excel осуществляются в большинстве  случаев  при помощи созданного для этого средства пакета Office под  названием Microsoft Query. Microsoft Query  генерирует  запросы  и   передает  их  источнику  данных,  что  избавляет  вас  от необходимости создавать эти запросы вручную на языке SQL. Если запрос относительно простой,  достаточно  ограничиться  применением  мастера,  сводящего   вашу  работу  к четырем шагам.

Задействовав механизм запросов, вы можете извлечь информацию из любой базы данных, для которой в системе установлен драйвер ODBC или OLE DB. ODBC — это стандартный интерфейс   взаимодействия   с   системами   управления   базами   данных,   a   OLE   DB фактически является усовершенствованием интерфейса ODBC (OLE DB имеет несколько возможностей,  недоступных  для  ODBC,  и  Excel  подключается  к  источникам  данных ODBC средствами OLE DB). Excel  поддерживает файлы SQL Server, SQL Server OLAP Services, Access, Visual FoxPro, Oracle, dBase, текстовые, а также некоторые другие типы. Механизм запросов Excel позволяет также запрашивать данные из списков SharePoint и из внешних таблиц Excel.

Повторное использование запроса

Если  вы  уже  создали  запрос  к  базе  данных,  его  параметры  можно  сохранить  в  специальном файле, чтобы при необходимости снова  использовать этот запрос. Для того чтобы  открыть  файл  запроса,  выберите  пункт  меню  Данные  ? Импорт  внешних данных ? Импортировать данные (Data ? Import External Data ? Import Data). На экране  появится  диалоговое  окно  Выбор  источника  данных  (Select  Data  Source), показанное на рис. 29.11. Как можно  видеть, оно напоминает стандартное диалоговое окно Открытие документа.

Рис.   29.11.   Диалоговое   окно   Выбор   источника   данных   позволяет   повторно   использовать существующий запрос

На  самом  деле  это  сходство  только  внешнее.  Несмотря  на  то  что  в  панели  адресов отображается ярлык Мои источники данных (папка, вложенная в папку Мои документы), файлы,  имена  которых  видны  в  окне,  могут  находиться  также  и  в  папках  Program Files\Common Files\ODBC\Data Sources и Officell\Queries.

Скорее всего, ваше диалоговое окно Выбор источника данных содержит пять элементов,

как на рис. 29.11: +Новое подключение к SQL-серверу,  +Подключение к новому

источнику данных (знаки плюса включены в названия для того, чтобы названия попали в начало списка), а также три пункта, связанные с сервисом MSN Money-Central. Если на вашем компьютере имеются дополнительные файлы запросов или подключений, их имена также будут присутствовать в окне.

При выполнении запроса к базе данных, в зависимости от того, каким  образом он был создан, на экране может появиться диалоговое окно  Выбор таблицы (Select Table). Выберите из списка нужную вам-таблицу и нажмите кнопку ОК. Вы также вправе сразу перейти к диалоговому окну Импорт данных (Import Data), показанному на рис. 29.15. Укажите, куда вы хотите импортировать данные, и нажмите кнопку ОК.

СОВЕТ

В  диалоговом  окне  Импорт  данных  есть  возможность  настроить   параметры  процесса импорта или самого запроса. Для этого  предназначены кнопки Параметры (Parameters) и Изменить запрос (Edit  Query). Запросы, которые при выполнении запрашивают значения параметров,  также  можно  настроить  с  помощью  кнопки  Параметры.  Если  вы  не  хотите заниматься  настройкой  прямо  сейчас,  вы  вправе  это  сделать  в  любой  момент  после выполнения  запроса  (см.  разделы  «Режимы  обновления  и  другие  свойства  запроса»  и

«Редактирование запроса»).

Создание запроса к базе данных

Первым шагом при создании запроса к базе данных является установление соединения с источником  данных.  Выберите  команду   Данные  ? Импорт  внешних  данных  ? Импортировать данные и затем в диалоговом окне Выбор источника данных нажмите кнопку Создать (New Source). Далее процессом будет руководить Мастер подключения данных, начальное окно которого изображено на рис. 29.12.

Рис.    29.12.    Мастер    подключения    данных    запрашивает    информацию,

необходимую для подключения к внешнему источнику

Если необходимый источник данных отсутствует в списке, следует выбрать пункт ODBC DSN, Дополнительно (Other/Advanced) или Службы извлечения данных (Other Data Services Provider) и  щелкнуть  на кнопке Далее (Next). В первом случае будет выдан список источников данных ODBC (включающий в себя файлы dBase, MS Access и Excel), доступных через посредничество OLE DB, а во втором —  длинный список источников данных,    связываемых    напрямую    через    OLE   DB.    Службы    извлечения    данных обеспечивают доступ к  спискам и библиотекам Windows SharePoint Services, а также к источникам данных Microsoft SQL Server и Microsoft Business Solutions.

СОВЕТ

Если вам не удалось найти нужный источник данных в списках ODBC DSN и Дополнительно,

см. следующий раздел.

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

Создание DSN-файла

Если  вам  не  удалось  найти  нужный  источник  данных  с  помощью   команды  Импортировать  данные,  выберите  команду  Данные  ?  Импорт  внешних  данных  ? Создать запрос (New Database Query) и затем выполните следующие действия:

1.   Дважды  щелкните  на  пункте  <Новый источник  данных> (<New  Data  Source>), чтобы открыть диалоговое окно Создание нового источника данных (Create New Data Source). В этом окне вы должны последовательно заполнить четыре поля.

2.   Задайте имя источника данных в первом из полей. Это имя будет  присвоено DSN-

файлу по окончании процесса создания.

3.   Укажите драйвер во втором поле.

4.   Нажмите кнопку  Связь (Connect), расположенную под цифрой 3.  Это приведет к появлению нового диалогового окна, в котором вам следует конкретизировать версию драйвера. Например, для драйвера dBase вам нужно уточнить версию СУБД — dBase 5.0 или dBase IV.

5.   Если  файлы,  к  которым  вы  хотите  получить  доступ,  находятся  в  текущей  папке, оставьте  установленным  флажок  Использовать   текущий  каталог  (Use  Current Directory). Если же необходимые  файлы расположены в другой папке, снимите этот флажок  и   укажите   ее  местоположение,  нажав  кнопку  Выбор  каталога   (Select Directory).

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

Обратите  внимание:  в  случае  драйвера  Microsoft  Text  Driver  Excel   станет  рассматривать каждый текстовый файл, находящийся в указанной папке, как «таблицу». Для   Microsoft   Excel   Driver   «таблицей»   будет   считаться   любой   именованный

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

В результате в папке Мои источники данных (My Data Sources) появится новый DSN-

файл. Теперь для выполнения запроса следует его открыть.

Создание простого запроса

В  качестве  примера  рассмотрим  простой  запрос  к  базе  данных  «Борей»  (North-wind), включенной в комплект поставки Microsoft Access. При установке Microsoft Access файл Борей.mdb (Northwind.mdb),  содержащий пример учебной базы данных, помещается в папку  Officell\Samples. В этом файле хранится множество таблиц, однако  наш  запрос будет адресован лишь одной из них — таблице с именем. Клиенты (Customers).

Начните с выполнения следующих действий:

1.   Выберите команду Данные ? Импорт внешних данных ? Импортировать данные (Data ? Import External Data ? Import Data), а затем в диалоговом окне Выбор источника данных (Select Data Source) нажмите кнопку Создать (New Source).

2.   В  начальном  окне  Мастера  подключения  данных  выберите  вариант  ODBC DSN и нажмите кнопку Далее (Next).

3.   В следующем окне мастера выберите тип База данных MS Access (MS Access Database) и нажмите Далее. На экране появится диалоговое  окно  Выбор базы данных (Select Database), показанное на рис. 29.13.

Рис. 29.13. Это диалоговое окно используется для поиска файла базы данных

Отыскав файл Борей.mdb и нажав кнопку ОК, вы увидите список таблиц, содержащихся в файле (рис. 29.14). По умолчанию флажок  Подключение к определенной таблице (Connect To A Specific  Table)  установлен, то есть ваш запрос будет ориентирован на конкретную таблицу из файла Борей.mdb. Это условие не является обязательным, и вы можете, сняв указанный флажок, создать общий  запрос к базе данных, а имя таблицы уточнить по ходу выполнения запроса.

Рис.  29.14.  В  этом  окне  мы  можем  подключить   определенную   таблицу  или отложить выбор таблицы до момента выполнения запроса

Оставив  флажок  Подключение  к  определенной  таблице  установленным,  выберите таблицу  Клиенты  (Customers)  и  нажмите  Далее.  Пройдя  следующий  этап  работы мастера, вы вернетесь к диалоговому окну Выбор источника данных, в котором будет отображено имя файла нового подключения. После того как вы  выберете этот файл и нажмете Открыть (Open), вы увидите диалоговое окно Импорт данных (Import Data), показанное на рис. 29.15.

Рис. 29.15. Последний шаг — задание места для размещения импортируемых данных

Основное  назначение  окна  Импорт  данных  —  определить  место   для   размещения импортируемых  данных.  Вы  можете  ограничиться   только  указанием  места,  но  при желании также построить на основе  этих данных сводную таблицу, щелкнув на ссылке Создать отчет  сводной таблицы (Create A PivotTable Report). (Сводные таблицы рассматриваются в главе 30 «Анализ данных при помощи сводных таблиц».) Кроме того, вы  вправе  изменить  свойства  запроса  с  помощью  кнопки  Свойства (Properties) или вызвать   Мастер   запросов   с   помощью   кнопки   Изменить   запрос   (Edit   Query).

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

«Борей».

Рис. 29.16. Результат запроса к базе данных «Борей»

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

«Работа  со  списками»,  изменять  форматирование,  производить  с  данными  различные вычисления и т. д. Разница между таблицей  результатов запроса и обычным списком Excel заключается в следующем:

? Excel запоминает, откуда поступили данные, и поддерживает связь  с  их источником.

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

? Таблицу результатов нельзя преобразовать в объект «список» и опубликовать на узле SharePoint  до  тех  пор,  пока  она  связана  с  внешним  источником  данных.  Чтобы разорвать связь, выберите команду Свойства диапазона данных. В диалоговом окне Свойства внешнего диапазона снимите флажок Сохранить определение запроса (Save Query Definition) и затем подтвердите свое решение.

Другими  словами,  логично  рассматривать  таблицу  результатов  как   диапазон  ячеек, обладающий   специальным   качеством:   способностью    обновляться.   Единственными способами обнаружить отличие такого  диапазона от обычного является просмотр меню Данные   и   взгляд   на    панель   инструментов   Внешние   данные   (External   Data), появляющуюся по умолчанию каждый раз после выполнения нового запроса (рис. 29.17). Если активная ячейка находится внутри диапазона  данных запроса, команды Импорт внешних данных (Import External  Data), Изменить запрос (Edit Query), Свойства диапазона  данных  (Data  Range  Properties)  и  их  значки  на  панели  инструментов Внешние данные становятся доступными.

Рис. 29.22. В этом диалоговом окне вы можете сохранить запрос и выбрать вариант дальнейших действий

Для того чтобы иметь возможность использовать созданный запрос в  другой рабочей книге, его необходимо сохранить. По умолчанию  результирующий файл помещается в

папку Мои источники данных, и именно в этой папке его следует искать после выбора команды Данные ? Импорт внешних данных  ? Импортировать данные (Data ? Import External Data ? Import Data). Кроме того, этот файл можно открыть с помощью команды  Данные ? Импорт внешних данных ? Создать запрос (Data  ? Import External Data ? Create Query), даже если он не является новым запросом к базе данных.  Его  имя  будет присутствовать  на  вкладке  Запросы (Queries) диалогового  окна Выбор источника данных (Select Data Source).

Заключительное окно мастера позволяет перейти прямо в Microsoft Query для дальнейшей работы с запросом. Если ваша цель уже достигнута, выберите вариант Вернуть данные в Microsoft Excel (Return Data To Microsoft Excel) и нажмите кнопку Готово (Finish).

СОВЕТ

Любой DQY-файл запускается из окна Проводника Windows, подобно обычному файлу .XLS.

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

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

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

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