在Oracle数据库中读取Excel文件 [英] Read Excel file in Oracle database

问题描述

我想知道一种可以帮助我读取Excel文件的方式或过程或查询.我只想将excel文件放在数据库服务器的某个位置，并使用查询在查询输出中显示该excel文件的所有记录.文件可以是任何长度，任何数量的行和列.

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.

我使用过as_read_xlsx，但是它以列形式提供数据，并且还以单独的字符串，数字和日期表示.我希望所有数据都像在excel中一样显示.

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.

推荐答案

我只想将excel文件放在我的数据库服务器上的某个地方并使用 查询以在查询输出中显示该excel文件的所有记录. 文件可以是任何长度，任何数量的行和列.

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.

正如我的评论中所述，您将需要创建一个External表. 见下文:

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

我的CSV(Tst.csv)文件内容:

My CSV (Tst.csv) file content:

创建目录CSV_FILES，并以SYS身份登录，并授予读取&写.

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

GRANT READ,WRITE ON DIRECTORY CSV_FILES TO scott;

创建的External表如下:

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

这篇关于在Oracle数据库中读取Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！