Oracle Loader for .xlsx文件 [英] Oracle Loader for .xlsx file

问题描述

我正打算将.xlsx文件加载到Oracle数据库表中.我的代码出现错误.我通常将此代码用于.csv文件，但需要将其用于.xlsx.我已经编辑了字段名，表名等 这可能吗?

I am tring to load a .xlsx file into an Oracle database table. I am getting an error for my code. I normally use this code for .csv files but need to use it for .xlsx I have edited my field names, table names etc Is this possible?

Drop TABLE Temp_Info;
      CREATE TABLE Temp_Info
                           (
                             Unique_Id varchar2(255)  ,  
                             Name varchar2(255),  
                             Alt_Name varchar2(255)
                           )
             ORGANIZATION EXTERNAL 
             ( 
               TYPE ORACLE_LOADER
               DEFAULT DIRECTORY SEPA_FILES 
               ACCESS PARAMETERS 
               ( 
                 records delimited by newline  
                 skip 1 
                 fields terminated by ',' 
                 missing field values are null
                 ( 
                  Unique ID -(filled automatically),Name,Alt Name
                 )
               ) 
               LOCATION ('Data_File.xlsx') 
             ) 
             REJECT LIMIT UNLIMITED;  

Select * From Temp_Info a;

Error Message: 9:16:55  ORA-29913: error in executing ODCIEXTTABLEOPEN callout
9:16:55  ORA-29400: data cartridge error
9:16:55  KUP-00554: error encountered while parsing access parameters
9:16:55  KUP-01005: syntax error: found "identifier": expecting one of: "comma, char, date, defaultif, decimal, double, float, integer, (, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned"
9:16:55  KUP-01008: the bad identifier was: ID
9:16:55  KUP-01007: at line 6 column 30
9:16:55  ORA-06512: at "SYS.ORACLE_LOADER", line 14
9:16:55  ORA-06

推荐答案

1)选项[纯PL/SQL]

1) Option [ Pure PL/SQL]

Xlsx文档是xml文档的压缩集.您可以将扩展名xlsx更改为zip，解压缩并找出其中的内容. 此处描述在Oracle环境中如何处理xlsx文档. 该解决方案有效，但是实现起来非常痛苦.

Xlsx document is zipped set of xml documents. You can change extension xlsx to zip, unzip and find out what is inside. Here is description how to deal with xlsx document in oracle environment. This solution works but implementation is very painful.

2)选项(PL/SQL + Apache POI) 在Java中创建实现.并在db中使用它.

2) Option (PL/SQL + apache POI) Create implementation in java. And use it in db.

3)将xlxs转换为csv.

3) Convert xlxs to csv.

这篇关于Oracle Loader for .xlsx文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！