How to import data from excel's csv file into oracle database

If you want to import data from external file like excel's .csv or .xlsx and when you have to consider more then 50 columns, then collection or apex_collection don't help you to do it.

At this point, UTL_FILE package will help you to do it very easily. Let's see how this amazing package works.

Firstly, you have to create a directory at your server. Here are scripts to help you.

CREATE OR REPLACE DIRECTORY CSV_DIR AS 'c:\oracle\csv_files';
GRANT READ, WRITE ON DIRECTORY CSV_DIR TO HR;

Then, write a procedure at HR schema to place the .csv blob file to the CSV_DIR directory.

CREATE OR REPLACE PROCEDURE pro_save_csv_to_dir (
   p_csv_blob   IN OUT NOCOPY BLOB,
   p_dir          IN            VARCHAR2,
   p_filename     IN            VARCHAR2)
AS
   v_file       UTL_FILE.file_type;
   v_buffer     RAW (32767);
   v_amount     BINARY_INTEGER := 32767;
   v_pos        INTEGER := 1;
   v_blob_len   INTEGER;
BEGIN
   v_blob_len := DBMS_LOB.getlength (p_csv_blob);

   v_file :=
      UTL_FILE.fopen (p_dir,
                      p_filename,
                      'wb',
                      32767);

   WHILE v_pos <= v_blob_len
   LOOP
      DBMS_LOB.read (p_csv_blob,
                     v_amount,
                     v_pos,
                     v_buffer);
      UTL_FILE.put_raw (v_file, v_buffer, TRUE);
      v_pos := v_pos + v_amount;
   END LOOP;

   UTL_FILE.fclose (v_file);
EXCEPTION
   WHEN OTHERS
   THEN
      UTL_FILE.fclose (v_file);
      RAISE;
END;

NB: Here, "p_csv_blob" is your .csv blob file, "p_dir" is your created directory name.

After successfully placing your .csv blob file to the directory, write another procedure to insert data from file into a database table.

CREATE OR REPLACE PROCEDURE pro_insert_from_dir (
   p_dir          IN     VARCHAR2,
   p_file_name    IN     VARCHAR2,
   p_table_name   IN     VARCHAR2,
   p_out_msg         OUT VARCHAR2)
AS
   v_values        VARCHAR2 (20000);
   v_values2       VARCHAR2 (20000);
   sqlstmt         VARCHAR2 (20000);

   acc_open_file   UTL_FILE.file_type;
BEGIN
   acc_open_file :=
      UTL_FILE.fopen ('' || p_dir || '', '' || p_file_name || '', 'R');

   LOOP
      BEGIN
         UTL_FILE.get_line (acc_open_file, v_values);
         DBMS_OUTPUT.put_line (v_values);

         SELECT REPLACE (v_values, ',', ''',
                         ''')
           INTO v_values2
           FROM DUAL;

         sqlstmt :=
               'insert into '
            || p_table_name
            || ' (col1, col2, col3) values('''
            || v_values2
            || ''')';

         EXECUTE IMMEDIATE sqlstmt;

         DBMS_OUTPUT.put_line (sqlstmt);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;
   END LOOP;

   UTL_FILE.fclose (acc_open_file);
END;

NB: Here "col1, col2, col3" is the column from table to maintain the sequence of .csv file's column.

You must have to remember,

1. In this procedure, comma (,) is used as separator. So, no other comma (,) is allowed at your .csv file.
2. Each and every row of .csv file would consider as a record. So, cut the heading column from .csv file before uploading.

If you don't want to consider above terms, then you should upload and import .xlsx file.

You may also like:

• How to import data from excel's xlsx file into oracle database