Load CSV file in Oracle using PL/SQL

In one of my previous post I have created a CSV file using PL/SQL (Read Here). Here we are trying to load that CSV FILE in ORACLE normal table. Here we assume that the directory is created and the permissions are already granted also the CSV file has been exported.

To create a CSV file used in this article please read following post:
- http://nimishgarg.blogspot.in/2011/09/create-csv-file-using-plsql.html

To Create directory & and grant the read write permission to the specified USER, we need to execute following commands as SYS or SYSTEM user. e:\mycsv\ must be a physical path on the disk.
C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 3 15:07:01 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> create or replace directory MYCSV as 'e:\mycsv\';
Directory created.

SQL> grant read, write on directory MYCSV to scott;
Grant succeeded.

To Load "EMP_DEPT.CSV" file in to Oracle Table we have created a table EMP_DEPT as
C:\>sqlplus scott/tiger
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 29 15:47:05 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> CREATE TABLE EMP_DEPT
  2  (
  3  EMPNO NUMBER(4),
  4  ENAME VARCHAR2(10),
  5  SAL NUMBER(7,2),
  6  DNAME VARCHAR2(14)
  7  );

Table created.

Once the table is created, we just need to execute following pl/sql block to load the CSV file into the Oracle table.
SQL> DECLARE
  2    F UTL_FILE.FILE_TYPE;
  3    V_LINE VARCHAR2 (1000);
  4    V_EMPNO NUMBER(4);
  5    V_ENAME VARCHAR2(10);
  6    V_SAL NUMBER(7,2);
  7    V_DNAME VARCHAR2(14);
  8  BEGIN
  9    F := UTL_FILE.FOPEN ('MYCSV', 'EMP_DEPT.CSV', 'R');
10    IF UTL_FILE.IS_OPEN(F) THEN
11      LOOP
12        BEGIN
13          UTL_FILE.GET_LINE(F, V_LINE, 1000);
14          IF V_LINE IS NULL THEN
15            EXIT;
16          END IF;
17          V_EMPNO := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);
18          V_ENAME := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);
19          V_SAL := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);
20          V_DNAME := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4);
21          INSERT INTO EMP_DEPT VALUES(V_EMPNO, V_ENAME, V_SAL, V_DNAME);
22          COMMIT;
23        EXCEPTION
24        WHEN NO_DATA_FOUND THEN
25          EXIT;
26        END;
27      END LOOP;
28    END IF;
29    UTL_FILE.FCLOSE(F);
30  END;
31  /

PL/SQL procedure successfully completed.

Data of "EMP_DEPT" can be determind as following
SQL> SELECT * FROM EMP_DEPT;

     EMPNO ENAME             SAL DNAME
---------- ---------- ---------- --------------
      7369 SMITH            1000 20
      7499 ALLEN            2000 30
      7521 WARD             4000 30
      7566 JONES            2975 20
      7654 MARTIN           1250 30
      7698 BLAKE            2850 30
      7782 CLARK            2450 10
      7788 SCOTT            3000 20
      7839 KING             5000 10
      7844 TURNER           1500 30
      7876 ADAMS            1100 20
      7900 JAMES             950 30
      7902 FORD             3000 20
      7934 MILLER           1300 10

14 rows selected.

Other simple (and better) method might be to load CSV file in External table (Read here) and then simply execute a "insert into select" query.


Related Posts:
- Create CSV file using PL/SQL
- Create External Table from CSV File
- Create XML file using PL/SQL
- Load XML file in Oracle Table

10 comments:

  1. Thanks man, especially the sysdba login part. God bless u

    ReplyDelete
  2. V_EMPNO := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);--- In this line I get an error saying :[Error] ORA-00947 (17: 101): PL/SQL: ORA-00947: not enough values.

    Any ideaa ?

    ReplyDelete
    Replies
    1. which version of oracle you are using ?
      try to execute following query is sqlplus
      select REGEXP_SUBSTR('a,b,c', '[^,]+', 1, 1) from dual

      Delete
  3. V_EMPNO := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1); -- On this line I get an error saying : [Error] ORA-00947 (17: 101): PL/SQL: ORA-00947: not enough values

    Any idea ?

    ReplyDelete
    Replies
    1. try to put your csv line instead of V_LINE variable and execute as
      select REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, 1) from dual;

      Delete
  4. ERROR at line 2:
    ORA-06550: line 2, column 4:
    PLS-00201: identifier 'UTL_FILE' must be declared
    ORA-06550: line 2, column 4:
    PL/SQL: Item ignored
    ORA-06550: line 9, column 2:
    PLS-00320: the declaration of the type of this expression is incomplete or
    malformed
    ORA-06550: line 9, column 2:
    PL/SQL: Statement ignored
    ORA-06550: line 10, column 5:
    PLS-00201: identifier 'UTL_FILE' must be declared
    ORA-06550: line 10, column 2:
    PL/SQL: Statement ignored
    ORA-06550: line 29, column 18:
    PLS-00320: the declaration of the type of this expression is incomplete or
    malformed
    ORA-06550: line 29, column 2:
    PL/SQL: Statement ignored

    It shows me Error as above...
    Please reply me the solution... :)

    ReplyDelete
    Replies
    1. user must have execute permission on utl_file

      Delete
  5. Thanks.and i have a requirement .if the query is like below
    select REGEXP_SUBSTR('a,,c', '[^,]+', 1, 2) from dual;
    i need output should be "NULL".
    Please Help on this.

    ReplyDelete
    Replies
    1. try REGEXP_SUBSTR('a,,c', '[^,]', 1, 2)

      Delete
    2. My csv has some columns with no values. how do i handle that? the above doesn't return 'NULL'. How do i read it as NULL?

      Delete