CODEWITHSUNDEEP
sqlexceloracle-databaseplsql
Ahsan Anwar
Ahsan Anwar

Reputation: 49

Read Excel file in Oracle database

I want to know a way or procedure or query that can help me to read an excel file. I just want to place the excel file somewhere at my DB server and use a query to display all records of that excel file in a query output. File could be of any length, any number of rows and columns.

I have used as_read_xlsx but it gives data in columns and also separate strings, numbers and dates. I want all data to appear as it is in excel.

Upvotes: 0

Views: 2282

Answers (1)

XING
XING

Reputation: 9886

I just want to place the excel file somewhere at my DB server and use a query to display all records of that excel file in a query output. File could be of any length, any number of rows and columns.

As mentioned in my comments you would need to create a External Table to do so. See below:

My CSV (Tst.csv) file content:

enter image description here

Created directory CSV_FILES and logged in as SYS and granted permission to read & write.

GRANT READ,WRITE ON DIRECTORY CSV_FILES TO scott;

Directory

Created External table as below:

CREATE TABLE TST_TABLE
(
  ACCT_NO                   VARCHAR2(7),
  LOAN_AMOUNT               NUMBER,
  PRODUCT_TYPE              VARCHAR2(3) 
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY CSV_FILES
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED  BY NEWLINE
         skip 1
         LOGFILE CSV_FILES:'tstlog.log'
         BADFILE CSV_FILES:'tstbad.bad'
         FIELDS TERMINATED BY  ","            
           (
              ACCT_NO            ,
              LOAN_AMOUNT        ,
              PRODUCT_TYPE       
            )
          )
     LOCATION ('Tst.csv'))
     reject limit unlimited;

Query the table all records as:

SQL> select * from TST_TABLE;

ACCT_NO LOAN_AMOUNT PRO
------- ----------- ---
AFSGSGT       34454 XXX
BSDFFGS       45645 YYY
SFSDFDS       56453 ZZZ

Query to select selective records using filter condition:

SQL> select * from TST_TABLE where product_type = 'XXX';

ACCT_NO LOAN_AMOUNT PRO
------- ----------- ---
AFSGSGT       34454 XXX

Upvotes: 2

Related Questions