NC’s Blog

Just another weblog for students, IT people and programmers

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

Microsoft Office Exel уже довольно давно стал ключевой программой при переносе разных баз, тк практически любая даже очень старая база работает с Exel и перевести в него данный не состовляет ни каких проблем. Это пошаговое руководство описывает различные способы импорта данных из листов Microsoft Excel в базы данных Microsoft SQL Server.

Требования перед началом

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

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

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

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

Описание

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

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

Примеры

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 (http://support.microsoft.com/kb/306397/RU/ )

Следующая программа импортирует данные из рабочего листа «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.

on Visual Basic

←**
    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:
   [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 (http://support.microsoft.com/kb/299484/RU/ )
  • Обнаружив, что в столбцах Excel содержатся числовые и текстовые данные, поставщик данных Jet выбирает «доминирующий» тип данных и возвращает NULL вместо значений другого типа.Для получения дополнительных сведений об обходе этой проблемы щелкните следующий номер статьи базы знаний Майкрософт:
    194124 (http://support.microsoft.com/kb/194124/RU/ )

—————————————————-

Полезные ссылки:

306397 (http://support.microsoft.com/kb/306397/RU/ )

Использование Excel со связанными серверами SQL Server и распределенными запросами

299484 (http://support.microsoft.com/kb/299484/RU/ )

При использовании ADOX для получения столбцов таблицы Access столбцы упорядочиваются по алфавиту

194124 (http://support.microsoft.com/kb/194124/RU/ )

При использовании DAO OpenRecordset данным в Excel присваивается значение NULL

Май 6, 2009 - Posted by | Администрирование и Настройка | , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

12 комментариев »

  1. С большим удовольствием читаю. Продолжайте писать обязательно. Андрей, Татарстан.

    комментарий от Balashka | Май 31, 2009 | Ответить

    • Думаю что правильно вы сделали что разместили этот материал

      комментарий от musicnubru | Сентябрь 3, 2009 | Ответить

  2. Очень понравился Ваш сайт. С удовольствием буду заглядывать.

    комментарий от Алексей | Июнь 1, 2009 | Ответить

  3. Спасибо!!!

    комментарий от AssemyWerie | Июнь 10, 2009 | Ответить

  4. Любое искусство, особенно не совсем традиционное, всегда вызывало ожесточенные споры. Думаю, оно просто имеет право на свое существование, вот и всё!

    комментарий от tapakah | Июнь 18, 2009 | Ответить

  5. Класс!!! Круто!!!

    комментарий от Septasype | Июнь 26, 2009 | Ответить

  6. Молодец! Поддерживаю!🙂

    комментарий от Гарри | Июнь 27, 2009 | Ответить

  7. Информация полезная. Спасибо. Хотелось бы только обновлений почаще

    комментарий от Сергей Александров | Июль 9, 2009 | Ответить

  8. Автор, посты , конечно, очень интересные. Но вы не размышляли поменять дизайн?

    комментарий от Bacek | Июль 15, 2009 | Ответить

  9. Понравилась статья. Буду ждать продолжения. Эта тема конечно же интересна всем.

    комментарий от диcтpoфик | Июль 15, 2009 | Ответить

  10. Соглашусь с автором. Многое из вышеперечисленного действительно актуально

    комментарий от UnodoFot | Август 4, 2009 | Ответить

  11. Все замечательно: и по стилю изложения, и по содержимому. Так держать!

    комментарий от CTИЛЯГA | Август 21, 2009 | Ответить


Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

%d такие блоггеры, как: