Reading .xls file gives error with 5.0 version

[madhuridara]

Hi,

I am using this code to read an .xls file but unable to do it. Where am I going wrong? I am using the 5.0 version.

ORA-20731: Error at position 310477, expecting a [String] record ORA-06512: at "RARFSTG2.XUTL_XLS", line 263 ORA-06512: at "RARFSTG2.XUTL_XLS", line 283 ORA-06512: at "RARFSTG2.XUTL_XLS", line 740 ORA-06512: at "RARFSTG2.XUTL_XLS", line 953 ORA-06512: at "RARFSTG2.XUTL_XLS", line 1498 ORA-06512: at "RARFSTG2.EXCELTABLE", line 4360 ORA-06512: at "RARFSTG2.EXCELTABLEIMPL", line 140

SELECT t.* FROM Table( ExcelTable.getRows( ExcelTable.getFile('TEST'123,'tstxl90.xls') , 'Unit Info - RENEWABLE' , ' "COL1" varchar2(200) , "COL2" varchar2(200) , "COL3" varchar2(200) , "COL4" varchar2(200) , "COL5" varchar2(200) , "COL6" varchar2(200)' ) ) t ;

[mbleron]

Hi,

Looks like unexpected data in the file content. I would require a sample file to sort this out. Could you attach one here so that I can reproduce the issue? Thanks.

[madhuridara]

SELECT t.* FROM Table(  ExcelTable.getRows( exceltable.getFile('TEST','tstxl.xls') ,ExcelTableSheetList('sheet1') ,' "COL1" varchar2(200 char)    , "COL2" varchar2(1 char)    , "COL3" varchar2(200 char)    , "COL4" varchar2(200 char)    , "COL5" varchar2(200 char)    , "COL6" varchar2(200 char)    , "COL7" varchar2(200 char)    , "COL8" varchar2(200 char)    , "COL9" varchar2(200 char)    , "COL10" date' , '9:10',1 ) ) t ;

Here is a sample attached file.

Thank You, Madhuri Dara

On Sunday, August 8, 2021, 07:56:48 AM CDT, Marc Bleron @.***> wrote:

Hi,

Looks like unexpected data in the file content. I would require a sample file to sort this out. Could you attach one here so that I can reproduce the issue? Thanks.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android.

[madhuridara]

Adding a little context of my project..

A table in an Oracle database has a BLOB that contains a zipped file of 3 xls files. I need to process these 3 xls files by reading them and loading the tables with the relevant data.

When I save the blob to xls file and manually save it as xlsx, I am able to read. But there are 100s of such files. I either have to programmatically convert the xls to xlsx and then read or use your exceltable to read the xls blob directly.

I spent last week trying to make the ExcelTable work but I hit a hard block at reading the formula. You make sure the stream is an RT_String (0702) but my file gets the code BC04. I thought it could be with the variables/constant in a formula.. not sure..

I have no time anymore, I have a deadline for this effort- perhaps 2 more days. The other option I have in mind is to write a Java Stored Procedure to convert XLS to XLSX. There are some api like Apache POI/ Aspose etc. But If ExcelTable works, that would be my first choice.

I wish there is a work around for my issue with ExcelTable. Eagerly waiting for your feedback :)

Thank You, Madhuri Dara

On Aug 8, 2021, at 1:46 PM, Madhuri Dara @.***> wrote:

[mbleron]

Sorry, attachments get lost when you reply via email. You'll have to attach your sample file directly here on the GitHub issue. I'll look at it asap.

[mbleron]

Record number 0x04BC happens to be a ShrFmla record. It's an optional record meant to optimize formula storage, and may be encountered between a Formula and a String record. We just have to ignore it. Code fixed in v5.2.1.

[madhuridara]

Marc,

 The ExcelTable works with .xls files.

You are the best. You saved my project and paycheck!!

Thank You So much!!

-Madhuri Dara

On Monday, August 9, 2021, 02:25:45 AM PDT, Marc Bleron @.***> wrote:

Record number 0x04BC happens to be a ShrFmla record. It's an optional record meant to optimize formula storage, and may be encountered between a Formula and a String record. We just have to ignore it. Code fixed in v5.2.1.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android.

[mbleron]

You're welcome.