Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PowerBuilder [игнор отключен] [закрыт для гостей] / SELECT из Excel через OLE DB / 18 сообщений из 18, страница 1 из 1
14.05.2007, 17:43
    #34524213
Ikar
Ikar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
Приветствую всех. Столкнулся с проблемой, никак не могу найти решения. Суть в следующем.

Требуется забирать данные из Excel-файла. Забор происходит путем создания datastore (или datawindow) на основе SELECT через OLE DB. Вроде бы все работает, но есть одно но... Если в каком либо столбце (заранее известно, что данные в этом столбце числовые) в первых 9-и строках (не считая строки с названиями столбцов, или, соответственно, если считать заголовки, то в первых 10-и) значения нет, то весь столбец затягивается пустой вне зависимости от того, есть ли данные ниже или нет.

Обнаружил, что при выполнении syntaxfromsql для "плохого" столбца проставляется тип данных "char(255)". Пробовал ручками менять на "numeric" - бесполезно, все равно столбец возвращается пустым.

Попытки использования "SELECT IsNull(col_b, 0) ...", "SELECT convert(numeric, col_b) ..." и т.п. приводят либо к ошибке создания синтаксиса или ДВ, либо к сваливанию самого РВ (т.к. гоняю, естественно, под дебаггером).

Тестовый код:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
string ls_fname, ls_select, ls_presentation, ls_err, ls_syntax
long ll_rc
transaction lt_tran

ls_fname = "c:\Temp\testfile.xls"

TRY
	lt_tran = create transaction
	// Profile Excel
	lt_tran.DBMS = "OLE DB"
	lt_tran.AutoCommit = False
	lt_tran.DBParm = "PROVIDER='Microsoft.Jet.OLEDB.4.0',DATASOURCE='" + ls_fname + "',PROVIDERSTRING='Excel 5.0'"
	connect using lt_tran;
	if lt_tran.sqlcode <>  0  then
		messagebox("Ошибка!","Ошибка при подключении к файлу " + ls_fname + "~n" + lt_tran.sqlerrtext, stopsign!)
		return - 1 
	end if
	
	ls_select = 'select col_a, col_b, col_c from [asdf$] '
	
	ls_presentation = "style(type=grid) column(font.face='MS Sans Serif')"
	
	ls_syntax = lt_tran.syntaxfromsql(ls_select, ls_presentation, ls_err)
	dw_2.create(ls_syntax,ls_err)
	
	dw_2.settransobject(lt_tran)
	ll_rc = dw_2.retrieve()

	
FINALLY
	disconnect using lt_tran;
	destroy lt_tran
END TRY
Код выполняется по нажатию кнопки на окне. dw_2 - datawindow на окне.

Тестовый файл "c:\Temp\testfile.xls" содержит лист "asdf", с которого производится выборка, со следующими значениями:
col_acol_bcol_c1122324252627181911234.561024
Проблема возникает, соответственно, со столбцом "col_b".

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

РВ 8.0.3 build 9704 (проверял на РВ 10 - тоже самое)

------------
С уважением, IKAR

IkarHomeCenter ........ Детский Дворик ........ ГавГав.Инфо
Портал русскоязычной группы пользователей Sybase
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.05.2007, 18:22
    #34524355
Dmitry.
Dmitry.
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
эт приколы самого екселя
--

я создал указанный файл
запустил MS Query
подконнектился к файлу через ODBC (родной драйвер микрософта)
результат: колонка col_b пустая
--

т.е. решать проблему надо на уровне ексель файла а не на уровне ПБ
например для всей колонки col_b установить формат number
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2007, 10:38
    #34525355
ChV
ChV
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
Через ODBC у меня все получилось ( MS Query ). Через OLE DB не работаю - не было необходимости.
Код: plaintext
1.
SELECT `asdf$`.col_a, `asdf$`.col_b, `asdf$`.col_c  FROM `C:\ 111 \test`.`asdf$` `asdf$`

Good Luck !
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2007, 11:11
    #34525502
Estets
Estets
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
ChVЧерез ODBC у меня все получилось ( MS Query ).
Ну с ODBC у меня в свое время тоже были приколы, там есть настройка в которой раньше не работало значение "0". Так что если данные начинались с 17-ой строки, то колонка не читалась. Сейчас честно говоря времени проверить нет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2007, 11:22
    #34525564
Ikar
Ikar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
Спасибо всем. Решение найдено.
http://connectionstrings.com/default.aspx?carrier=excel
5-й абзац описания OLE DBCheck 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.

Поменял значение на 0 (пока что ручками) и покатило. На всякий случай попробовал усугубить пример и установил первое значение в файл на 130-й строке - тоже работает. Так что будем править реестр...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.05.2007, 11:27
    #34525589
ChV
ChV
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
Вообще-то я с Excel редко работаю. Пока "грабли" не попадались.

Good Luck !
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.05.2007, 16:38
    #34529936
KOLCHOZ_POSTEVENT
KOLCHOZ_POSTEVENT
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
Как на твой взгляд,нормально этот твой ексель в екстарнал засосался?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.05.2007, 16:43
    #34529960
KOLCHOZ_POSTEVENT
KOLCHOZ_POSTEVENT
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
Я прошу прощения,одну клетку потерял при копировании,вот так нормально?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.05.2007, 16:54
    #34530019
Ikar
Ikar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
KOLCHOZ_POSTEVENTКак на твой взгляд,нормально этот твой ексель в екстарнал засосался?эээ... А у кого именно Вы спрашиваете? Если у меня, то да, нормально. И о том, что решение проблемы найдено я уже и написал выше
( http://www.sql.ru/forum/actualthread.aspx?tid=428842#4137920 )

А вообще, на самом деле, не решенным остался один момент. Если в столбце, тип данных которого при создании синтаксиса ДВ определен как текстовый (char(255)) имеются числовые значения, то вместо этих значений при retrieve получаем пустую строку или NULL (точно не смотрел).

Тоесть если в столбце екселя будут такие значения:
col_1aaabbb111ccc
то после retrieve получаем следующее:
col_1aaabbb.ccc

В рамках текущей задачи мне это не мешает, т.к. такие ситуации не возникают, но на будущее это может оказаться серьезной проблемой.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.05.2007, 17:05
    #34530074
KOLCHOZ_POSTEVENT
KOLCHOZ_POSTEVENT
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
Да,ну дай его сюда,если не лень,проблеммный примерчик...посмотрим,на него вблизи.
Я-то,признаться,никаких нулей руками не вбивал и диссертацию не писал,всё на кликах.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.05.2007, 17:58
    #34530336
Ikar
Ikar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
KOLCHOZ_POSTEVENTДа,ну дай его сюда,если не лень,проблеммный примерчик...посмотрим,на него вблизи.
Я-то,признаться,никаких нулей руками не вбивал и диссертацию не писал,всё на кликах.Да чегож лень то... Совсем не лень... Правда про диссертацию я чего то не понял, ну да ладно, не об том речь...

На окне ДВ (dw_2) и кнопка, на кнопке код:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
string ls_fname, ls_select, ls_presentation, ls_err, ls_syntax
long ll_rc
ulong ll_RegValue_old
transaction lt_tran
datawindow ld_dst

ls_fname = "c:\Temp\testfile.xls"

TRY
	ll_rc = RegistryGet("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel", "TypeGuessRows", ReguLong!, ll_RegValue_old)
	if ll_rc = - 1  then
		messagebox("Ошибка", "Ошибка получения значения из реестра!")
		return - 1 
	end if
	ll_rc = RegistrySet("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel", "TypeGuessRows", ReguLong!,  0 )
	if ll_rc = - 1  then
		messagebox("Ошибка", "Ошибка установки значения в реестр!")
		return - 1 
	end if
	lt_tran = create transaction
	// Profile Excel
	lt_tran.DBMS = "OLE DB"
	lt_tran.AutoCommit = False
	lt_tran.DBParm = "PROVIDER='Microsoft.Jet.OLEDB.4.0',DATASOURCE='" + ls_fname + "',PROVIDERSTRING='Excel 5.0'"
	connect using lt_tran;
	if lt_tran.sqlcode <>  0  then
		messagebox("Ошибка!","Ошибка при подключении к входному файлу " + ls_fname + "~n" + lt_tran.sqlerrtext, stopsign!)
		return - 1 
	end if
	
	ld_dst = create datawindow
	ls_select = 'select col_a, col_b, col_c, abc from [asdf$] '
	
	ls_presentation = "style(type=grid) column(font.face='MS Sans Serif')"
	
	ls_syntax = lt_tran.syntaxfromsql(ls_select, ls_presentation, ls_err)
	dw_2.create(ls_syntax,ls_err)
	
	dw_2.settransobject(lt_tran)
	ll_rc = dw_2.retrieve()
FINALLY
	disconnect using lt_tran;
	destroy ld_dst
	destroy lt_tran
	ll_rc = RegistrySet("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel", "TypeGuessRows", ReguLong!, ll_RegValue_old)
	if ll_rc = - 1  then
		messagebox("Ошибка", "Ошибка восстановления значения в реестре!")
		return - 1 
	end if
END TRY
Файлик в аттаче.

Проблемка наблюдается в столбце 'abc'. После dw_2.retrieve() в строках 5, 6, 20 - 22, 36 - 38, 53 - 55 и т.д. значения в столбце 'abc' пустые...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2007, 11:10
    #34531584
KOLCHOZ_POSTEVENT
KOLCHOZ_POSTEVENT
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
Ты мне дал немного неточную нумерацию проблеммных строк.
Не 36-38,а 37-39,если смотреть на нумерацию в екселе,а не в колонке col_A.
Русский шрифт на моей машине в PB не отображается и я должен с этим мирится.В остальном,вроде всё чётко,а?

А это код который это делает
Код: plaintext
1.
2.
dw_1.importfile('d:\1.txt')
dw_1.deleterow( 1 )
Думаю,DW показывать не надо,ты его легко представишь,екстернал с 4 стринг полями по 255 символов в каждом.Ну еще счётчик строк-вычисляемое поле.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2007, 11:14
    #34531605
KOLCHOZ_POSTEVENT
KOLCHOZ_POSTEVENT
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
А это то,что осталось за кадром,с остальными пролеммными сроками.
Подходит?Можно обсудить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2007, 11:16
    #34531617
KOLCHOZ_POSTEVENT
KOLCHOZ_POSTEVENT
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
Прошу прощения за ошибки,виртуальная клавиатура,знаете-ли.
А это то,что осталось за кадром,с остальными проблеммными строками.
Подходит?Можно обсудить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2007, 15:24
    #34532923
Ikar
Ikar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
Прошу прощения за ошибку с номерами строк - ступил...

А на счет варианта с
авторdw_1.importfile('d:\1.txt')
dw_1.deleterow(1)
Это конечно хорошо, но... Задача-то стоит в решении проблемы именно при получении данных из файла excel напрямую, SQL-запросом через OLE DB, а не через промежуточные варианты. Ибо промежуточный вариант с макросом ничуть не хуже предложенного тобой (хотя, опять же, хуже или не хуже будет зависеть от обстоятельств конкретной задачи).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2007, 16:31
    #34533204
KOLCHOZ_POSTEVENT
KOLCHOZ_POSTEVENT
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
Ладно,тебе видней.
Мне приходилось загонять в DW биржевые котировки из екселя.
Заставил юзера сперва превращать его в текстовый файл,tab delimeted-3 клика,дальше ты знаешь.Поморщились,но смирились,уж больно устойчиво заработало,к тому-же прекратились ошибки с забиранием "не своего" файла,когда открываешь файл и делаешь ему save as взгляд ,невольно,цепляется за название.
Но,если у вас такая автоматика,что только OLE DB-удачи,короче.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2007, 16:36
    #34533230
Ikar
Ikar
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
Видней не мне, видней начальству-заказчику :)
А так - то, оно и есть, что автоматика. Файлы (кстати, именно биржевые котировки) кладутся автоматом в определенное место (уж не знаю кем или чем), софт их автоматом забирает, импортит, пересчитывает... А юзеры получают с этого результаты и отчеты...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.05.2007, 16:44
    #34533269
KOLCHOZ_POSTEVENT
KOLCHOZ_POSTEVENT
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SELECT из Excel через OLE DB
Там было не совсем так.Юзер сам решал,какой файл ему забрать и обсчитать,а названия файлов были похожи.Ладно,пока.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PowerBuilder [игнор отключен] [закрыт для гостей] / SELECT из Excel через OLE DB / 18 сообщений из 18, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 367ms
Закрыть
start [/forum/topic.php?fid=15&mobile=1&tid=1337170]:
 0ms
get settings:
 11ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 40ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 50ms
get tp. blocked users:
 1ms
others: 232ms
total:  367ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]