Поделиться через

  • Статья

Источник Excel

Источник Excel извлекает данные с листов или из диапазонов книг Microsoft 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: Значения Excel, возвращенные как NULL, при использовании DAO OpenRecordset.

  • Усеченный текст. Когда драйвер определяет, что столбец 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 распознает только ограниченный набор типов данных. Например, все числовые столбцы воспринимаются как тип double (DT_R8), а все символьные столбцы (кроме столбцов типа memo) воспринимаются как строки в Юникоде длиной 255 символов (DT_WSTR). Службы Integration Services сопоставляют типы данных Excel следующим образом.

    • Числовой — с плавающей запятой двойной точности (DT_R8)

    • Денежный — денежный (DT_CY)

    • Логический — логический (DT_BOOL)

    • Дата/время — datetime (DT_DATE)

    • Строковый — строка в Юникоде длиной в 255 символов (DT_WSTR)

    • Memo — текстовый поток в Юникоде (DT_NTEXT)

  • Преобразование типов данных и длины. Службы Integration Services не выполняют неявное преобразование типов данных. В результате, возможно, потребуется использовать преобразования «Производный столбец» или «Преобразование данных» для явного преобразования данных Excel до их загрузки в назначение, отличное от Excel, либо преобразовать данные, отличные от Excel, до их загрузки в назначение Excel. В этом случае может оказаться полезным создать исходный пакет с помощью мастера импорта и экспорта, который сам настроит необходимые преобразования. Ниже приведены некоторые примеры преобразований, которые могут потребоваться.

    • Преобразование между строковыми столбцами Excel в Юникоде и строковыми столбцами не в Юникоде с заданными кодовыми страницами.

    • Преобразование между строковыми столбцами Excel длиной в 255 символов и строковыми столбцами другой длины.

    • Преобразование между числовыми столбцами Excel с плавающей запятой двойной точности и числовыми столбцами других типов.

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

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

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

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

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

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

Внешние ресурсы

Значок служб Integration Services (маленький) Оставайтесь в курсе новых возможностей служб Integration Services

Новейшую документацию, статьи, образцы и видеоматериалы корпорации Майкрософт, а также лучшие решения участников сообщества см. на странице служб Integration Services на сайте MSDN.


Чтобы получать автоматические уведомления об этих обновлениях, подпишитесь на RSS-каналы, предлагаемые на этой странице.