Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Импорт данных из 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
Сообщений: 1342
да, хреновый косяк, я когда первый раз с ним столкнулся, просто о..ел. Повезло еще, что вовремя заметил !

При трансформации, формат данных определяется по первым нескольким строчкам, и если там числа - то вместо текста снизу окажется 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
Сообщений: 1342
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
Сообщений: 1342
Дарю ;)

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]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить