Источник 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 (маленький) Будьте в курсе новых возможностей cлужб Integration Services

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


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