Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
mih.dim1
Member

Откуда:
Сообщений: 28
Кто нибудь знает где найти полное описание параметров в Extended Properties для загрузки файла Excel 2007 или выше?
Нашел следующие:
HDR
ReadOnly
FirstRowHasNames
MaxScanRows
IMEX

А вообще нужно найти параметр, указав который из столбца в файле *.xlsx будет браться реальное число, а не отображаемое. Т.е. в столбце дробные числа например 10.34 или 100.12, а формат ячеек этого столбца стоит 0 знаков после запятой и отображается в Excel 10 и 100 соответственно. И при считывании в MS SQL 2012 тоже идет числа 10 и 100 вместо реальных. Вообщем чтобы при загрузке игнорировал формат ячеек.
Исправить сам файл нет возможности, да и самих файлов более 1000 ежемесячно.
19 мар 19, 14:46    [21837390]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
mih.dim1
Member

Откуда:
Сообщений: 28
Что никто ничего не знает? АУ ребята эксперты вы где?
25 мар 19, 16:38    [21842969]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7762
mih.dim1,
в этом форуме не рассматриваются вопросы настройки OLEDB провайдеров.
25 мар 19, 16:51    [21842977]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
mih.dim1
Member

Откуда:
Сообщений: 28
Владислав Колосов
mih.dim1,
в этом форуме не рассматриваются вопросы настройки OLEDB провайдеров.


Написал чтобы что-то написать?
На этом форуме куча вопросов по ACE.OLEDB, т.к. он очень часто используется для загрузки данных из Excel в MS SQL.
А у меня с этой загрузкой проблемы. Вот я и ищу настройку, которая бы игнорировала формат ячейки/столбца в Excel - всегда загружалось бы как строка например.
26 мар 19, 08:35    [21843394]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
mih.dim1,
При сложных импортах никто не пользуется Linked Servers или OPENQUERY - такие вещи гораздо лучше делать в SSIS, который специально для этого и предназначен.
26 мар 19, 10:46    [21843489]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
mih.dim1
А у меня с этой загрузкой проблемы. Вот я и ищу настройку, которая бы игнорировала формат ячейки/столбца в Excel - всегда загружалось бы как строка например.
Не слышал про такое. Драйверы кривые, функциональность заморожена в середине 90-х, и микрософтом не развиваются.
Лучше действительно воспользоваться SSIS
Другие варианты - готовить файл специально для импорта, или искать драйверы сторонних производителей, или читать данные из файлов самому.
26 мар 19, 11:00    [21843506]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7762
mih.dim1,

то есть вы нашли на форуме описание настроек параметров? Несмотря на "куча вопросов по ACE.OLEDB" я не думаю, что Вы это здесь найдёте. Основной инструмент загрузки данных их Excel не ACE.OLEDB, а Integration Services, в особо запущенных случаях применяют VBA для подготовки выгрузки, например, в CSV и последующем использовании или той же SSIS или утилиты BCP для загрузки CSV.
26 мар 19, 11:31    [21843540]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
mih.dim1, imex не то?
+

Standard alternative
Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.

OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

"SELECT * FROM [Sheet1$a5:d]", start picking the data as of row 5 and up to column D.

Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.
26 мар 19, 11:40    [21843552]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Владислав Колосов
в особо запущенных случаях применяют VBA для подготовки выгрузки
Имеется в виду VBA, который в Excel?
26 мар 19, 13:23    [21843684]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
buser
"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.
О, спасибо, буду знать, думал, нет такого.
26 мар 19, 13:25    [21843685]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
Ferdipux
Member

Откуда: Москва
Сообщений: 553
alexeyvg
О, спасибо, буду знать, думал, нет такого.

Аккуратно с этим. Из MS KB 194124
MS KB 194124
NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the
registry setting ImportMixedTypes=Text will be noticed. This forces mixed
data to be converted to text. For this to work reliably, you may also have to
modify the registry setting, TypeGuessRows=8. The ISAM driver by default
looks at the first eight rows and from that sampling determines the datatype.
If this eight row sampling is all numeric, then setting IMEX=1 will not
convert the default datatype to Text; it will remain numeric.

You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT
mode, so the results may be unpredictable if you try to do appends or updates
of data in this mode.

The possible settings of IMEX are:

0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)

То есть если все значения - int, то приедет int. Но - если есть строчные заголовки и отключить режим заголовков - драйвер может посчитать что в столбце есть String и int и привести все к String. Но -- лучше тестировать, иногда это работает странно.
26 мар 19, 14:56    [21843841]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
mih.dim1
Member

Откуда:
Сообщений: 28
[quot buser]mih.dim1, imex не то?
Это не работает, так как мне надо. Он не игнорирует формат. Проверено
26 мар 19, 17:07    [21844042]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
mih.dim1
Member

Откуда:
Сообщений: 28
Руслан Дамирович
mih.dim1,
При сложных импортах никто не пользуется Linked Servers или OPENQUERY - такие вещи гораздо лучше делать в SSIS, который специально для этого и предназначен.


У нас простые импорты. Есть файлы одинаковой структуры, я их считываю последовательно и потом обрабатываю. А Excel меня подставляет вот такими фишками, потому что я не контролирую форматы в самом файле и пользователи могут отформатировать Excel как им удобно для отображения, не меняя при этом структуру данных.

по поводу SSIS: если есть ссылка на пример загрузки кучу Excel файлов через него, то поделитесь, плиз. Я с ним не работал.
26 мар 19, 17:11    [21844050]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Ferdipux
То есть если все значения - int, то приедет int. Но - если есть строчные заголовки и отключить режим заголовков - драйвер может посчитать что в столбце есть String и int и привести все к String
Понятно, в тексте выше однозначно обещают текст, но в документации денйствительно пишут, что только для смешанных колонок, причём смешанность определяется некорректно, как мы знаем....
26 мар 19, 17:15    [21844058]     Ответить | Цитировать Сообщить модератору
 Re: У кого есть описание загрузка файла *.xlsx: Microsoft.ACE.OLEDB.12.0 Extended Properties?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
Альтернатива:
.NET приложение с использованием бесплатной библиотеки для чтения Excel файлов EPPlus
26 мар 19, 17:35    [21844093]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить