Oracle Sql Script Reading Data From Text File

Reading data from CSV file using utl_file

Hi,
Tin anyone guide me how to load data from CSV file into oracle tables using utl_file.

My requirement is similar

Customer is sending me a excel in which top 5 rows having some details about the excel canvass which i need to skip and the actual header nd data starts from 6th row.

So i desire to import the data from 6th row ignoring the summit five rows.

Can anyone help me with this.
Many Thank you,
Santhosh.Southward

Answers

• 

  You tin can use Sql*Loader or Extenral Tables to load data from csv file

• 

  Hoek Member Posts: xvi,079 Gilt Crown

  Hi, here are some examples:

  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:243814703172

  Why utilize UTL_FILE by the mode?

• 

  There is some restriction for non using sql*loader. Then i need to use utl_file simply. Tin can you paste some example.

  many thanks,
  santhosh

• 

  Y'all should open that file using FOPEN part, read lines using GET_LINE function. Information technology's not then difficult ..
  And by getting every line, y'all should split it and insert into related columns

  - - - - - - - - - - - - - - - - - - - - -
  Kamran Agayev A. (10g OCP)
  http://kamranagayev.wordpress.com

• 

  --to read CREATE OR REPLACE Process .file_upload IS    v_file_name   VARCHAR2 (200);    v_file_type   UTL_FILE.file_type;    v_line        VARCHAR2 (m); BEGIN    v_file_name   := 'customers.txt';    v_file_type   := UTL_FILE.fopen ('EXT_TABLES', v_file_name, 'R');        UTL_FILE.get_line (v_file_type, v_line);      DBMS_OUTPUT.put_line (v_line);    UTL_FILE.fclose (v_file_type); END; /   --to open a file CREATE OR Supplant Process testme.file_upload IS    v_file_name   VARCHAR2 (200);    v_file_type   UTL_FILE.file_type;    v_line        VARCHAR2 (1000); BEGIN    v_file_name   :=      'customers_'     || TO_CHAR (SYSDATE, 'dd')     || TO_CHAR (SYSDATE, 'Monday')     || TO_CHAR (SYSDATE, 'YYYY')     || '.txt';    v_file_type   := UTL_FILE.fopen ('EXT_TABLES', v_file_name, 'a');        UTL_FILE.put_line (v_file_type, v_line);    DBMS_OUTPUT.put_line (v_line);    UTL_FILE.fclose (v_file_type); END; /                  

• 

  Hoek Fellow member Posts: 16,079 Gold Crown

  http://asktom.oracle.com/pls/asktom/f?p=100:eleven:0::::P11_QUESTION_ID:464420312302 is an utl_file example.

  If you want to skip lines, then just add together a skip_lines parameter to the procedure and add an incrementing counter to the loop...
  As long as the counter <= skip_lines, you do goose egg, else you beginning processing the data.

• 

  APC Member Posts: 11,316 Bronze Crown

  There is some restriction for not using sql*loader.

  What most external tables? They are and so much easier than UTL_FILE.

  Cheers, APC

  web log: http://radiofreetooting.blogspot.com

• 

  Hi its non TXT file. I want to read and load from csv files. I cant attach the CSV file here otherwise i would have attached that for your reference.
  Besides i desire to ignore the first six rows from that CSV file. I need to insert data from 7th row.

  Thanks,
  Santhosh.S

• 

  santhosh.shivaram wrote:
  Hullo its not TXT file. I desire to read and load from csv files.

  If you are in windows, right click on the file and open with Notepad.It is a text file only, with some graphic symbol equally seperator between fields.
  If in linux, open information technology in vi editor.

  As well i want to ignore the offset half-dozen rows from that CSV file. I need to insert information from 7th row.
  

  SQL> create or supplant directory OGL_REPORT_ACCESS every bit 'C:\Temp';  Directory created.   file a.csv: (Seperator is comma) -------------  ane,test1,xxxxxxxx ii,test1,xxxxxxxx 3,test1,xxxxxxxx 4,test1,xxxxxxxx 5,test1,xxxxxxxx 6,test1,xxxxxxxx vii,test1,xxxxxxxx 8,test1,xxxxxxxx 9,test1,xxxxxxxx 10,test1,xxxxxxxx  SQL> create tabular array test(str varchar2(100));  Table created.  SQL> declare   two   fp utl_file.file_type;   iii   str varchar2(500);   4  brainstorm   five   fp := utl_file.fopen('OGL_REPORT_ACCESS','a.csv','r');   vi   --skipping 7 lines   7   for i in 1..7 loop   8    utl_file.get_line(fp,str);   9   terminate loop;  ten   --reading next lines and in put into test tabular array  11   loop  12    utl_file.get_line(fp,str);  13    insert into test values(str);  xiv   stop loop;  15  exception  16   when no_data_found then  17    commit;  18  end;  19  /  PL/SQL procedure successfully completed.  SQL> select * from examination;  STR ------------------------------------------------------------------------------------------ 8,test1,xxxxxxxx 9,test1,xxxxxxxx 10,test1,xxxxxxxx

• 

  Hullo APC,

  Information technology may be off-topic, only curious to know.

  Is at that place anyway to skip first few lines when defining external tables?

  Also, is there any style to get the information as per the order in the file. ( If no field is available for sorting..)

  Regrds,
  Jeneesh

This give-and-take has been closed.

Oracle Sql Script Reading Data From Text File

Source: https://community.oracle.com/tech/developers/discussion/885949/reading-data-from-csv-file-using-utl-file

Share this post