CSV FILE LOADER IN ORACLE USING PLSQL

Step # 1:

Create Database directory where you will put your CSV file in Server.

CREATE OR REPLACE DIRECTORY MY_DIR AS '/U03/.......';

-------------------------------------------------------------------------------------------

 

Step # 2;

Create 3 Tables According to your CSV Columns:

1. CSV Data Table

2. Bad Log/Error Table

3. History Table

CREATE TABLE CSV_AR_INV_TABLE

(

LEGAL_ENTITY_NAME                   VARCHAR2(100),

INVOICE_TYPE                                  VARCHAR2(100),

CUST_TRX_TYPE_NAME               VARCHAR2(100),

CURRENCY_CODE                            VARCHAR2(100),

TRX_DATE                                           DATE,

CONVERSION_RATE                       NUMBER,

PAYMENT_TERM                             VARCHAR2(100),

SALES_PERSON_NAME                                 VARCHAR2(100),

BILL_TO_CUSTOMER_NUMBER VARCHAR2(100),

BILL_TO_CUSTOMER_NAME      VARCHAR2(100),

BILL_TO_CUSTOMER_SITE           VARCHAR2(100),

SHIP_TO_CUSTOMER_NUMBER VARCHAR2(100),

SHIP_TO_CUSTOMER_NAME     VARCHAR2(100),

SHIP_TO_CUSTOMER_SITE         VARCHAR2(100),

LINE_NO                                              NUMBER,

LINE_TYPE                                           VARCHAR2(100),

LINE_DESCRIPTION                         VARCHAR2(100),

QUANTITY                                           NUMBER,

UNIT_SELLING_PRICE                    NUMBER,

AMOUNT                                            NUMBER,

CRM_INVOICE_NO                         VARCHAR2(100),

GL_CODE                                             VARCHAR2(100),

STATUS                                                VARCHAR2(100)

);

CREATE TABLE CSV_AR_INV_LOADER_BADLOG
(
TABLE_NAME VARCHAR2(200),
LINE_NUMBER NUMBER,
ERRM VARCHAR2(4000),
DATA VARCHAR2(4000),
ERROR_DATE TIMESTAMP(6)
);

CREATE TABLE CSV_AR_INV_LOADER_HIST
(
FILENAME VARCHAR2(200),
TABLE_NAME VARCHAR2(200),
NUM_OF_REC NUMBER,
IMPORT_DATE DATE
);

------------------------------------------------------------------------

Step # 3

Create Function to Read and Load CSV Data From Server File Location.

CREATE FUNCTION CSV_AR_LOADER_FUNC
      ( P_TABLE              IN VARCHAR2,
        P_DIR                IN VARCHAR2 DEFAULT 'MY_DIR',
        P_FILENAME           IN VARCHAR2,
        P_IGNORE_HEADERLINES IN INTEGER DEFAULT 1,
        P_DELIMITER          IN VARCHAR2 DEFAULT ',',
        P_OPTIONAL_ENCLOSED  IN VARCHAR2 DEFAULT '"')
    RETURN VARCHAR2 IS

    /***************************************************************************
    -- PROCEDURE LOAD_CSV
    -- PURPOSE: This Procedure read the data from a CSV file.
    -- And load it into the target oracle table.
    -- Finally it renames the source file with date.
    -- P_FILENAME -- The name of the flat file(a text file)
    -- P_DIRECTORY -- Name of the directory where the file is been placed.
    -- Note: The grant has to be given for the user to the directory
    -- before executing the function
    -- P_IGNORE_HEADERLINES: -- Pass the value as '1' to ignore importing headers.
    -- P_DELIMITER -- By default the delimiter is used as ','
    -- As we are using CSV file to load the data into oracle
    -- P_OPTIONAL_ENCLOSED -- By default the optionally enclosed is used as '"'
    -- As we are using CSV file to load the data into oracle
    -- AUTHOR: M WAQAS KHAN
    -- Date : 24 FEB, 2017
    **************************************************************************/
    l_input     utl_file.file_type;
    l_theCursor integer default dbms_sql.open_cursor;
    l_lastLine  varchar2(4000);
    l_cnames    varchar2(4000);
    l_bindvars  varchar2(4000);
    l_status    integer;
    l_cnt       number default 0;
    l_rowCount  number default 0;
    --l_sep       char(1) default NULL;
    L_ERRMSG varchar2(4000);
    V_EOF    BOOLEAN := false;
    L_RES    VARCHAR2(100) := 'SUCCESS';
    l_error exception;
  begin
 
    DELETE FROM CSV_AR_INV_TABLE;
    commit;
    delete from CSV_AR_INV_LOADER_BADLOG;
    commit;
 
    l_cnt := 1;
    for TAB_COLUMNS in (select column_name, data_type
                          from user_tab_columns
                         where table_name = p_table
                         order by column_id) loop
      l_cnames   := l_cnames || tab_columns.column_name || ',';
      l_bindvars := l_bindvars || case
                      when tab_columns.data_type in ('DATE', 'TIMESTAMP(6)') then
                      --'to_date(:b' || l_cnt || ',''YYYY-MM-DD HH24:MI:SS''),'
                       'to_date(:b' || l_cnt || ',''MM-DD-YYYY HH24:MI:SS''),'
                      else
                       ':b' || l_cnt || ','
                    end;
      l_cnt      := l_cnt + 1;
      --dbms_output.put_line(l_bindvars );
   
    end loop;
 
    begin
      l_cnames   := rtrim(l_cnames, ',');
      L_BINDVARS := RTRIM(L_BINDVARS, ',');
      /* Modified By Muhamamd Waqas Khan
        Date : 15 March 016
      */
      BEGIN
        L_INPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'r');
      exception
        WHEN UTL_FILE.INVALID_OPERATION THEN
          raise l_error;
      END;
   
      ---dbms_output.put_line(l_bindvars || ' - ' || l_cnames);
   
      IF p_ignore_headerlines > 0 THEN
        BEGIN
          FOR i IN 1 .. p_ignore_headerlines LOOP
            UTL_FILE.get_line(l_input, l_lastLine);
          END LOOP;
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            v_eof := TRUE;
        end;
      END IF;
      if not v_eof then
        dbms_sql.parse(l_theCursor,
                       'insert into ' || p_table || '(' || l_cnames ||
                       ') values (' || l_bindvars || ')',
                       dbms_sql.native);
     
        loop
          begin
            utl_file.get_line(l_input, l_lastLine);
          exception
            when NO_DATA_FOUND then
              exit;
          end;
          if length(l_lastLine) > 0 then
            for i in 1 .. l_cnt - 1 LOOP
              dbms_output.put_line(ltrim(REGEXP_SUBSTR(l_lastline,
                                                       '(^|,)("[^"]*"|[^",]*)',
                                                       1,
                                                       i),
                                         p_delimiter));
           
              dbms_sql.bind_variable(l_theCursor,
                                     ':b' || i,
                                     rtrim(rtrim(ltrim(ltrim(REGEXP_SUBSTR(l_lastline,
                                                                           '(^|,)("[^"]*"|[^",]*)',
                                                                           1,
                                                                           i),
                                                             ','),
                                                       '"'),
                                                 ','),
                                           '"'));
            end loop;
            begin
              l_status := dbms_sql.execute(l_theCursor);
              -- dbms_output.put_line('Waqas:' ||l_theCursor);
              l_rowCount := l_rowCount + 1;
            exception
              when OTHERS then
                L_ERRMSG := SQLERRM;
                insert into CSV_AR_INV_LOADER_BADLOG
                  (TABLE_NAME, line_number, ERRM, data, ERROR_DATE)
                values
                  (p_table,
                   l_rowCount + 2,
                   l_errmsg,
                   l_lastLine,
                   systimestamp);
             
            end;
          end if;
        end loop;
        dbms_sql.close_cursor(l_theCursor);
        utl_file.fclose(l_input);
        commit;
      end if;
      insert into CSV_AR_INV_LOADER_HIST
        (FILENAME, TABLE_NAME, NUM_OF_REC, IMPORT_DATE)
      values
        (P_FILENAME, P_TABLE, l_rowCount, sysdate);
      UTL_FILE.FRENAME(P_DIR,
                       P_FILENAME,
                       P_DIR,
                       REPLACE(P_FILENAME,
                               '.csv',
                               '_' ||
                               TO_CHAR(SYSDATE, 'DD_MON_RRRR_HH24_MI_SS_AM') ||
                               '.csv'));
      commit;
   
    exception
      when l_error then
        L_RES := 'Either Invalid File or File Not Exists';
    end;
    RETURN L_RES;
  end CSV_AR_LOADER_FUNC;
 

----------------------------------------------------------------------------------------------------

Step # 4  

Execute Function and Enjoy !!