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:
- Generate XML data using SQL in Oracle Database
- Convert XML to Rows and Columns in Oracle
- Create CSV file using PL/SQL
- Create External Table from CSV File
- Create XML file using PL/SQL
- Load XML File in Oracle Table
- Generate XML Output from SQL

64 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
  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
    3. Hi Nimish,
      That does not seem to work as it's returning a 'c'. Is there any suggestion to get a null value back?

      Delete
    4. I managed to work with null values like this:

      V_EMPNO:= TRIM(BOTH '"' FROM TRIM(TRAILING ';' FROM (REGEXP_SUBSTR(V_LINE, '[^' || V_DELIMITER || ']*' || V_DELIMITER || '|[^' || V_DELIMITER || ']*$', 1, 1))));

      This does also get rid of double quotes surrounding a value.
      V_DELIMITER can be replaced with any character used to seperate values.

      Delete
  6. Shraddha, have you find a solution? I have the same problem.

    ReplyDelete
  7. Hello, I am trying to use above code and its not working when I have one column as date..

    V_LAST_MOD_USERID := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 18);
    error is like
    ORA-01843: not a valid month
    ORA-06512: at line 47
    01843. 00000 - "not a valid month"

    ReplyDelete
    Replies
    1. Hello Nimish - I am just using your above example to read a csv file. since in my table, I have huge number of columns, among them one is a date formatted data.

      dates are coming in this format 5/12/2014 12:12:22 ( month/day/year hour:minute:second)

      could you pls tel me how I can modify this block of code to read a date formatted field from CSV file.

      Delete
    2. I am not good in SQL but just trying my hand.. in your code, I see you have used V_LINE in UTL_FILE.GET_LINE(F, V_LINE, 1000); and based on same I used in my code.

      Delete
  8. I have table in that 45 columns are there so for each column I have to take variable or if you have other option to process all columns through one variable.

    ReplyDelete
  9. If I have one table in that around 57 columns so for that i have to take one variable for each column or is it possible to with one variable.
    put your suggestion.

    ReplyDelete
    Replies
    1. Use rowtype of table like if I have emp table, i can declare rowtype as
      l_row emp%rowtype;

      Delete
    2. Thanks
      One more query can I use any Object type or Record

      Delete
    3. Yes, actually l_row emp%rowtype; is record itself

      Delete
  10. Hi Nimish,

    Using UTL_FILE how to perform below query
    1. how to copy the content of file1 to file2?
    2. how to copy only line having 'error' Key word.

    ReplyDelete
  11. While executing this i am getting the below error please help me
    ORA-29283: invalid file operation
    ORA-06512: at "SYS.UTL_FILE", line 536
    ORA-29283: invalid file operation
    ORA-06512: at line 9

    ReplyDelete
  12. Hi Nimish IN .CSV file some columns are comma in between for column value is 'RECORDS CREATED BY , ABCD' . So ABCD is getting stored in nect column.. How we overcome this?

    ReplyDelete
  13. Se voce ama cafe' ou cha' pode beber 'a vontade.

    ReplyDelete
  14. I соuld nоt ƅе happier thаn I аm todaу!

    ReplyDelete
  15. Enganam-se sobrе isso. Mais do quе isso ja'
    e' demais.

    ReplyDelete
  16. Um amigo Ԁa faculdade ԛue voce nao ᴠia uns dois anos?

    ReplyDelete
  17. Cartao ficou muitⲟ bonito, e diferente!

    ReplyDelete
  18. Pretty! This has been an extremely wonderful article.

    Thank you for supplying these details.

    ReplyDelete
  19. I do agree with all of the ideas you've introduced to your post.

    They are very convincing and will certainly work. Still, the posts are very quick for novices.
    May just you please lengthen them a bit from
    subsequent time? Thank you for the post.

    ReplyDelete
  20. Highly energetic article, I loved that bit. Will there be a
    par 2?

    ReplyDelete
  21. Wow, tyis piece oof writing іs fastidious, my sister іs analyzing these thіngs,
    sо I amm goinhg to convey һer.

    ReplyDelete
  22. hi do you have a script that loads csv file by column name not by column position?

    ReplyDelete
  23. Greetings! I've been following your blog for a while now and
    finally got the bravery to go ahead and give you a shout out from
    Houston Texas! Just wanted to mention keep up the great work!

    ReplyDelete
  24. Pretty! This was a really wonderful post. Thank you
    for supplying these details.

    ReplyDelete
  25. Hi, In my CSV, i have some values which are empty(Not spaces). For that above code gives error.How can i handle that.

    ReplyDelete
  26. Did anyone find a solution to empty values?

    ReplyDelete
  27. You have posted an incredibly great article here.

    New tractor

    ReplyDelete
  28. my csv is in local drive and when i am giving path in

    F := UTL_FILE.FOPEN ('F:\PAYROLL','SIL_PAYROLL.csv', 'R'); it says invalid directory path.Can anyone guide me

    ReplyDelete
  29. Nice Blog, visit Bidz365 for Vendor Assessment on GeM, OEM Panel on GeM, and Tender Submission Services. For more information visit our website.
    Vendor Assessment on GeM

    ReplyDelete
  30. pg slot สล็อต พี จี ถ้ามีปริศนาว่า เกมสล็อต PG SLOT เกมไหนดี จะต้องบอกเลยว่า เว็บชั้น 1 ของประเทศเวลานี้ จำต้องน่าเล่นมากยิ่งกว่า 1000 เกม แล้วก็ ยังสามารถเล่นได้ทั้งยัง

    ReplyDelete