В настоящее время вы работаете в автономном режиме; ожидается повторное подключение к Интернету

Импорт данных из Excel в SQL Server

Аннотация
Это пошаговое руководство описывает различные способы импорта данных из листов 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.Для получения дополнительных сведений щелкните приведенный ниже номер статьи базы знаний Майкрософт:
306397 ИНСТРУКЦИИ: Использование 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 щелкните следующий номер статьи базы знаний Майкрософт:
    299484 PRB: При использовании ADOX для получения столбцов таблицы Access столбцы упорядочиваются по алфавиту
  • Обнаружив, что в столбцах Excel содержатся числовые и текстовые данные, поставщик данных Jet выбирает «доминирующий» тип данных и возвращает NULL вместо значений другого типа.Для получения дополнительных сведений об обходе этой проблемы щелкните следующий номер статьи базы знаний Майкрософт:
    194124 PRB: При использовании DAO OpenRecordset данным в Excel присваивается значение NULL
К началу статьи
Ссылки
Для получения дополнительных сведений об использовании Excel в качестве источника данных щелкните следующий номер статьи базы знаний Майкрософт:
257819 ИНСТРУКЦИИ: Использование ADO с данными Excel из Visual Basic или VBA
Для получения дополнительных сведений о передаче данных в Excel щелкните следующие номера статей базы знаний Майкрософт:
295646 ИНСТРУКЦИИ: Передача данных из источника данных ADO в Excel с помощью ADO
247412 СВЕДЕНИЯ: Способы передачи данных из Visual Basic в Excel
246335 ИНСТРУКЦИИ: Автоматическая передача данных из набора записей ADO в Excel
319951 ИНСТРУКЦИИ: Передача данных в Excel с помощью служб DTS SQL Server
306125 ИНСТРУКЦИИ: Импорт данных из SQL Server в Microsoft Excel
К началу статьи
excel sql ado dts sqloledb jet
Свойства

Номер статьи: 321686 — последний просмотр: 03/08/2014 16:41:00 — редакция: 3.0

Microsoft Excel 2000 Standard Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 64-bit Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft Excel 2002 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbhowtomaster kbjet KB321686
Отзывы и предложения