Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2 3      [все]
 Импорт данных из MS Excel  [new]
Alexander Rudenko
Member

Откуда:
Сообщений: 65
Проблема заключается в том, что JET каким-то образом определятет тип колонки (Double) хотя колонка содержит как числовые значение так и текстовые.

После этого текстовые значения игнорируются и вместо них вставляется NULL.

Как напраить JET в нужное русло?

спасибо.
20 янв 05, 02:20    [1259019]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Вот в FAQ описан способ как преодолеть препятсвие в ограничение экспорта ячеек длинной более 255 символов, но, думаю, что предложенный способ решения может помочь в решении и этой проблеммы. Правда, иногда и это не сработает, т.к. приводит лишь только к тому, что будут анализироваться не первые 8 строк, а 16384, что в свою очередь меньше максимального числа строк на листе экселя (65536).
20 янв 05, 07:06    [1259105]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Alexander Rudenko
Member

Откуда:
Сообщений: 65
Мне что-то это не помогло.
Как будто ничего и не делалось
25 янв 05, 16:57    [1272502]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
StalkerS
Member

Откуда: Nowhere
Сообщений: 1343
да, хреновый косяк, я когда первый раз с ним столкнулся, просто о..ел. Повезло еще, что вовремя заметил !

При трансформации, формат данных определяется по первым нескольким строчкам, и если там числа - то вместо текста снизу окажется null, и
соответственно наоборот.

Как нормально с этим бороться не понятно, но я вышел следующим образом :

Ко всем ячейкам, которые содержат цифры, добавь слева символ одинарной кавычки ', это можно сделать элементарным макросом в Excell. Теперь
ексел будет думать, что это текст, и поставит в верхнем левом углу каждой такой ячейки зеленый треугольник. И при переносе в mssql проблем не будет
25 янв 05, 19:18    [1273036]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Alexander Rudenko
Member

Откуда:
Сообщений: 65
Мне это не помогло.... :(
25 янв 05, 21:55    [1273218]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Alexander Rudenko
Member

Откуда:
Сообщений: 65
Неужели нельзя отучить JET умничать?

Может ему можно сказать какой именно тип нужно использовать?
25 янв 05, 22:07    [1273226]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Glory
Member

Откуда:
Сообщений: 104760
Alexander Rudenko
Неужели нельзя отучить JET умничать?

Может ему можно сказать какой именно тип нужно использовать?

Если у вас в столбце Excel содержатся данные разных типов, то почему же "виноват" Jet ?
25 янв 05, 23:53    [1273331]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Alexander Rudenko
Member

Откуда:
Сообщений: 65
Glory
Alexander Rudenko
Неужели нельзя отучить JET умничать?

Может ему можно сказать какой именно тип нужно использовать?

Если у вас в столбце Excel содержатся данные разных типов, то почему же "виноват" Jet ?


Я делаю следующие: выбираю целую колонку в Excel'e, устанавливаю ему тип Text

А он там находит видите дли цифры и считает что те записи где есть буквы нужно просто исключить...
26 янв 05, 00:28    [1273361]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Glory
Member

Откуда:
Сообщений: 104760
Я делаю следующие: выбираю целую колонку в Excel'e, устанавливаю ему тип Text
Этим вы лишь меняете _формат отображения_ ячеек.
_Тип данных_ этим НЕ меняется
26 янв 05, 00:31    [1273363]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Alexander Rudenko
Member

Откуда:
Сообщений: 65
Glory
Я делаю следующие: выбираю целую колонку в Excel'e, устанавливаю ему тип Text
Этим вы лишь меняете _формат отображения_ ячеек.
_Тип данных_ этим НЕ меняется


А где он меняется?
26 янв 05, 00:41    [1273370]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Alexey Sh
Member

Откуда: SPB
Сообщений: 1930
Бред какой-то. А если в готовую таблицу импортировать?
26 янв 05, 00:55    [1273378]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
BugsBunny
Member

Откуда: GMT+5=EST
Сообщений: 2414
?

How To Use ADO with Excel Data from Visual Basic or VBA


...
To work around this problem for read-only data, enable Import Mode by using the setting "IMEX=1" in the Extended Properties section of the connection string. This enforces the ImportMixedTypes=Text registry setting. However, note that updates may give unexpected results in this mode. For additional information about this setting, click the article number below to view the article in the Microsoft Knowledge Base:
194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset
...
26 янв 05, 01:03    [1273383]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
AVVS
Member

Откуда:
Сообщений: 335
А какова процедура импорта можно узнать? Т.е. что надо сделать:

(1) просто импортировать лист из Excel'я в отдельную таблицу, а потом из нее данные используются самим сервером или приложением т.е. эта операция выполняется из-под DTS,

или

(2) приложение пытается импортировать данные, но при этом искажается
тип данных столбца?

***
26 янв 05, 10:35    [1273826]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Glory
Member

Откуда:
Сообщений: 104760
Alexander Rudenko
Glory
Я делаю следующие: выбираю целую колонку в Excel'e, устанавливаю ему тип Text
Этим вы лишь меняете _формат отображения_ ячеек.
_Тип данных_ этим НЕ меняется


А где он меняется?

Он меняется в самой ячейке. У символьного типа в первой позиции должен быть `. См. пост StalkerS
26 янв 05, 10:46    [1273876]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Alexander Rudenko
Member

Откуда:
Сообщений: 65
AVVS
А какова процедура импорта можно узнать? Т.е. что надо сделать:

(1) просто импортировать лист из Excel'я в отдельную таблицу, а потом из нее данные используются самим сервером или приложением т.е. эта операция выполняется из-под DTS,

или

(2) приложение пытается импортировать данные, но при этом искажается
тип данных столбца?

***


Через DTS
26 янв 05, 14:06    [1274913]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
AVVS
Member

Откуда:
Сообщений: 335
Alexander Rudenko

Через DTS


Ну так чего уж проще-то?!
В DTS есть специальное окошко, в котором можно указать тип данных,
к которому надо привести каждое импортируемое поле.

DTS сам смотрит на первые 25 строк файла и пытается подставить нужный
тип. Если не устраивает - можно выбрать свой и вперед.

avvs
26 янв 05, 14:21    [1274978]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
StalkerS
Member

Откуда: Nowhere
Сообщений: 1343
Alexander Rudenko

Мне это не помогло.... :(


это почему ? Все работает просто замечательно, надо всего-лишь добавить символ ' ко всем числовым ячейкам. При этом их вид должен стать вот таким :

К сообщению приложен файл. Размер - 0Kb
26 янв 05, 14:56    [1275173]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Like
Member

Откуда: Донецк
Сообщений: 134
StalkerS


это почему ? Все работает просто замечательно, надо всего-лишь добавить символ ' ко всем числовым ячейкам. При этом их вид должен стать вот таким :


Да, у меня так тоже получилось. Только макрос заполнения всех ячеек не получился. :(
26 янв 05, 15:13    [1275264]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
StalkerS
Member

Откуда: Nowhere
Сообщений: 1343
Дарю ;)

Private Sub CommandButton1_Click()
i = 2
Do While Range("a" + CStr(i)) <> ""
Range("a" + CStr(i)).Value = "'" + CStr(Range("a" + CStr(i)).Value)
i = i + 1
Loop
End Sub
26 янв 05, 19:54    [1276449]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Glory
Member

Откуда:
Сообщений: 104760
Like
StalkerS


это почему ? Все работает просто замечательно, надо всего-лишь добавить символ ' ко всем числовым ячейкам. При этом их вид должен стать вот таким :


Да, у меня так тоже получилось. Только макрос заполнения всех ячеек не получился. :(

Проще сохранить Excel файл как текстовый с разделителем
26 янв 05, 20:16    [1276485]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
AVVS
Member

Откуда:
Сообщений: 335
Glory

Проще сохранить Excel файл как текстовый с разделителем


DTS может этого не понять. Если в определенном поле .csv файла лежат
данные, похожие на числа (т.е. только цифры и, может быть, знак)
и так, на протяжении первых 25-ти строк (тут могу ошибаться, может он больше строк смотрит), то DTS по умолчанию будет кастить все значения
этого поля к типу float или int. Если найдет значение текстового типа, то
просто вставит NULL.
Т.к. импорт идет в новую таблицу, то будут работать правила кастинга
типов DTS.
Если импортировать в таблицу, которая уже определена в базе и типы
данных столбцов заданы, то DTS будет ориентироваться на эти типы.

avvs
26 янв 05, 22:22    [1276595]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
BugsBunny
Member

Откуда: GMT+5=EST
Сообщений: 2414
Are you boycotting me?

1. on server HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRow = 0x10000

2. IMEX=1 in extended properties
...OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\Test.xls";User ID=Admin;Password=;Extended properties="Excel 8.0;HDR=No;IMEX=1"')...Sheet1$

and I don't see any problem after that

F1
---
1
2
3
4
5
6
7
8
9
10
a
b
c

(13 row(s) affected)
27 янв 05, 00:13    [1276692]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Glory
Member

Откуда:
Сообщений: 104760
BugsBunny

2. IMEX=1 in extended properties


Thanks BugsBunny
27 янв 05, 00:51    [1276740]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Slav
Member

Откуда: МАсква, великий и прекрасный город
Сообщений: 9047
Тема возникает с завидной постоянностью - например, здесь или здесь.

К сожалению, столкнулся с ней. Прочитал советы:
1. Пройтись по ячейкам - и просто два раза по каждой кликнуть.
2. Пройтись по ячейкам - и проставить апостроф.
3. Идти через csv (или другой промежуточный формат).
4. Корректировать скрипты DTS.
5. Писать свою прогу с использованием неких уникальных опций.
6. ...

Вот сижу и думаю - что же выбрать в конце концов. Почему!? - в визарде DTS я не могу быстро выполнить такую элементарную операцию?

Мне необходимо перебросить в SQL xls файл. Листов - 184. На каждом достаточно много данных. Так что советы - пройтись, поставить апостроф, идти через промежуточный формат и т.д. - не катят. (Давайте оставим вопросы "Кто такие файлы делает!?". Не я. Я просто должен решить проблему).
Данные - не такие уж и разнотипные по сути. Номера счетов - субсчетов. Где могут встречаться и, допустим 10.01 и 26. Это даже не 12345 и abc. Более того - в самом начале ввода (подчеркиваю - не после, а в начале) соответствующие колонки были отформатированы как Текст.

Далее хочу привести 3 картинки. Не торопитесь, пожалуйста, отвечать - пока все три не увидите.
Итак, первая. В 5-ой ячейке стоит 51. При импорте в SQL - на этом месте NULL.

К сообщению приложен файл. Размер - 0Kb
11 мар 05, 16:33    [1379383]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Slav
Member

Откуда: МАсква, великий и прекрасный город
Сообщений: 9047
Другой лист. В 5-ой строке (в предыдущем посте тоже следует читать вместо "ячейка" - строка) - 08.03.
При импорте в SQL - 08.03 в текстовом поле. Замечу сразу - точка у меня в Экселе это "десятичная точка". Но вот почему-то 08.03 DTS берет как текстовую.

К сообщению приложен файл. Размер - 0Kb
11 мар 05, 16:36    [1379403]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Slav
Member

Откуда: МАсква, великий и прекрасный город
Сообщений: 9047
А вот - самое интересное, на мой взгляд. В продолжение предыдущей картинки - там же, но в 10-ой строке стоит 26. И - что характерно - в SQL она передается именно как 26! А не NULL как в первом случае с 51.

Так все-таки - визард DTS следует просто отбросить для задач импорта из Экселя? По причине непредсказуемости результата?

К сообщению приложен файл. Размер - 0Kb
11 мар 05, 16:41    [1379428]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Glory
Member

Откуда:
Сообщений: 104760
2Slav
1. Мне все таки интересует почему Excel-ий диалог с именем "Формат ячейки" воспринмается как "Тип данных ячейки" ?

2. Метод BugsBunny не подходит ?
11 мар 05, 16:55    [1379522]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Slav
Member

Откуда: МАсква, великий и прекрасный город
Сообщений: 9047
Glory
2Slav
1. Мне все таки интересует почему Excel-ий диалог с именем "Формат ячейки" воспринмается как "Тип данных ячейки" ?


Glory,

поверьте - я знаю разницу между форматом ячейки и типом данных, которые в ней лежат. (Кстати уж - в качестве оффтопа - а не подскажете, где в Экселе посмотреть не формат ячейки, а тип данных?)
Я привел картинки именно в заявленную тему - загадки работы DTS при импорте из Экселя. При прочих равных условиях. Почему в первом случае 51 в 5-ой строке - это число (и соответственно NULL в SQL, потому что первые 4 строки это текст) - а во втором случае 08.03 это текст, хотя ведь тоже число? Ведущий ноль говорит, что это именно текст. Но ведь и 51 вводилось в ячейку, предварительно отформатированную как Текст. Почему же оно текстом не стало? (с точки зрения визарда DTS). А вот 26 - в третьем примере - почему-то стало. Ведь 26 передается правильно! Ну чем 26 лучше 51? ;-)

Glory
2. Метод BugsBunny не подходит ?

Может и подходит - пока не проверял. Потому что не могу так с ходу организовать в T-SQL цикл по 184 листам файла xls. Придется идти в Дельфи. Целый огород городить.

Вопрос должен получить ясный ответ: импорт с использованием визарда DTS из Экселя недостоверен и категорически не рекомендуется к использованию? Никакие настройки не делают его работоспособным?

Мне надо было срочно решить задачу. Я это сделать не смог. Пожалуй стоит написать нормальную утилиту - популярность у неё будет бешеная! :-)
11 мар 05, 17:28    [1379704]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Glory
Member

Откуда:
Сообщений: 104760
Я привел картинки именно в заявленную тему - загадки работы DTS при импорте из Экселя. При прочих равных условиях. Почему в первом случае 51 в 5-ой строке - это число (и соответственно NULL в SQL, потому что первые 4 строки это текст) - а во втором случае 08.03 это текст, хотя ведь тоже число? Ведущий ноль говорит, что это именно текст. Но ведь и 51 вводилось в ячейку, предварительно отформатированную как Текст. Почему же оно текстом не стало? (с точки зрения визарда DTS). А вот 26 - в третьем примере - почему-то стало. Ведь 26 передается правильно! Ну чем 26 лучше 51? ;-)
Много раз уже говорилось что MSSQL пытается определить тип столбца по нескольким первым его значениям. И после этого все последующие просто пытается читать как этот тип. Если не удается то ставит NULL.
Поэтому 26 ничем не лучше 51 или 08.03 - вопрос в том совпадает ли тип данных конкретной ячейки с выбранным на данный момент MSSQL-ем типом.
11 мар 05, 17:35    [1379740]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Slav
Member

Откуда: МАсква, великий и прекрасный город
Сообщений: 9047
Glory
Я привел картинки именно в заявленную тему - загадки работы DTS при импорте из Экселя. При прочих равных условиях. Почему в первом случае 51 в 5-ой строке - это число (и соответственно NULL в SQL, потому что первые 4 строки это текст) - а во втором случае 08.03 это текст, хотя ведь тоже число? Ведущий ноль говорит, что это именно текст. Но ведь и 51 вводилось в ячейку, предварительно отформатированную как Текст. Почему же оно текстом не стало? (с точки зрения визарда DTS). А вот 26 - в третьем примере - почему-то стало. Ведь 26 передается правильно! Ну чем 26 лучше 51? ;-)
Много раз уже говорилось что MSSQL пытается определить тип столбца по нескольким первым его значениям. И после этого все последующие просто пытается читать как этот тип. Если не удается то ставит NULL.
Поэтому 26 ничем не лучше 51 или 08.03 - вопрос в том совпадает ли тип данных конкретной ячейки с выбранным на данный момент MSSQL-ем типом.


Glory,

1. "...много раз уже говорилось". Да - по этой проблеме темы открываются с завидной регулярностью. Но ни в одной нет прямого ответа - как решить эту проблему? Не на уровне "танцев с бубном" - а средствами того же DTS. Более того - Ваши ответы стимулируют и впредь открытие подобных тем. ;-)

"...пытается определить тип столбца по нескольким первым его значениям. И после этого все последующие просто пытается читать как этот тип."
Хотелось бы точное описание алгоритма. По скольким значениям? Как происходит выбор в случае 3 и более разных типов в одном столбце? Да и Вы уверены - что по "первым"? Тогда попробуйте такой пример:

Excell         SQL

col1            название колонки
abc             NULL
25.05.2005      38436
123.00          123
Тип получившейся колонки - float. А ведь первое значение - текст.

Изменим немного исходный файл:
Excell         SQL

col1            название колонки
abc             abc
qwe             qwe
25.05.2005      NULL
123.00          NULL
Тип получившейся строки - nvarchar. Допустим. Но в чем проблема - дату и число представить в текстовом виде? Функции а-ля FloatToStr или DateToStr есть во всех языках.

В результате почти недельных мучений (и поисков в сети описания этого самого пресловутого "алгоритма") пришлось написать небольшую утилитку под свой конкретный случай. Рискну поставить точку в этой "вечнозеленой" теме: в MS SQL Server 2000 импорт из Excell элементарно не работает. Гарантировать правильную передачу даже однородных данных, имеющих один и тот же формат ячейки (как в моем случае - 51 и 8.06) нельзя. В случае разнородных данных проблема только усугубляется.

PS "...вопрос в том совпадает ли тип данных конкретной ячейки с выбранным на данный момент MSSQL-ем типом."
Вы упорно разделяете в Excell формат вывода и тип данных. И так же упорно не хотите подсказать - а где этот тип можно посмотреть и изменить? Из каких загадочных тайников MS SQL берет этот самый тип - на который я даже посмотреть не могу? (разве что внешнее представление изменить)
18 мар 05, 14:02    [1397711]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Glory
Member

Откуда:
Сообщений: 104760
1. "...много раз уже говорилось". Да - по этой проблеме темы открываются с завидной регулярностью. Но ни в одной нет прямого ответа - как решить эту проблему? Не на уровне "танцев с бубном" - а средствами того же DTS. Более того - Ваши ответы стимулируют и впредь открытие подобных тем. ;-)
Мои ответы стимулируют ???
Может нежелание других отказатся от Excel как способа импорта/экспорта ?

Хотелось бы точное описание алгоритма. По скольким значениям? Как происходит выбор в случае 3 и более разных типов в одном столбце? Да и Вы уверены - что по "первым"? Тогда попробуйте такой пример:

Приведу еще раз данную выше ссылку от BugsBunny - там все сказано
http://support.microsoft.com/kb/194124/EN-US/


В результате почти недельных мучений (и поисков в сети описания этого самого пресловутого "алгоритма") пришлось написать небольшую утилитку под свой конкретный случай.
По-моему опять же в ссылке приведено решение.
Не говоря о том, что простейшим рещением было бы просто сохранить Excel как текстовый файл с разделителем. И импортировать уж его

Рискну поставить точку в этой "вечнозеленой" теме: в MS SQL Server 2000 импорт из Excell элементарно не работает. Гарантировать правильную передачу даже однородных данных, имеющих один и тот же формат ячейки (как в моем случае - 51 и 8.06) нельзя. В случае разнородных данных проблема только усугубляется.
Работа происходит не напрямую, а через Jet. Таке что _любое ПО_, которое как и MSSQL будет работать через этот драйвер получит теже самые проблемы

Вы упорно разделяете в Excell формат вывода и тип данных. И так же упорно не хотите подсказать - а где этот тип можно посмотреть и изменить? Из каких загадочных тайников MS SQL берет этот самый тип - на который я даже посмотреть не могу? (разве что внешнее представление изменить)
А вас не посещяла мысль что моя скрытность вызывана незнанием ответа на ваш вопрос ?
18 мар 05, 17:53    [1398823]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
AVVS
Member

Откуда:
Сообщений: 335
Slav

Glory,

1. "...много раз уже говорилось". Да - по этой проблеме темы открываются с завидной регулярностью. Но ни в одной нет прямого ответа - как решить эту проблему? Не на уровне "танцев с бубном" - а средствами того же DTS. Более того - Ваши ответы стимулируют и впредь открытие подобных тем. ;-)

"...пытается определить тип столбца по нескольким первым его значениям. И после этого все последующие просто пытается читать как этот тип."
Хотелось бы точное описание алгоритма. По скольким значениям? Как происходит выбор в случае 3 и более разных типов в одном столбце? Да и Вы уверены - что по "первым"? Тогда попробуйте такой пример:



Может быть еще разочек попробуем:

1. DTS'у можно явно указать какой тип данных содержится в каждом импортируемом столбце. Если там, к примеру, замес из текста (varchar) и цифирек (int, float, decimal, money etc.), то вы можете ему сказать:

- импортировать как текст (varchar), будут импортированы ВСЕ значения
- импортировать как данные некоторого типа, тогда данные, которые укалдываются в этот тип будут к нему преобразованы, а все остальные
данные, которые не укладываются, будут приведены к NULL.

На вкладке DTS есть кнопочка [Trasform] - ткнитесь в нее и для каждого стоблца выберите то, что нужно.

2. Где-то читал, но не могу утверждать точно, что DTS пытается определить тип данных столбца по первым 25 строкам (значениям). Если он находит смешение типов данных, то пытается выбрать такой, который позволит импортировать максимально число значений.
т.е. если видит
int + float = выберет float (причем money или numeric не выберет никогда)

int+ text = выберет varchar(255) или nvarchar(255)

ну и т.д. В крайнем случае - всегда выберет varchar, к нему все что угодно привести можно, только тогда парсить импортированные значения придется уже самому.
19 мар 05, 12:33    [1399571]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Glory
Member

Откуда:
Сообщений: 104760
1. DTS'у можно явно указать какой тип данных содержится в каждом импортируемом столбце. Если там, к примеру, замес из текста (varchar) и цифирек (int, float, decimal, money etc.), то вы можете ему сказать:
Может там можно указать все таки тип данных в _приемнике_? А не в источнике как вы утверждаете. А за тип данных _источника_ отвечает драйвер источника.

2. Где-то читал, но не могу утверждать точно, что DTS пытается определить тип данных столбца по первым 25 строкам (значениям). Если он находит смешение типов данных, то пытается выбрать такой, который позволит импортировать максимально число значений.
Цитирую ссылку

"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. "
19 мар 05, 13:11    [1399604]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Slav
Member

Откуда: МАсква, великий и прекрасный город
Сообщений: 9047
AVVS

Может быть еще разочек попробуем:

1. DTS'у можно явно указать какой тип данных содержится в каждом импортируемом столбце. Если там, к примеру, замес из текста (varchar) и цифирек (int, float, decimal, money etc.), то вы можете ему сказать:

- импортировать как текст (varchar), будут импортированы ВСЕ значения
- импортировать как данные некоторого типа, тогда данные, которые укалдываются в этот тип будут к нему преобразованы, а все остальные
данные, которые не укладываются, будут приведены к NULL.

На вкладке DTS есть кнопочка [Trasform] - ткнитесь в нее и для каждого стоблца выберите то, что нужно.


AVVS,

тыкался неоднократно. А Вы сами-то пробовали? Рекомендую - говорят, смех продлевает жизнь. ;-)
К началу импорта столбец определяется как float. Я его меняю на nvarchar 50. И действительно - в результирующей таблице столбец имеет текстовый тип! Только - судя по всему - все преобразования (о которых никто не просил) уже произведены:

Excel           MS SQL 
col1            название столбца (тип - nvarchar 50)
01.01.2005      38353
02.01.2005      38354
abc              NULL
qwe             NULL
589.00          589
367.00          367

Так что там совсем не "...какой тип данных содержится в каждом импортируемом столбце." А какой тип вы хотели бы видеть - но проводить или не проводить преобразования (и как их проводить) вас никто не спрашивает.


AVVS

2. Где-то читал, но не могу утверждать точно, что DTS пытается определить тип данных столбца по первым 25 строкам (значениям). Если он находит смешение типов данных, то пытается выбрать такой, который позволит импортировать максимально число значений.
т.е. если видит
int + float = выберет float (причем money или numeric не выберет никогда)

int+ text = выберет varchar(255) или nvarchar(255)

ну и т.д. В крайнем случае - всегда выберет varchar, к нему все что угодно привести можно, только тогда парсить импортированные значения придется уже самому.

Посмотрите еще раз пример, который я привел в этом посте. "В крайнем случае" был выбран float - самый, на мой взгляд, неудачный (и что интересно - стоящий даже не в начале, а в конце) формат для этого набора данных. Вот я и недоумеваю - почему?
21 мар 05, 17:37    [1403076]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Slav
Member

Откуда: МАсква, великий и прекрасный город
Сообщений: 9047
Glory

...
Может нежелание других отказатся от Excel как способа импорта/экспорта ?
...
Не говоря о том, что простейшим рещением было бы просто сохранить Excel как текстовый файл с разделителем. И импортировать уж его.


Glory,

предлагаю все-таки не смешивать саму проблему и пути её решения. Импорт из Excel не работает. Хорошо - работает, мягко говоря, странно. Проблема налицо. Обойти её, можно, например, через текстовый файл. Правда запоминать таким образом можно только по листам - а, если Вы помните, в моем случае их без малого 200 штук. Как раз на день работы. Не говоря уже о том, что через неделю мне придется опять делать тоже самое... Можно еще копи-паст приспособить - но сама проблема-то останется.

Glory

Приведу еще раз данную выше ссылку от BugsBunny - там все сказано
http://support.microsoft.com/kb/194124/EN-US/
...
По-моему опять же в ссылке приведено решение.
...

Да - действительно IMEX=1 частично решает проблему. Но возникает другая - при наличии "фирменного" DTS приходится писать свою собственную утилиту. Со всеми вытекающими...
Но эта опция тесно увязана - не очень прозрачным способом - с TypeGuessRows. На разных машинах может быть совершенно разный результат. Или на одной машине - при разных конкретных наборах. И логика эта для меня по прежнему загадка.
21 мар 05, 18:01    [1403168]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Glory
Member

Откуда:
Сообщений: 104760
Импорт из Excel не работает. Хорошо - работает, мягко говоря, странно. Проблема налицо.
Это проблема ваша. Excel - это не то ПО, которое нужно использовать в качестве интерфейса. Потому что заставляет вас решать задачу по контролю вводимой информации не ДО а ПОСЛЕ ее ввода.

Но возникает другая - при наличии "фирменного" DTS приходится писать свою собственную утилиту. Со всеми вытекающими...
DTS как MSSQL НЕ работает с Excel файлами напрямую. Он использует Jet.OLEDB. Поэтому проблема ваша к DTS-у притянута за уши. Ибо DTS ничего сверх возможностей самого драйвера вам предложить не сможет

Правда запоминать таким образом можно только по листам - а, если Вы помните, в моем случае их без малого 200 штук. Как раз на день работы.
Вы имете ввиду записывать каждый лист в файл ВРУЧНУЮ ??? Однако.

Да - действительно IMEX=1 частично решает проблему. Но возникает другая - при наличии "фирменного" DTS приходится писать свою собственную утилиту. Со всеми вытекающими...
Если IMEX=1 решает проблему то какую еще утилиту нужно писать ?
21 мар 05, 18:18    [1403229]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
27 понуро бредущих кроликов
Member

Откуда:
Сообщений: 262
Slav
Импорт из Excel не работает. Хорошо - работает, мягко говоря, странно.


Странно...Я из EXCEL-я через DTS в ORACLE сливаю - и все нормально работает,в оракловаых таблицах все поля varchar2, в DTS Transformation - CopyColumn.

Перед этим еще Active-X Script-ом Names-ы в хls прописываю, из низ данные брать удобнее, чем прямо из Sheet-a, если в workBook несколько разных таблиц на страницу положено...
21 мар 05, 19:20    [1403398]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
AVVS
Member

Откуда:
Сообщений: 335
Slav

Посмотрите еще раз пример, который я привел в этом посте. "В крайнем случае" был выбран float - самый, на мой взгляд, неудачный (и что интересно - стоящий даже не в начале, а в конце) формат для этого набора данных. Вот я и недоумеваю - почему?


Мдя :( Натурный эксперимент с приведенными данными дал отрицательный результат. Попробую еще. Если получится, постараюсь сообщить.

Конкретно в этом случае - если DTS указать varchar, то получается сообщение об ошибке, DTS не может преобразовать данные типа Date в текст!
О, как! :(
22 мар 05, 00:22    [1403857]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
27 понуро бредущих кроликов
Member

Откуда:
Сообщений: 262
AVVS
Конкретно в этом случае - если DTS указать varchar, то получается сообщение об ошибке, DTS не может преобразовать данные типа Date в текст!
О, как! :(


А ксли поставить Transformation DateTime String?
22 мар 05, 10:15    [1404348]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
AVVS
Member

Откуда:
Сообщений: 335
27 понуро бредущих кроликов
AVVS
Конкретно в этом случае - если DTS указать varchar, то получается сообщение об ошибке, DTS не может преобразовать данные типа Date в текст!
О, как! :(


А ксли поставить Transformation DateTime String?


Ща попробую. Но у меня вообще полная лажа полезла, когда я стал именно эти данные пробовать.

Для целевого столобца ставлю varchar(255) (в DTS).

В ехеле форматирую столбец как text.
При этом данные в виде даты съезжают в числовые значения (есть такая бага у ехеля).
Числа остаются числами, но съезжают к левому краю поля (т.е. становятся
текстовыми в понимании ехеля).

Хрен, с тобой золотая рыбка: перебиваю даты в виде текста, числовые оставляю как есть. При этом забиваю еще одно значение в виде (и формате) даты (чтбы эксперимент был чистым)

Импортирую.

Имеем -
Строки с буковками - OK
Строки с датой в виде текста - OK
Строка с датой в виде даты :) - NULL
Строки с цифирьками в виде текста - NULL

В общем, вышел мне от этого DTS'a "жестокий афронт" :)
22 мар 05, 10:38    [1404442]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Slav
Member

Откуда: МАсква, великий и прекрасный город
Сообщений: 9047
Glory

Это проблема ваша. Excel - это не то ПО, которое нужно использовать в качестве интерфейса. Потому что заставляет вас решать задачу по контролю вводимой информации не ДО а ПОСЛЕ ее ввода.


Glory,

в данном конкретном случае Ваш пафос немного не по адресу. Предполагается заключение большого контракта, потенциальный партнер принес мне данные в Excel - чтобы подложить под мою систему в MS SQL. Я сам таких файлов с таким количеством листов до сих пор не видел. Вы предлагаете мне прочитать им лекцию об интерфейсах и со словами "Это ваша проблема" послать куда подальше? Боюсь тогда мне придется разбираться с DTS в другом месте. ;-)

Glory

DTS как MSSQL НЕ работает с Excel файлами напрямую. Он использует Jet.OLEDB. Поэтому проблема ваша к DTS-у притянута за уши. Ибо DTS ничего сверх возможностей самого драйвера вам предложить не сможет

С уточнением согласен. Попробую учесть все тонкости формулировки проблемы: визард DTS (от Microsoft Corporation), используя Jet.OLEDB (от Microsoft Corporation) не позволяет произвести корректный импорт из Excel (от Microsoft Corporation) в MS SQL (от Microsoft Corporation). Конкретно к DTS претензий у меня нет. (великий А.Райкин вспомнился: "К пуговицам претензии есть? - Нет!")

Glory

Правда запоминать таким образом можно только по листам - а, если Вы помните, в моем случае их без малого 200 штук. Как раз на день работы.
Вы имете ввиду записывать каждый лист в файл ВРУЧНУЮ ??? Однако.

Вы будете удивлены - но я вообще не собираюсь записывать в текстовые файлы ничего!!! Я читаю непосредственно из xls файла - зачем мне еще какие-то???
1. Извините, но Вы обсуждаете несколько другую проблему. Мы говорим об импорте из файла Excel - а Вы (настойчиво) из текстового файла.
2. Вы будете еще раз удивлены - но написать в Excel макрос по Sheets.Count может не каждый. Кроме того, в этом же макросе необходимо предусмотреть формирование файла скрипта по загрузке этих самых текстовых файлов в MS SQL. Или Вы будете грузить эти 200 файлов ВРУЧНУЮ??? Только зачем же так корячиться?


Glory

Да - действительно IMEX=1 частично решает проблему. Но возникает другая - при наличии "фирменного" DTS приходится писать свою собственную утилиту. Со всеми вытекающими...
Если IMEX=1 решает проблему то какую еще утилиту нужно писать ?

Честно говоря, я не понял Вашего вопроса. Что значит "какую"? Да ту самую, где я этот IMEX поставлю в:
...OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\Test.xls";User ID=Admin;Password=;Extended properties="Excel 8.0;HDR=No;IMEX=1"')...Sheet1$

Зная Вашу педантичность уточню: "собственную утилиту (на VB or Delphi) или скрипт (на T-SQL)" Суть дела не меняет - обычную задачу по импорту данных нельзя поручить оператору. Изменить такой существенный параметр ( галочкой, например) в визарде DTS не представляется возможным - его там просто нет.

Все-таки хотелось бы, чтобы эта тема стала последней в проблеме импорта из Excel. Поэтому уточню, что я имел ввиду под:
Но эта опция тесно увязана - не очень прозрачным способом - с TypeGuessRows. На разных машинах может быть совершенно разный результат. Или на одной машине - при разных конкретных наборах. И логика эта для меня по прежнему загадка.
IMEX=1 действительно позволяет получить в корректном виде вседанные. Приятно радует, что работает он не дубово - если в столбце, допустим, число, дата, текст - то будет выбран результирующий текст. Если же, целое и флоат - то флоат. Смысл понятен - анализ содержимого столбца и выбор наиболее подходящего типа, чтобы передать все данные (не максимально возможное - а именно все). Но вот зачем вообще нужен TypeGuessRows? При том же значении 8 имеем:
1         1
2         2
3         3
abc      abc

1         1
2         2
....
8         8
9         9
abc      NULL

В первом случае - тип полученной колонки nvarchar, во втором - float. Какой смысл - задаться целью передать все данные, но анализировать только какую-то часть столбца? Почему нельзя - если уж стоит такая задача - проанализировать весь столбец, а потом выбрать тип для импорта? Быстродействие? Вряд ли - сам Excel не позволяет делать гигантские файлы. Да ведь и цель - импортировать все данные.
Вот и получается - в зависимости от того, какое значение TypeGuessRows в реестре на данной конкретной машине установлено (на клиенте - не на сервере), результаты будут разные. Кроме того, результат также зависит и от конкретных данных (см. пример выше). Поэтому - чтобы получить гарантированный результат - каждый раз надо узнать количество строчек в листе, изменить в реестре TypeGuessRows и затем открыть с IMEX=1.
22 мар 05, 13:20    [1405209]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Slav
Member

Откуда: МАсква, великий и прекрасный город
Сообщений: 9047
27 понуро бредущих кроликов
Slav
Импорт из Excel не работает. Хорошо - работает, мягко говоря, странно.


Странно...Я из EXCEL-я через DTS в ORACLE сливаю - и все нормально работает,в оракловаых таблицах все поля varchar2, в DTS Transformation - CopyColumn.

Перед этим еще Active-X Script-ом Names-ы в хls прописываю, из низ данные брать удобнее, чем прямо из Sheet-a, если в workBook несколько разных таблиц на страницу положено...


27 понуро бредущих кроликов,

наверно, MS любит ORACLE больше, чем себя. ;-)
22 мар 05, 13:23    [1405231]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
27 понуро бредущих кроликов
Member

Откуда:
Сообщений: 262
Slav
27 понуро бредущих кроликов
Slav
Импорт из Excel не работает. Хорошо - работает, мягко говоря, странно.


Странно...Я из EXCEL-я через DTS в ORACLE сливаю - и все нормально работает,в оракловаых таблицах все поля varchar2, в DTS Transformation - CopyColumn.

Перед этим еще Active-X Script-ом Names-ы в хls прописываю, из низ данные брать удобнее, чем прямо из Sheet-a, если в workBook несколько разных таблиц на страницу положено...


27 понуро бредущих кроликов,

наверно, MS любит ORACLE больше, чем себя. ;-)


Да просто так проще, чем OWB или HS настраивать.А MS SQL все равно у меня стоит..
24 мар 05, 11:06    [1411284]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Andrey_X
Member

Откуда: Kharkov
Сообщений: 5
Попробуйте заменить импорт на экспорт. Сам набил себе кучу шишек и с DTS и с OPENROWSET. Лучшим решением оказалось использование VB. Помимо типа столбца этот вариант обходит наличие хидеров и футеров, а также некоторые отклонения от нормализации при формировании таблиц Excel. Скорость максимальная. DTS рулит, но без наличия в Excel "изысков".
VB пользовал одноразово, только для этого случая. Так что по нему вопросы не ко мне и лучше в другой форум.

Public Sub ExportToSQL()
     
    Set cnnADODB = CreateObject("ADODB.Connection")
    Set cmdADODB = CreateObject("ADODB.Command")

    cnnADODB.ConnectionString = "Provider=SQLOLEDB.1;....." 
    cnnADODB.Open

    cmdADODB.ActiveConnection = cnnADODB
    cmdADODB.CommandType = 1
    cmdADODB.Prepared = True

    cmdADODB.CommandText = "INSERT INTO ['T1'] " & _
        "(F1, F2, F3, F4, F5) " & _
        " VALUES (?,?,?,?,?)"

    cmdADODB.Parameters.Append cmdADODB.CreateParameter("p1", 3, 1, 4)
    cmdADODB.Parameters.Append cmdADODB.CreateParameter("p2", 200, 1, 255)
    cmdADODB.Parameters.Append cmdADODB.CreateParameter("p3", 200, 1, 255)
    cmdADODB.Parameters.Append cmdADODB.CreateParameter("p4", 200, 1, 255)
    cmdADODB.Parameters.Append cmdADODB.CreateParameter("p5", 200, 1, 255)

    iRow = 5

    While ActiveSheet.Cells(iRow, 3).Value <> ""

        For iCol = 1 To 5
            par = "p" & iCol
            cmdADODB.Parameters(par).Value = ActiveSheet.Cells(iRow, iCol).Value 
        Next

        cmdADODB.Execute

        iRow = iRow + 1

    Wend

    cnnADODB.Close
    
    If Not cmdADODB Is Nothing Then Set cmdADODB = Nothing
    If Not cnnADODB Is Nothing Then Set cnnADODB = Nothing

End Sub
31 мар 05, 13:18    [1429879]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Pantalone
Member

Откуда:
Сообщений: 1321
Импорт из экселя в аксес дает ту же самую проблему. Весело живем..
14 апр 05, 12:16    [1467732]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Estets
Member

Откуда: Химки
Сообщений: 604
Когда столкнулся с такой-же проблемой, решил ее просто ;) написал маленький скриптик который прибавлял к значению поля пробел.
Потом загонял все данные как varchar через ODBC.

О IMEX=1 не знал, спасибо.
14 апр 05, 13:11    [1468092]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Tаrantino
Member

Откуда:
Сообщений: 3767
Мля звиздей просто какой-то, у меня получилось только через CSV
17 май 05, 11:37    [1546669]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Slav
Member

Откуда: МАсква, великий и прекрасный город
Сообщений: 9047
Tаrantino
Мля звиздей просто какой-то, у меня получилось только через CSV


Tаrantino,

во-во! Это так и работает - через... CSV. Повезет тому, у кого это один раз и недолго (в смысле - и файлов и листов в них немного). ;-)
17 май 05, 12:52    [1547114]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Tаrantino
Member

Откуда:
Сообщений: 3767
мне повезло, у меня один файл и один листик, а заметили только через два месяца как начали экспортировать их экселя...
17 май 05, 13:10    [1547229]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Pantalone
Member

Откуда:
Сообщений: 1321
Только что проверил, Аccess XP определяет каким будет столбец по первому значению в ячейке. По крайней мере поставив символ в поле оно залилось как текст, хотя без этого лилось как цифровое.
20 май 05, 10:13    [1557802]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Orlic
Member

Откуда: г. Ижевск
Сообщений: 564
Есть подобная проблема, по форуму лазил, ссылки на базу знаний Microsoft читал, не помогло.
Необходимо:
1. Из Excel-файла с одним листом импортировать данные в таблицу SQL Server 2000.
2. Импортировать пытался через DTS-Wizard, пошагово указывал, откуда что брать и КАК куда класть. Но, учитывай сабж топика, тип данных брался "с потолка", данные теряются...
3. Почитав статьи базы знаний Microsoft и топики форума по ключевым словам ИМПОРТ ДАННЫХ, понял, что визардом не справишься, и пытался сделать в Query Analizer следущее:
select *
into #LOADER
from 
opendatasource('Microsoft.Jet.OLEDB.4.0','DataSource="E:\TEST.xls";user id=Admin;Password=;Extended properties="Excel 8.0;HDR=No;IMEX=1"')...sheet1$
причем в реестре изменил ключ TypeGuessRows на 100000, да и на 0 менял, как написано в вышеуказанном FAQ...

Что имеем:
1. Вместо ошибки
Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error] 
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
получил ошибку
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  
[OLE/DB provider returned message: Невозможно найти устанавливаемый ISAM.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:   ].

Теперь то, что меня волнует:
1. Как же все таки импортировать данные и не потерять ни одной записи?
2. Какой скрипт/программу нужно написать или использовать хранимую процедуру на самом сервере?
3. Какой правильный синтаксис команды
select *
into #LOADER
from 
opendatasource('Microsoft.Jet.OLEDB.4.0','DataSource="E:\TEST.xls";user id=Admin;Password=;Extended properties="Excel 8.0;HDR=No;IMEX=1"')...sheet1$
то есть как указать к какому листу или именнованной области обращаться?
4. На чьейстороне (клиент или сервер) необходимо обновлять Jet, менять ключи в реестре, выдавать полный доступ на папку C:\Temp для учетной записи SYSTEM

Ногами пожалуйста не бить, приму к сведению дельные ссылки.
Спасибо.
9 ноя 05, 14:45    [2051664]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33541
Блог
DataSource заменить на Data Source
9 ноя 05, 15:04    [2051793]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Orlic
Member

Откуда: г. Ижевск
Сообщений: 564
Критик
DataSource заменить на Data Source

Помоголо, но не совсем.

Данные я выбрал, но не так, как надо... В столбце INN, например, хранятся ИНН, которые введены числа... При импорте даже со строкой
opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source="E:\TEST.xls";user id=Admin;Password=;Extended properties="Excel 8.0;HDR=Yes;IMEX=1"')...sheet1$

эти ИНН представляются как 1,83128E-06... Почему же, ведь я указал в параметрах явный тип текст???
10 ноя 05, 10:16    [2054275]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33541
Блог
укажите числовой, с числом десятичных знаков, равным 0
10 ноя 05, 10:59    [2054483]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Orlic
Member

Откуда: г. Ижевск
Сообщений: 564
Критик
укажите числовой, с числом десятичных знаков, равным 0

Что-то не совсем понял, как ...
Столбец INN в таблице содержит NULL, числовые данные (ИНН) и текстовые данные; в реестре изменил ключ TypeGuessRows на 100000, да и на 0 менял, как написано в вышеуказанном FAQ...


В QA запускаю этот запрос:
select *
into #LOADER
from 
opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source="E:\TEST.xls";user id=Admin;Password=;Extended properties="Excel 8.0;HDR=Yes;IMEX=1"')...sheet1$
получаю вместо числовых ИНН значения типа 1.836E-06, текстовые ячейки импортируются нормально...

Пытаюсь сделать так:
select Convert(Char(255), INN) as INN, ...
into #LOADER
from 
opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source="E:\TEST.xls";user id=Admin;Password=;Extended properties="Excel 8.0;HDR=Yes;IMEX=1"')...sheet1$
но получаю те же самые числа 1.836E-06

Вопрос:
1. Как же все-таки на этапе JET.OLEDB провайдера принять ЛЮБЫЕ данные КАК ТЕКСТ без КАКОГО-ЛИБО ФОРМАТИРОВАНИЯ/КОНВЕРТИРОВАНИЯ???
2. Можно это где-то в DTS сделать или там только можно указать тип данных уже импортированных, в таблицах SWL-Server'a???
10 ноя 05, 13:03    [2055430]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
sol1111
Member

Откуда:
Сообщений: 1
проблема не в DTS на самом деле, а в драйвере ODBC для экселя.

там, кстати и настраивается количество строчек, по которым определяется тип столбца.

он конфигурится какими-то ключиками. я нашел, когда надо было, а щас уж не помню, где это искать.
Вот такой ключик в коннект стринге мне помог немного: IMEX=1
15 ноя 05, 13:07    [2069841]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Импорт данных из MS Excel  [new]
Damir77
Member

Откуда:
Сообщений: 1
Вот еще одна программка для импорта данных из Excel. Программа анализирует данные на соответствие типов и ключей, не требует подключения к серверу БД.
Сайт программы - TSNDamir.narod.ru
26 янв 07, 16:25    [3700429]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
diwww
Member

Откуда:
Сообщений: 425
Slav
Тема возникает с завидной постоянностью - например, здесь или здесь.

К сожалению, столкнулся с ней. Прочитал советы:
1. Пройтись по ячейкам - и просто два раза по каждой кликнуть.
2. Пройтись по ячейкам - и проставить апостроф.
3. Идти через csv (или другой промежуточный формат).
4. Корректировать скрипты DTS.
5. Писать свою прогу с использованием неких уникальных опций.
6. ...



Способ № 6. Имхо, более простой и быстрый чем 1 и 2:
В Excel выделям столбец \ Данные \ Текст по столбцам \ Фиксированной ширины \ Ставим разделитель далеко справа \ Делее \ Тому что слева задаем текстовый формат \ Готово
1 фев 07, 10:43    [3723805]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Импорт данных из MS Excel  [new]
tigerskin
Member

Откуда:
Сообщений: 1
Ребят, я не программист ни разу, просту приходится в excel писать...
Так вот, я решил эту проблему так:
в HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel изменил значение TypeGuessRows на 1. Т.е. Jet 4 анализирует первые восемь строк по умолчанию... Функция MaxScanRows ни к чему не приводит, а именно это действо - в самую дырочку. Под шапкой таблицы прописал произвольный текст и теперь получаю все данные в текстовом виде: 12.02.08 --> "12.02.08" и тэпэ... Не знаю что такое SQL (ну слышал конечно , но может кому пригодится :))
7 апр 08, 16:15    [5513468]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
Хм...
2 tigerskin, вы на дату последнего поста смотрели? Больше года прошло однако...
8 апр 08, 07:18    [5515703]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
mr_Black63
Member

Откуда: Самара
Сообщений: 34
Коллеги, всётаки подскажите. у меня от нет проблем с типами даннх.
но
select * into #LOADER
from opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source="C:\TEST.xls";user id=x;Password=y;Extended properties="HDR=Yes;IMEX=1"')...xactions
НЕРАБОТАЕТ (SQL 2000).
Ошибка таже
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Не удается запустить приложение. Системная база данных отсутствует или открыта с монопольным доступом другим пользователем.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

Хотя следующие инструкции выполнил
http://support.microsoft.com/default.aspx?scid=kb;en-us;814398

импортировать данные подобным способом самый просто и удобный вариант для меня.
16 дек 08, 15:12    [6577866]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
Сегодня занимался продвинутой камасутрой с импортом их Экселя столбца, где находились числовые и текстовые данные.

Помог этот топик.
Послее введения параметера IMEX=1 импорт стал корректен.

Спасибо коллеги!
5 мар 09, 18:50    [6893609]     Ответить | Цитировать Сообщить модератору
 Re: Импорт данных из MS Excel  [new]
vassav
Member

Откуда:
Сообщений: 4
Читаю экселевский файл с помощью MICROSOFT.JETю В столбце есть как текст так и числа, весь столбец читается полностью благодоря IMEX=1, но числа, сохраненные в формате числа и читаются в виде 1,234е+4.

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

1. Пройтись по ячейкам - и просто два раза по каждой кликнуть.
2. Пройтись по ячейкам - и проставить апостроф.
3. Идти через csv (или другой промежуточный формат).

Первые два с пособа не подходят, так рограмма работает на сервере, и обрабатывает присылаемые файлы. На сервере так же нет офиса, так что запускать эксель не получится.
5. Писать свою прогу с использованием неких уникальных опций.

P.S. Может уже есть что-то готовое что можно использовать с открытым кодом или бесплатное под .NET?
7 июл 09, 11:51    [7385171]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2 3      [все]
Все форумы / Microsoft SQL Server Ответить