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

Импорт данных из 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
Отзывы и предложения
ript" src="https://c.microsoft.com/ms.js" '="">