How to read csv and write it in a table using a query?

Question

i want to read a csv file and then, write it on a table. The solutions that I've found online haven't helped me and i don't know how to do it.

My csv file has 2 columns like that:

ID NAME
1  Sylvia
2  John
...

And i want to read it and import it to the table STUDENTS.

I have to do it with sql statements. CSV file has 50000 rows, and i'm using a oracle DB

Thank you

UPDATE:

This is what I execute:

CREATE OR REPLACE DIRECTORY data_dir1 AS '/datos/students/csv';

create table aaa
 (ID varchar2(20),
  NAME varchar2(20)
 )
 organization external
 (type oracle_loader
  default directory DATA_DIR1
  access parameters
    (records delimited by newline
     fields terminated by ';'
       (ID char(20),
        NAME char(20)
       )
    )
  location ('IdStudents.txt')
 )
 reject limit unlimited

And when i throw the next query, throw an error:

select * from aaa

SQL Error [29913] [99999]: ORA-29913: Error executing ODCIEXTTABLEOPEN call ORA-29400: data cartridge error error opening /data/students/csv/AAA_10383.log file

Answer 1

As you said you have to do it using SQL, then consider external tables feature. Here's an example.

Connected as SYS, I'll create a directory (Oracle object which points to a filesystem directory) and grant required privileges to scott, user who will be using the CSV file (which represents the external table):

SQL> show user
USER is "SYS"
SQL> create directory ext_dir as 'c:\temp';

Directory created.

SQL> grant read, write on directory ext_dir to scott;

Grant succeeded.

SQL>

Contents of my CSV file (named test3.txt):

1,Sylvia
2,John
3,Littlefoot

Connect as scott, create table which will be the target of my insert statement later:

SQL> create table test (id number, name varchar2(10));

Table created.

Create external table:

SQL> select directory_name from all_directories;

DIRECTORY_NAME
------------------------------
EXT_DIR

SQL> create table ext_test
  2    (id    varchar2(10),
  3     name  varchar2(10)
  4    )
  5  organization external
  6    (type oracle_loader
  7     default directory ext_dir
  8     access parameters
  9       (records delimited by newline
 10        fields terminated by ','
 11        missing field values are null
 12          (id    char(10),
 13           name  char(10)
 14          )
 15       )
 16     location ('test3.txt')
 17    )
 18    reject limit unlimited;

Table created.

Is everything OK?

SQL> select * from ext_test;

ID         NAME
---------- ----------
1          Sylvia
2          John
3          Littlefoot

SQL>

Yes, everything is fine, I can see CSV file's contents.

Finally, it is a matter of a simple insert to fetch data from test3.txt and insert it into my table. I can write any kind of select statement, use a where clause etc. which improves the whole experience A LOT!

SQL> insert into test (id, name)
  2  select id, name
  3    from ext_test
  4    where length(name) > 5
  5      and mod(id, 3) = 0;

1 row created.

SQL> select * from test;

        ID NAME
---------- ----------
         3 Littlefoot

SQL>

---

As you can see, that's pretty nice approach. Its "drawback" is that (if database doesn't belong to you and/or you aren't a DBA) you'll have to talk to DBA to create a directory (which they probably already have) and grant you access to it.

Answer 2

Use SQL*Loder utility

create a .ctl file like students.ctl

OPTIONS ( SKIP = 1) -- IF YOUR FILE HAS HEADER
LOAD DATA
INFILE 'FILENAME'
INSERT INTO TABLE STUDENTS
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
ID,
NAME
)

and use command

sqlldr user/password@servicename students.ctl