View source for Access CSV file as a table

Example of reading a CSV textfile as a table. Internally sqlloader is used. You need an Oracle directory with enough rights:

<syntaxhighlight lang=sql>
connect system
create or replace directory data_dir as 'C:/TEMP';
grant read on directory data_dir to user1;
grant read,write on directory data_dir to user1;
grant create any directory to user1;
</syntaxhighlight>

Textfile example 'YD.txt':

<pre>
header,53,22/04/2003,Hello World 
master,1,01-sep-2002 01:02:03,some info 
detail,1,1,52321,yddata yddata yddata 
detail,1,2,233,data data data 
master,2,11-mar-2001 11:02:03,some info 
detail,2,1,52324,yddata yddata yddata 
detail,2,2,25323432,yddata yddata yddata 
</pre>

SQL:

<syntaxhighlight lang=sql>
connect user1

-- create virtual table
create table csv_table( c1 varchar2(80), c2 varchar2(80)
    , c3 varchar2(80), c4 varchar2(80), c5 varchar2(80) ) 
  ORGANIZATION EXTERNAL ( 
    type oracle_loader
    default directory data_dir
    access parameters ( 
      fields terminated by ','
      optionally enclosed by "'"
      missing field values are null )
    location ('YD.txt') )
  ;

select * from csv_table;
</syntaxhighlight>
[[Category:Oracle]]
[[Category:Oracle SQL]]
[[Category:200908]]