Load excel/ csv file data Oracle table using PL/SQL Block

To read a CSV file using PLSQL block and loading into a Oracle table.

Let us proceed with very simple steps and we can see here how easily and quickly load .csv file data to table in Oracle database using a plsql block.

STEP:1 - Create a table to which csv data will be inserted. Remeber your .csv file must be organized. Based on your requirement create table structure before data load.


  CREATE TABLE sales.book_info
    (
    product_id NUMBER(4),
    product_name VARCHAR2(10),
    price NUMBER(7,2),
    author VARCHAR2(14)
    );  

STEP:2 create database directory in the Database where your .csv file resides

SQL> create directory salesdata as '/u03/work/salesdata';


STEP:3  Run the below procedure to read data from .CSV and insert into table sales.book_info table.

DECLARE
      F UTL_FILE.FILE_TYPE;
      V_LINE VARCHAR2 (1000);
      V_product_id NUMBER(4);
      V_product_name VARCHAR2(10);
      V_price NUMBER(7,2);
      V_author VARCHAR2(14);
    BEGIN
     F := UTL_FILE.FOPEN ('salesdata', 'employee.csv', 'R');
    IF UTL_FILE.IS_OPEN(F) THEN
      LOOP
        BEGIN
          UTL_FILE.GET_LINE(F, V_LINE, 1000);
          IF V_LINE IS NULL THEN
            EXIT;
          END IF;
          V_product_id := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);
          V_price := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);
          V_product_name := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);
          V_author := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4);
          INSERT INTO sales.book_info VALUES(V_product_id, V_product_name, V_price, V_author);
          COMMIT;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
        END;
      END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
  END;
  /

STEP:4 Select the table sales.book_info and check if csv data are loaded or not

SQL> Select * from sales.book_info;

     product_id product_name      price author
     --------- ------------------ ---------- ------------
         1 Ray of Hope 100 Gouranga
         2 A Needle of thons       200 Kabu Panda
         3 Two States 150 ChetanBhagat