Аннотация
Описание приема
В примерах, приведенных в данной статье, импорт данных 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.Для получения дополнительных сведений щелкните указанный ниже номер статьи базы знаний Майкрософт:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
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С].[Название таблицы]
DSN:
[odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]
DSN-less:
[odbc;Driver={SQL Server};Server=<server>;Database=<database>;
UID=<user>;PWD=<password>]
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, имена которых содержат знак доллара ($), являются листами (например, Лист1$), другие объекты являются именованными диапазонами Excel.
- В некоторых случаях, особенно когда вы
назначаете исходные данные Excel с помощью названия таблицы, а не
запроса SELECT, столбцы в целевой таблице SQL Server перегруппируются в
алфавитном порядке.Дополнительную информацию о данной проблеме с Jet Provider см. в следующей статье Базы знаний Майкрософт:299484 ПРОБЛЕМА: При использовании ADOX для получения столбцов таблицы Access столбцы упорядочиваются по алфавиту
- Когда
Jet Provider определяет, что столбец Excel содержит смешанные текстовые
и числовые данные, он выбирает тип данных «большинства», а не совпавшие
с ним значения возвращает в виде NULL-значений.Дополнительную информацию по поиску обходного решения данной проблемы см. в следующей статье Базы знаний Майкрософт:194124 ПРОБЛЕМА: При использовании DAO OpenRecordset данным в Excel присваивается значение NULL