Search notes:

Oracle: Reading CSV files with external tables

This example tries to demonstrate how CSV files can be read with external tables
First, we need a CSV-file whose data we want to import into the database. I put this file into the directory home/rene/ext (or when on Windows into C:\Users\Rene/ext/) and named it file.csv:
1,one,first
2,two,second
3,three,third
4,four,fourth
Create a directory object and grant read (at least) and write to the user that wants the external table:
connect / as sysdba

create    directory ext_dir as '/home/rene/ext';
-- create directory ext_dir as 'C:\Users\Rene\ext';

grant read write to rene
Create the external table:
connect rene/rene

create table ext_table_csv (
   i   number,
   n   varchar2(20),
   m   varchar2(20)
)
organization external (
   type              oracle_loader
   default directory ext_dir
   access parameters (
      records delimited  by newline
      fields  terminated by ','
      missing field values are null
   )
   location ('file.csv')
)
reject limit unlimited;
select * from ext_table_csv;
Cleaning up:
drop directory ext_dir;

Error messages in Windows

In Windows, I have received the following error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file file.csv in EXT_DIR not found
I fixed this by giving the Oracle Service (process) the required privileges on the file (See KUP-04040).
After fixing this error, I had a KUP-04027: file name check failed error which I was able to fix by granting the necessary privileges on $home/ext (See KUP-04027)

See also

Reding fixed field length files

Index