search

mbleron / ExcelTable

ExcelTable is an Oracle SQL interface to read Microsoft Excel files (.xlsx, .xlsm, .xlsb, .xls, .xml), ODF spreadsheet files (.ods) and flat files as external tables
MIT License
45 stars 12 forks source link

Problem when getting rows from a text file #41

Closed adrianboangiu closed 2 years ago

adrianboangiu commented 2 years ago

Hello,

I used a lot ExcelTable to transform Excel files (.xlsx) into Oracle Views. Now I am using the same "template" to transform a text file into a view and I obtain some weird results.

I am using the latest code of ExcelTable and Oracle 19.3.0.0.0.

I attached a text file (lables.txt) that allow to simulate the problem and the definition of the view using ExcelTable (v_label.txt).

I use TAB as field separator and LF as line terminator. labels.txt v_label.txt

The following images show the problem. Excel.png shows the content of labels.txt in Excel. SQLDeveloper.png and SQLDeveloper.full.png shows the content of the view in SQLDeveloper (the same columns as Excel and also an extra computed column in the view). Excel SQLDeveloper SQLDeveloper full

As you can see the CONTROL_TYPE and ID columns is wrong for some lines (truncated for the second line and completely wrong -seems to have the previous lines value- for 6th and 9th line).

Am I doing something wrong in the definition of the view or is ExcelTable problem?

Thank you. Best regards

mbleron commented 2 years ago

Oracle bug, related to view merging apparently. The issue occurs when we add the computed column in the middle of the SELECT list. It seems to work when the column is added at the last position though.

Workaround : NO_MERGE hint

   WITH EXCEL_FILE
      AS(
         SELECT /*+ no_merge*/ t.*
           FROM TABLE(
                   EXCELTABLE.getRows( ...
adrianboangiu commented 2 years ago

Thank you for the work around. I will use it altough the suggestion to move the computed column as the last column of the view works too.