Проблема с типом данных при создании файла Excel с использованием ACE OLEDB Provider

Question

Я использую провайдера ACE OLE DB и задачу "Выполнение SQL" для создания файла Excel. Строка подключения, которую я использую, выглядит следующим образом:

Data Source=C:\MyFolder\MyFile.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties="EXCEL 12.0 XML;HDR=YES";

и команда SQL такая:

CREATE TABLE [Query] (
[ProductAssemblyID] Numeric,
[ComponentID] Numeric,
[ComponentDesc] String,
[TotalQuantity] Numeric,
[StandardCost] Currency,
[ListPrice] Currency,
[BOMLevel] Numeric,
[RecursionLevel] Numeric)

Затем я использую пункт назначения Excel для заполнения файла. Когда я открываю файл результата, все столбцы являются текстовыми столбцами, а числа сохраняются в виде текста. Где я делаю не так?

Спасибо.

Answer 1

Вот как я обычно делаю такую ​​задачу, как ваша:

1. Создайте файл Excel с нужными вам столбцами.
2. Добавить соединение данных Excel в новый файл
3. Создать поток данных
4. Добавьте источник OLE DB и SELECT ... FROM ...
5. Добавить пункт назначения Excel и установить подключение к Excel
6. Сопоставьте столбцы по мере необходимости
7. В расширенном редакторе места назначения Excel установите типы данных столбцов

Answer 2

Я боролся с этим несколько дней. Вот что я обнаружил: с расширенными свойствами по умолчанию (EXCEL 12.0 XML; HDR=YES) НИ ОДИН из этих поставщиков не работает (т.е. они создают текстовые поля вместо чисел, дат и т. Д.):

• Microsoft.ACE.OLEDB.12.0 (Microsoft Excel 2007-2010)
• Microsoft.ACE.OLEDB.15.0 (Microsoft Excel 2013)
• Microsoft.ACE.OLEDB.16.0 (Microsoft Excel 2016)

Как упоминал @lucazav, выбор более старого поставщика Microsoft.Jet.OLEDB.4.0 (если он у вас есть) действительно работает. Это то, что используется, если вы выбираете «Excel 97-2003». Однако, если вам нужно выполнить развертывание на производственной машине или у вас нет старого драйвера JET, вы можете обнаружить, что он больше не доступен, не работает на 64-битных машинах и т. Д.

Но вам НЕ НУЖНО устанавливать старый драйвер JET. Мне удалось заставить ACE.OLEDB.12.0 работать, просто изменив расширенные свойства.

Изменять

      Excel 12.0 XML;HDR=YES

к

      Excel 8.0;HDR=YES

Кроме того, вы захотите установить расширение имени файла на .xls вместо .xlsx. В противном случае, когда вы откроете файл в Excel, он будет жаловаться на то, что расширение не соответствует фактическому типу файла.

HDR не является обязательным, но мне нужны заголовки, поэтому я использую его. Я не думаю, что корпус «Excel» имеет значение.

Об этом решении я узнал из инструкции по установке на странице загрузки Microsoft для распространяемого пакета Microsoft Access Database Engine 2010 (именно так вы получаете поставщик Microsoft.ACE.OLEDB.12.0).

«Excel 8.0» также является расширенными свойствами по умолчанию при использовании JET.