Closed DrinkPoison closed 5 years ago
mbleron
commented
5 years ago Hi,
I'll try to reproduce the problem and debug.
One remark though, you shouldn't use GETCELLS function, it's not documented and does not support all file formats. Does it work with GETROWS?
DrinkPoison
commented
5 years ago no, with getrows I have the same issue.
I use GETCELLS just cause it returns dataset like Anton Scheffer's as_xlsx package which we used before :)
But any case I need to parse the file by url above, and got strange error by both GETROWS and GETCELLS functions
mbleron
commented
5 years ago Found the problem after getting the following error :
ORA-21560: argument 3 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 1056
ORA-06512: at "DEV.XUTL_XLS", line 288
ORA-06512: at "DEV.XUTL_XLS", line 380
ORA-06512: at "DEV.XUTL_XLS", line 968
ORA-06512: at "DEV.XUTL_XLS", line 1598
ORA-06512: at "DEV.EXCELTABLE", line 3762Don't know yet why the session freezes in your case though...
So the problem is that the worksheet contains invalid Index entries starting from the 3rd row block, i.e. pointers to DBCell records don't actually point to the beginning of a DBCell. First two entries are correct that's why it works if we specify a range like '1:64'.
The file opens correctly in Excel though, that means it either doesn't use the index to load the file, or can detect invalid entries and falls back on direct row block scans. Saving the file again from Excel repairs the index and that's why it works after that, as you noticed. My program relies on a correct index structure to extract cell data so for now there's no workaround. I'll need some time to implement a fallback mechanism.
mbleron
commented
5 years ago Done in v4.0.1 :
select t.sheetidx sheet_nr
, t.cellrow row_nr
, t.cellcol || t.cellrow nm_cell
, sys.anydata.gettypename(t.celldata) as cell_type
, case sys.anydata.gettypename(t.celldata)
when 'SYS.VARCHAR2' then sys.anydata.accessvarchar2(t.celldata)
when 'SYS.NUMBER' then to_char(sys.anydata.accessnumber(t.celldata), 'FM99999990D00999') end
as cell_value
from table(
exceltable.getcells(
p_file => exceltable.getFile('XL_DATA_DIR','20190910_MOSENERG_PMOSENER_082019_gtp_1st_stage.xls')
, p_sheet => sys.anydata.convertvarchar2('Лист1')
, p_cols => '"A" varchar2(4000), "B" varchar2(4000), "C" varchar2(4000), "D" varchar2(4000), "E" varchar2(4000), "F" varchar2(4000)'
)
) t
;
SHEET_NR ROW_NR NM_CELL CELL_TYPE CELL_VALUE
-------- ------ ------- --------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 2 A2 SYS.VARCHAR2 Составляющие предельных уровней нерегулируемых цен
1 3 A3 SYS.VARCHAR2 за расчетный период
1 3 B3 SYS.VARCHAR2 август 2019
1 4 A4 SYS.VARCHAR2 для ГТП
1 4 B4 SYS.VARCHAR2 PMOSENER
1 5 A5 SYS.VARCHAR2 участника оптового рынка
1 5 B5 SYS.VARCHAR2 АО "Мосэнергосбыт"
1 9 A9 SYS.VARCHAR2 Дифференцированные по зонам суток расчетного периода средневзвешенные нерегулируемые цены на электрическую энергию (мощность) на оптовом рынке и средневзвешенные нерегулируемые цены на электрическую энергию на оптовом рынке, определяемые для соответствующих зон суток, руб/МВтч
1 10 A10 SYS.VARCHAR2 Дифференцированная по зонам суток расчетного периода средневзвешенная нерегулируемая цена на электрическую энергию (мощность) на оптовом рынке по трем зонам суток:
1 11 A11 SYS.VARCHAR2 Ночная зона
1 11 B11 SYS.NUMBER 963.84
1 12 A12 SYS.VARCHAR2 Полупиковая зона
1 12 B12 SYS.NUMBER 2647.20
...
1 783 F783 SYS.NUMBER 1398.16
1 784 A784 SYS.VARCHAR2 31.08.2019
1 784 B784 SYS.NUMBER 23.00
1 784 C784 SYS.NUMBER 1074.89
1 784 D784 SYS.NUMBER 0.00
1 784 E784 SYS.NUMBER 217.25
1 784 F784 SYS.NUMBER 1091.99
1 788 A788 SYS.VARCHAR2 В случае если результатом расчета составляющей предельных уровней нерегулируемых цен (кроме приходящейся на единицу электрической энергии величины разницы предварительных требований и обязательств по результатам конкурентного отбора ценовых заявок на сутки вперед и приходящейся на единицу электрической энергии величины разницы предварительных требований и обязательств по результатам конкурентного отбора заявок для балансирования системы) и иных подлежащих публикации величин в соответствии с подпунктами 1 и 3.3 п. 10.8 Регламента Финансовых расчетов на оптовом рынке э/э является отрицательная величина, то КО публикует вместо отрицательной величины 0
1 790 A790 SYS.VARCHAR2 В случае если результатом расчета составляющей предельных уровней нерегулируемых цен и иных, подлежащих публикации величин в соответствии с подпунктами 1 и 3.3 п. 10.8 Регламента Финансовых расчетов на оптовом рынке э/э является неопределенность, то КО публикует вместо неопределенности 0
or, using getRows :
select t.*
from table(
exceltable.getrows(
p_file => exceltable.getFile('XL_DATA_DIR','20190910_MOSENERG_PMOSENER_082019_gtp_1st_stage.xls')
, p_sheet => 'Лист1'
, p_cols => q'{"A" date format 'DD.MM.YYYY', "B" number, "C" number, "D" number, "E" number, "F" number}'
, p_range => '41:784'
)
) t
;
A B C D E F
---------- ---------- ---------- ---------- ---------- ----------
01/08/2019 0 1151.6 0 180.99 1168.7
01/08/2019 1 1037.18 0 91.19 1054.28
01/08/2019 2 981.44 0 67.38 998.54
01/08/2019 3 946.4 0 47.63 963.5
01/08/2019 4 950.26 0 90.74 967.36
01/08/2019 5 1021.85 40.27 0 1038.95
01/08/2019 6 1189.16 35.43 0 1206.26
01/08/2019 7 1328.17 6.83 .34 1345.27
01/08/2019 8 1372.41 .73 19.33 1389.51
01/08/2019 9 1618.84 6.3 268.87 1635.94
01/08/2019 10 1657.5 2.8 355.15 1674.6
01/08/2019 11 1664.2 2.16 402.15 1681.3
01/08/2019 12 1644.59 1.29 503.42 1661.69
...
31/08/2019 11 1444.45 0 249.96 1461.55
31/08/2019 12 1408.09 0 278.09 1425.19
31/08/2019 13 1468.2 0 218.65 1485.3
31/08/2019 14 1450.4 0 276.77 1467.5
31/08/2019 15 1422.08 0 247.88 1439.18
31/08/2019 16 1437.64 0 232.63 1454.74
31/08/2019 17 1449.64 0 192.04 1466.74
31/08/2019 18 1417.95 0 310.61 1435.05
31/08/2019 19 1439.8 0 130 1456.9
31/08/2019 20 1479.12 0 276.48 1496.22
31/08/2019 21 1455.86 0 316.84 1472.96
31/08/2019 22 1381.06 0 383.17 1398.16
31/08/2019 23 1074.89 0 217.25 1091.99
Wow great. Thanks :)
mbleron
commented
5 years ago Closing the issue. Thanks for submitting the case :smiley:
Hi, this is very usefull parser for XLS/XLSX file, but I got an error, and can't find the reason. When I try to load .XLS files, some files - parsing got successed, but some files - got error (actually no error, session just freezing and dropped without error in some minutes without return any data) It looks like codepage issue or something like it, but I really can't fix it. Can you help me, please? DB: Oracle 12.1 1) Load and try to parse file (http://www.atsenergo.ru/dload/retail/20190801/20190910_MOSENERG_PMOSENER_082019_gtp_1st_stage.xls) 2) using next source
select t.sheetidx sheet_nr, t.cellrow row_nr, t.cellcol || t.cellrow nm_cell, sys.anydata.gettypename(t.celldata) as cell_type, case sys.anydata.gettypename(t.celldata) when 'SYS.VARCHAR2' then sys.anydata.accessvarchar2(t.celldata) when 'SYS.NUMBER' then to_char(sys.anydata.accessnumber(t.celldata), 'FM99999990D00999') when 'SYS.DATE' then to_char(sys.anydata.accessdate(t.celldata), 'DD-MON-YYYY HH24:MI:SS') end as cell_value from table(exceltable.getcells(p_file => :p_vl_file, p_sheet => sys.anydata.convertvarchar2('Лист1'), p_cols => '"A" varchar2(4000), "B" varchar2(4000), "C" varchar2(4000), "D" varchar2(4000), "E" varchar2(4000), "F" varchar2(4000) ')) tExcel application - can open and edit this file, without any errors. And when I edit and save file via Excel application as .xls or .xlsx - source above can parse it easy... But when I try to parse file from link above - it always freeze and dropped.