SELECT из Excel через OLE DB / PowerBuilder

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

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

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PowerBuilder [игнор отключен] [закрыт для гостей] / SELECT из Excel через OLE DB

18 сообщений из 18, страница 1 из 1

SELECT из Excel через OLE DB

#34524213

Ikar

Участник

Откуда: Москва

Сообщения: 931

Рейтинг: 0 / 0

Приветствую всех. Столкнулся с проблемой, никак не могу найти решения. Суть в следующем.

Требуется забирать данные из 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, 17:43

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34524355

Dmitry.

Участник

Откуда: Boston, MA

Сообщения: 858

Рейтинг: 0 / 0

эт приколы самого екселя
--

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

т.е. решать проблему надо на уровне ексель файла а не на уровне ПБ
например для всей колонки col_b установить формат number

...

Рейтинг:

0 / 0

14.05.2007, 18:22

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34525355

ChV

Участник

Откуда: Москва

Сообщения: 289

Рейтинг: 0 / 0

Через ODBC у меня все получилось ( MS Query ). Через OLE DB не работаю - не было необходимости.

Код: plaintext

SELECT `asdf$`.col_a, `asdf$`.col_b, `asdf$`.col_c  FROM `C:\ 111 \test`.`asdf$` `asdf$`

Good Luck !

...

Рейтинг:

0 / 0

15.05.2007, 10:38

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34525502

Estets

Участник

Откуда: Химки

Сообщения: 605

Рейтинг: 0 / 0

ChVЧерез ODBC у меня все получилось ( MS Query ).
Ну с ODBC у меня в свое время тоже были приколы, там есть настройка в которой раньше не работало значение "0". Так что если данные начинались с 17-ой строки, то колонка не читалась. Сейчас честно говоря времени проверить нет.

...

Рейтинг:

0 / 0

15.05.2007, 11:11

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34525564

Ikar

Участник

Откуда: Москва

Сообщения: 931

Рейтинг: 0 / 0

Спасибо всем. Решение найдено.
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:22

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34525589

ChV

Участник

Откуда: Москва

Сообщения: 289

Рейтинг: 0 / 0

Вообще-то я с Excel редко работаю. Пока "грабли" не попадались.

Good Luck !

...

Рейтинг:

0 / 0

15.05.2007, 11:27

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34529936

KOLCHOZ_POSTEVENT

Гость

Как на твой взгляд,нормально этот твой ексель в екстарнал засосался?

...

Рейтинг:

0 / 0

16.05.2007, 16:38

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34529960

KOLCHOZ_POSTEVENT

Гость

Я прошу прощения,одну клетку потерял при копировании,вот так нормально?

...

Рейтинг:

0 / 0

16.05.2007, 16:43

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34530019

Ikar

Участник

Откуда: Москва

Сообщения: 931

Рейтинг: 0 / 0

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, 16:54

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34530074

KOLCHOZ_POSTEVENT

Гость

Да,ну дай его сюда,если не лень,проблеммный примерчик...посмотрим,на него вблизи.
Я-то,признаться,никаких нулей руками не вбивал и диссертацию не писал,всё на кликах.

...

Рейтинг:

0 / 0

16.05.2007, 17:05

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34530336

Ikar

Участник

Откуда: Москва

Сообщения: 931

Рейтинг: 0 / 0

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

16.05.2007, 17:58

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34531584

KOLCHOZ_POSTEVENT

Гость

Ты мне дал немного неточную нумерацию проблеммных строк.
Не 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:10

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34531605

KOLCHOZ_POSTEVENT

Гость

А это то,что осталось за кадром,с остальными пролеммными сроками.
Подходит?Можно обсудить.

...

Рейтинг:

0 / 0

17.05.2007, 11:14

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34531617

KOLCHOZ_POSTEVENT

Гость

Прошу прощения за ошибки,виртуальная клавиатура,знаете-ли.
А это то,что осталось за кадром,с остальными проблеммными строками.
Подходит?Можно обсудить.

...

Рейтинг:

0 / 0

17.05.2007, 11:16

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34532923

Ikar

Участник

Откуда: Москва

Сообщения: 931

Рейтинг: 0 / 0

Прошу прощения за ошибку с номерами строк - ступил...

А на счет варианта с
авторdw_1.importfile('d:\1.txt')
dw_1.deleterow(1)
Это конечно хорошо, но... Задача-то стоит в решении проблемы именно при получении данных из файла excel напрямую, SQL-запросом через OLE DB, а не через промежуточные варианты. Ибо промежуточный вариант с макросом ничуть не хуже предложенного тобой (хотя, опять же, хуже или не хуже будет зависеть от обстоятельств конкретной задачи).

...

Рейтинг:

0 / 0

17.05.2007, 15:24

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34533204

KOLCHOZ_POSTEVENT

Гость

Ладно,тебе видней.
Мне приходилось загонять в DW биржевые котировки из екселя.
Заставил юзера сперва превращать его в текстовый файл,tab delimeted-3 клика,дальше ты знаешь.Поморщились,но смирились,уж больно устойчиво заработало,к тому-же прекратились ошибки с забиранием "не своего" файла,когда открываешь файл и делаешь ему save as взгляд ,невольно,цепляется за название.
Но,если у вас такая автоматика,что только OLE DB-удачи,короче.

...

Рейтинг:

0 / 0

17.05.2007, 16:31

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34533230

Ikar

Участник

Откуда: Москва

Сообщения: 931

Рейтинг: 0 / 0

Видней не мне, видней начальству-заказчику :)
А так - то, оно и есть, что автоматика. Файлы (кстати, именно биржевые котировки) кладутся автоматом в определенное место (уж не знаю кем или чем), софт их автоматом забирает, импортит, пересчитывает... А юзеры получают с этого результаты и отчеты...

...

Рейтинг:

0 / 0

17.05.2007, 16:36

| Ответить | Цитировать | Написать

SELECT из Excel через OLE DB

#34533269

KOLCHOZ_POSTEVENT

Гость

Там было не совсем так.Юзер сам решал,какой файл ему забрать и обсчитать,а названия файлов были похожи.Ладно,пока.

...

Рейтинг:

0 / 0

17.05.2007, 16:44

| Ответить | Цитировать | Написать

18 сообщений из 18, страница 1 из 1

Форумы / PowerBuilder [игнор отключен] [закрыт для гостей] / SELECT из Excel через OLE DB

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=15&msg=34530074&tid=1337170]:	0ms
get settings:	9ms
get forum list:	13ms
check forum access:	4ms
check topic access:	4ms
track hit:	35ms
get topic data:	10ms
get forum data:	3ms
get page messages:	47ms
get tp. blocked users:	1ms
others:	236ms

total:	362ms