Источник Excel

Изменения: 15 сентября 2007 г.

Источник Excel извлекает данные из листов или диапазонов в книгах Microsoft Excel.

Примечание.
На 64-разрядном компьютере пакеты, которые соединяются с источниками данных Microsoft Excel, должны запускаться в 32-разрядном режиме. Поставщик OLE DB для Microsoft Jet, соединяющийся с источниками данных Excel, доступен только в 32-разрядной версии.

Примечание.
Чтобы загрузить данные из источника данных, который использует Microsoft Office Excel 2007, используйте источник данных OLE DB. Для подключения к источнику данных «Excel 2007» невозможно использовать источник «Excel». Дополнительные сведения см. в разделе Диспетчер соединений с Excel.

Источник Excel предоставляет четыре различных режима доступа к данным для их извлечения:

• Таблица или представление.
• Таблица или представление, указанные в переменной.
• Результат выполнения инструкции SQL. Может использоваться параметризованный запрос.
• Результат выполнения инструкции SQL хранится в переменной.

Важно!

Лист или диапазон в Excel эквивалентны таблице или представлению. Список доступных таблиц в редакторах источников и целевых объектов Excel отображает существующие листы (идентифицируются как имя листа с добавленным знаком $, например «Лист1$») и именованные диапазоны (идентифицируются отсутствием знака $, например «Мой_диапазон»). Дополнительные сведения см. в разделе, посвященном вопросам применения.

Источник Excel для подключения к источнику данных использует диспетчер соединений Excel, и диспетчер соединений определяет исходный файл книги. Дополнительные сведения см. в разделе Диспетчер соединений с Excel.

Источник Excel имеет один обычный выход и один выход ошибок.

Особенности использования

Диспетчер соединений Excel использует поставщик Microsoft OLE DB для Jet 4.0, который поддерживает драйвер Excel ISAM (индексно-последовательный метод доступа) для соединения, считывания и записи данных в источники данных Excel.

Многие существующие статьи баз знаний Майкрософт документируют поведение этого поставщика данных и драйвера, и хотя эти статьи не затрагивают службы Integration Services или их предшественников службы DTS, необходимо знать о некоторых моментах поведения, которые могут привести к непредвиденным результатам. Общие сведения об использовании и поведении драйвера Excel см. в статье по использованию ADO с данными Excel из Visual Basic или VBA.

Следующие особенности поведения поставщика данных Jet в сочетании с драйвером Excel при считывании данных из источника данных Excel могут привести к непредвиденным результатам.

• Источники данных. В качестве источника данных в книге Excel может быть указано имя листа, к которому должен быть добавлен знак $ (например, «Лист1$»), или именованного диапазона (например, «Мой_диапазон»). В инструкции SQL имя листа должно быть окружено ограничителями (например, [Лист1$]), чтобы избежать синтаксической ошибки, вызванной знаком $. Построитель запросов автоматически добавляет эти ограничители. Когда указан лист или диапазон, драйвер считывает непрерывный блок ячеек, начиная с первой непустой ячейки в верхнем левом углу листа или диапазона. Поэтому не должно быть пустых строк в исходных данных или пустой строки между заголовком или строками заголовка и строками данных.
• Потерянные значения. Драйвер Excel считывает определенное количество строк (по умолчанию 8 строк) в указанный источник для определения типа данных каждого столбца. Если столбец содержит смешанные типы данных, особенно если числовые данные смешаны с текстовыми данными, драйвер принимает решение в пользу того типа данных, которого больше, и возвращает значения NULL в ячейки, содержащие данные другого типа. (В случае равенства преимущество получает числовой тип.) Большинство параметров форматирования ячеек в листе Excel не затрагивает это определение типа данных. Можно изменить поведение драйвера Excel, указав режим импорта. Чтобы указать режим импорта, добавьте IMEX=1 к значению расширенных свойств в строке соединения диспетчера соединений с Excel в окне Свойства. Дополнительные сведения см. в статье PRB: возврат значений NULL из Excel при использовании DAO.
• Усеченный текст. Когда драйвер определяет, что столбец Excel содержит текстовые данные, он выбирает тип данных (строковый или memo) на основании самого длинного значения. Если драйвер не обнаруживает значений длиннее 255 символов в выбираемых строках, он считает, что столбец является строковым с длиной 255 символов, а не столбцом типа memo. Поэтому значения длиннее 255 символов могут быть усечены. Чтобы импортировать данные из столбца типа memo без усечения, необходимо убедиться, что столбец memo по крайней мере в одной из выбранных строк содержит значение длиннее 255 символов, либо нужно увеличить число строк, выбираемых драйвером, чтобы включить в выборку такую строку. Увеличить количество строк, включаемых в выборку, можно путем увеличения значения TypeGuessRows в разделе реестра HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel. Дополнительные сведения см. в статье PRB: передача данных от источника Jet 4.0 OLEDB заканчивается сбоем с ошибками.
• Типы данных. Драйвер Excel распознает только ограниченный набор типов данных. Например, все числовые столбцы интерпретируются как двухбайтовые (DT_R8), а строковые столбцы (не столбцы типа memo) интерпретируются как строки в Юникоде длиной в 255 символов (DT_WSTR). Службы Integration Services сопоставляют типы данных Excel следующим образом:
  • Числовой — с плавающей запятой двойной точности (DT_R8)
  • Денежный — денежный (DT_CY)
  • Логический — логический (DT_BOOL)
  • Дата/время — дата (DT_DATE)
  • Строковый — строка в Юникоде длиной в 255 символов (DT_WSTR)
  • Memo — текстовый поток в Юникоде (DT_NTEXT)
• Преобразование типов данных и длины. Службы Integration Services не выполняют неявное преобразование типов данных. В результате, возможно, потребуется использовать преобразования «Производный столбец» или «Преобразование данных» для явного преобразования данных Excel до их загрузки в назначение, отличное от Excel, либо преобразовать данные, отличные от Excel, до их загрузки в назначение Excel. В этом случае может оказаться полезным создать исходный пакет с помощью мастера импорта и экспорта, который сам настроит необходимые преобразования. Ниже приведены некоторые примеры преобразований, которые могут потребоваться.
  • Преобразование между строковыми столбцами Excel в Юникоде и строковыми столбцами не в Юникоде с заданными кодовыми страницами.
  • Преобразование между строковыми столбцами Excel длиной в 255 символов и строковыми столбцами другой длины.
  • Преобразование между числовыми столбцами Excel с плавающей запятой двойной точности и числовыми столбцами других типов.

Настройка источника Excel

Свойства задаются через конструктор служб SSIS или программно.

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

• Редактор источника Excel (страница «Диспетчер соединений»)
• Редактор источника Excel (страница «Столбцы»)
• Редактор источника Excel (страница «Вывод ошибок»)

Диалоговое окно Расширенный редактор содержит все свойства, которые могут устанавливаться программными средствами. Дополнительные сведения о свойствах, которые можно задать в диалоговом окне Расширенный редактор или программно, см. в следующих разделах:

• Common Properties
• Source Custom Properties

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

• Как сопоставить параметры запросов с переменными в компонентах потока данных
• Как установить свойства компонента потока данных при помощи редактора компонентов
• Как установить свойства компонента потока данных в окне «Свойства»
• Как задать свойства компонента потока данных с использованием расширенного редактора
• Как установить атрибуты сортировки на выходе

Дополнительные сведения о переходе между файлами в группе файлов Excel см. в разделе Как выполнить цикл по файлам Excel и таблицам.

См. также

Задачи

Как выполнить цикл по файлам Excel и таблицам

Основные понятия

Назначение Excel
Переменные служб Integration Services
Создание потока данных пакета
Использование служб Integration Services на 64-разрядных компьютерах

Другие ресурсы

Источники служб Integration Services
Working with Excel Files with the Script Task

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений

Версия	Журнал
15 сентября 2007 г.	Добавления Добавлено примечание о запуске на 64-разрядном компьютере пакетов, соединяющихся с источниками данных Excel.
12 декабря 2006 г.	Добавления Добавлено примечание об использовании источников данных Excel 2007.
5 декабря 2005 г.	Измененное содержимое Расширена и изменена большая часть раздела «Особенности использования» для разъяснения известных проблем.