Применимо к: Microsoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise EditionMicrosoft SQL Server 2005 Standard Edition

Содержание

АННОТАЦИЯ

Описание приема

Требования

Примеры

Import или Append

Использование DTS или SSIS

Использование связанного сервера

Использование распределенных запросов

Использование ADO и SQLOLEDB

Использование ADO и Поставщика данных Jet

Устранение неполадок

Аннотация

В этом пошаговом руководстве описываются различные способы импорта данных из листов Microsoft Excel в базы данных Microsoft SQL Server.

Описание примеров

В примерах, приведенных в данной статье, импорт данных Excel выполняется с помощью следующих функций:

• Службы DTS SQL Server
• Службы Integration Services Microsoft SQL Server 2005 (SSIS)
• Связанные серверы SQL Server
• Распределенные запросы SQL Server
• Поставщик объектов данных ActiveX (ADO) и Microsoft OLE DB для SQL Server
• Поставщик ADO и Microsoft OLE DB для Jet 4.0

Требования

В приведенном ниже списке перечислены рекомендованное оборудование, программное обеспечение, сетевая инфраструктура, а также необходимые пакеты обновления:

• Экземпляр Microsoft SQL Server 7.0, Microsoft SQL Server 2000 или Microsoft SQL Server 2005
• Microsoft Visual Basic 6.0 для примеров объектов ADO, использующих Visual Basic

В ряде разделов данной статьи предполагается, что пользователь обладает достаточными знаниями в следующих областях:

• Службы преобразования данных
• Связанные серверы и распределенные запросы
• Разработка объектов ADO на Visual Basic

Примеры

Import или Append

В примерах команд SQL, используемых в статье, показаны запросы Create Table для импорта данных Excel в новую таблицу SQL Server с использованием конструкций SELECT...INTO...FROM. При сохранении ссылок на объекты-источники и получатели выражения, приведенные в примерах, могут быть преобразованы в запросы Append с использованием конструкций INSERT INTO...SELECT...FROM.

Использование DTS или SSIS

Для импорта данных Excel в таблицы SQL Server могут быть использованы мастер импорта служб преобразования данных (DTS) SQL Server или мастер импорта и экспорта SQL Server. При работе с мастером и выборе исходных таблиц Excel помните, что имена объектов Excel со знаком доллара ($) являются именами листов (например, Лист1$), а имена объектов без знака доллара являются названиями именованных диапазонов Excel.

Использование связанного сервера

Для упрощения запросов книга Excel может быть настроена как связанный сервер в SQL Server.

Для получения дополнительных сведений щелкните указанный ниже номер статьи базы знаний Майкрософт:

3 06397 ИНСТРУКЦИЯ: Использование Excel со связанными серверами SQL Server и распределенными запросами

Следующий код импортирует данные из рабочего листа «Customers» связанного сервера Excel «EXCELLINK» в новую таблицу SQL Server с именем XLImport1:

SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]

При использовании OPENQUERY источнику может быть передан сквозной запрос:

SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,

'SELECT * FROM [Customers$]')

Использование распределенных запросов

Если устанавливать существующее подключение к книге Excel как связанному серверу нежелательно, данные могут быть импортированы с использованием функций OPENDATASOURCE или OPENROWSET. В следующих примерах кода также производится импорт данных из рабочего листа Excel «Customers» в новые таблицы SQL Server:

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

Использование ADO и SQLOLEDB

Синтаксис «распределенных запросов», приведенный в разделе Использование распределенных запросов , может быть использован также в приложении ADO для импорта данных Excel в SQL Server, если для подключения к SQL Server используется Microsoft OLE DB для SQL Server (SQLOLEDB).

Для работы следующего примера программы на Visual Basic 6.0 требуется добавление ссылки на проект в объекты данных ActiveX (ADO). В этом примере показано использование функций OPENDATASOURCE и OPENROWSET для подключения SQLOLEDB.

Dim cn As ADODB.Connection

Dim strSQL As String

Dim lngRecsAff As Long

Set cn = New ADODB.Connection

cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _

"Initial Catalog=<database>;User ID=<user>;Password=<password>"

'Import by using OPENDATASOURCE.

strSQL = "SELECT * INTO XLImport6 FROM " & _

"OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _

"'Data Source=C:\test\xltest.xls;" & _

"Extended Properties=Excel 8.0')...[Customers$]"

Debug.Print strSQL

cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

Debug.Print "Records affected: " & lngRecsAff

'Import by using OPENROWSET and object name.

strSQL = "SELECT * INTO XLImport7 FROM " & _

"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _

"'Excel 8.0;Database=C:\test\xltest.xls', " & _

"[Customers$])"

Debug.Print strSQL

cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

Debug.Print "Records affected: " & lngRecsAff

'Import by using OPENROWSET and SELECT query.

strSQL = "SELECT * INTO XLImport8 FROM " & _

"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _

"'Excel 8.0;Database=C:\test\xltest.xls', " & _

"'SELECT * FROM [Customers$]')"

Debug.Print strSQL

cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

Debug.Print "Records affected: " & lngRecsAff

cn.Close

Set cn = Nothing

Использование ADO и поставщика данных Jet

В примере из предыдущего раздела при импорте из Excel в SQL для связи с получателем использовались ADO и поставщик SQLOLEDB. Для подключения к источнику Excel можно воспользоваться поставщиком данных OLE DB для Jet 4.0.

Используя в выражениях SQL особые конструкции, имеющие три различных формата, база данных Jet может ссылаться на внешние базы данных:

• [Полный путь к базе данных Microsoft Access].[Название таблицы]
• [Название ISAM;Строка подключения ISAM].[Название таблицы]
• [ODBC;Строка подключения ODBС].[Название таблицы]

В этом разделе для создания подключения ODBC к базе данных SQL Server используется третий формат. Может использоваться имя источника данных (DSN) ODBC или строка подключения без определения DSN:

DSN:

[odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

DSN-less:

[odbc;Driver={SQL Server};Server=<server>;Database=<database>;

UID=<user>;PWD=<password>]

Для работы следующего примера программы на Visual Basic 6.0 требуется добавление ссылки на проект в ADO. В примере показан импорт данных Excel в SQL Server через подключение ADO с использованием поставщика данных Jet 4.0.

Dim cn As ADODB.Connection

Dim strSQL As String

Dim lngRecsAff As Long

Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\test\xltestt.xls;" & _

"Extended Properties=Excel 8.0"

'Import by using Jet Provider.

strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _

"Server=<server>;Database=<database>;" & _

"UID=<user>;PWD=<password>].XLImport9 " & _

"FROM [Customers$]"

Debug.Print strSQL

cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

Debug.Print "Records affected: " & lngRecsAff

cn.Close

Set cn = Nothing

Для импорта данных Excel в другие базы данных Microsoft Access, базы данных индексно-последовательного метода доступа (ISAM) или базы ODBC также могут использоваться конструкции, поддерживаемые поставщиком данных Jet.

Устранение неполадок

• Помните, что объекты Excel, имена которых содержат знак доллара ($), являются листами (например, Лист1$), другие объекты являются именованными диапазонами Excel.
• В некоторых случаях, особенно когда вы назначаете исходные данные Excel с помощью названия таблицы, а не запроса SELECT, столбцы в целевой таблице SQL Server перегруппируются в алфавитном порядке.Дополнительную информацию о данной проблеме с Jet Provider см. в следующей статье Базы знаний Майкрософт:

2 99484 ПРОБЛЕМА: При использовании ADOX для получения столбцов таблицы Access столбцы упорядочиваются по алфавиту

• Когда Jet Provider определяет, что столбец Excel содержит смешанные текстовые и числовые данные, он выбирает тип данных «большинства», а не совпавшие с ним значения возвращает в виде NULL-значений.Дополнительную информацию по поиску обходного решения данной проблемы см. в следующей статье Базы знаний Майкрософт:

1 94124 ПРОБЛЕМА: При использовании DAO OpenRecordset данным в Excel присваивается значение NULL