Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

External Table with one column CSV

User_VBJB2Jun 16 2023

Hello,

I have the following CSV data and an external table definition. A CSV file with one column can be imported with this. I can read the data using the table, however as I choose them for reading, double quotes are imported before and after each row.

Please provide me some advice on how to import the data from the CSV file without using double quotes.

CREATE TABLE XXINT_IRS_EXT(
   "INVOICE_NUM" VARCHAR2 (100 BYTE)
)
ORGANIZATION EXTERNAL (TYPE oracle_loader
   DEFAULT DIRECTORY "OEB_INBOUND_DIR" ACCESS PARAMETERS (
       RECORDS DELIMITED BY NEWLINE
           BADFILE 'IRS14.bad'
           LOGFILE 'IRS14.log'
           SKIP 1
       FIELDS          
       TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
       MISSING FIELD VALUES ARE NULL
   ) LOCATION ("INBOUND_DIR" : 'IRS14.csv')
) REJECT LIMIT UNLIMITED;

CSV sample data:
INV-2081272-05488243
INV-1656378-05427715
INV-1666200-05428937
INV-1688570-05432307
INV-3292506-00012864
INV-811613-05326087

Current output is as below.
"INV-2081272-05488243 
"

Expected output is as below.
INV-2081272-05488243
This post has been answered by Paulzip on Jun 16 2023
Jump to Answer
Comments
Post Details
Added on Jun 16 2023
9 comments
586 views